VB for MsAccess 2000 How to pass name of object to function

Hey there.

I am using MsAccess 2000 and am trying to add some extra usability features to a form so I have been using visual basic for Access.

What I am doing is this: Whenever a dropdown box has focus, I want it to dropdown automatically if the box is empty or if the down arrow key is pressed. I have worked it so that if there is already data in the box, the down arrow key drops the box down but doesn't move the selection down until you press the down arrow key again (prevents selecting another item accidentally on the first key press).

I have the code to do what I need but instead of adding a bigger section of the code to all the keydown properties, I am making a function that can be called.

(I don't know if 'property' is the right word for keydown)

Anyway, I want to get the sub name (AFP in this example) and pass it to the function farther down.

How do I do that?

Code:

Private Sub AFP_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = 40 Then Call KeyDownOnce(onceflg, AFP)

End Sub

Code:

Public Function KeyDownOnce(onceflg As Integer, SubName As String)

msg =  "onceflg= " + Str(onceflg) + " keycode= " + Str(KeyCode) + " subname= " + SubName
result = MsgBox(msg)


If KeyCode = 40 Then
    If onceflg = 0 Then
        SubName.Dropdown
        KeyCode = 0
        onceflg = 1
    Else
        SubName.Dropdown
    End If
End If

End Function

The code I used above gives me an 'invalid qualifier' error.

I suppose, if I could work some code that runs in the form just checks when a dropdown box object is hit (or a list of names of dropdown boxes), that would require even less duplication of code but I don't know if that's workable without slowing things down.

Thanks in advance.

 

It's not easy to work out how this fits into everything else, but I would make one comment:

The variables onceflg and AFP don't appear to be defined in your Subroutine AFP_KeyDown. Unless these are global variables, nothing is being passed to the function.

Also the function is taking SubName as a String. What you need to pass to the function is the object that has the .Dropdown method or attribute. If SubName is a string, I expect SubName.Dropdown will fail because you are effectively doing this "name_of_something ".Dropdown rather than apply the Dropdown method to an object.

I expect you need the form item object. That is, the form that holds the selection dropdown will be defined (named) somewhere. The dropdown will be a child of this form. So what you pass to the function will be something like form_name.selection_object.

Oh! and do yourself a favour - run away from Access and VBA!!! Web front end with a proper database back end is much easier to manage and distribute.

 

Actually, I've just had a quick look in Excel. All the form objects have names defined in the properties of the object. If AFP is the name of the dropdown selection object within the form, then I think all you need to is change the function definition to:

Code:

Public Function KeyDownOnce(onceflg As Integer, SubName As Object)

I'll assume onceflg is a global variable.

 

Thanks, that helps. I also posted in a different forum and got similar information here. http://www.access-programmers.co.uk/forums/showthread.php?p=695962#post695962

I still haven't had a chance to poke around with it again but will soon.

I will let you know what happens/what I end up doing.

Also, what would you suggest to create a database and what to use to program a web front end? Is there anything good out there that is free and would work with just internet explorer after the database and interface is completed?

 

Database
MySQL is a decent database and the community edition is free. If you're reasonably new to databases, this is a good place to start.

Alternatives are SQLite (basic but very fast) and PostGres(Probably a more powerful database option to MySQL)

MicroSoft SQL Express is OK though its a bulky database if you only want something simple. MS SQL is a great corporate database, but for small stuff I'd rather use MySQL.

Programming
As for the programming language, the skies the limit.

If you want to build on your VBA skills, ASP.Net is an obvious way to go. By default it uses MS SQL (Express is the free version), but the latest versions will work with other databases. The Visual Developer IDE is available for free in its Express version.

However, you should also look at PHP which is a very good alternative, and probably the most popular on the net. It is certainly the easiest to get hosted.

My favourite solution is Ruby on Rails (I like it so much I wrote a book about it - see my signature). I came to Rails after developing ASP, ASP.Net, and PHP sites. I think it is a lot easier to use than those others and encourages you to write good code. There are plenty of excellent tutorials and books to get you started.

Of course there are other options like JSP and Perl CGI, but the ones above are probably the easiest to get started with. Have a look at those three and choose the one that best suits the way you work.