VB code to display queries in a dialog box does not work

[dderolph]

I'm not sure if this is the best section to post in.

I'm taking a class on Access and have a database with numerous Queries. One assignment calls for creating a queries dialog box, named frmQueriesDialogBox, to display all queries. I've done that and the dialog box displays all queries. The dialog box also has two command buttons, one to Dispaly a selected query and one to Close the dialog box.

The next part of this is to create VB code which will open a query from the dialog box by either double clicking on the query name in the dialog box list, or clicking the Display button. So, I carefully followed the example in the textbook and added this private function to a VB module called basDisplayQuery. The code is this:

Private Function basDisplayQuery()
'Open the selected query in Datasheet view
DoCmd.OpenQuery lstQueryList, acViewNormal
End Function

I tell Access to compile the code, I save it, then close the VB window and return to the database window.

Next, I open frmQueriesDialogBox is Design view, open the Properties panel, and add =basDisplayQuery() to the On Dbl Click property for the list box, named lstQueryList, and to the On Click property for the Display command button.

Then, I change to Form view to test form. When I try to display a query by clicking on the Display command button or double clicking on the Query name in the list box, I get an error message saying:

"The expression On Dbl Click you entered as the event property setting produced the following error: The expression you enterered has a function name Microsoft Office Access can't find.

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."

Since I believe I've done this exactly as illustrated in the textbook, I have no clue why this error message is occuring. Can someone give me a clue?

 

[pbaldy]

By the look of it, that function is designed to be in the form module. Is it?

 

[dderolph]

If I understand your question, the answer is no. It's in the lstQueryList module and the Display module, since the objective is to display the query by Clicking on the Display button or Double Clicking the query name in the list box. So, you're saying it should be in the Form module? Should it be in all three places?

 

[pbaldy]

The function itself should only be in one place; you can call it from multiple places. You can try to make it work in a standard module by changing Private to Public, but I suspect you'll quickly run into another problem.

 

[jal]

Sounds to me like you mainly need to identify the selected item of the listbox. You should run this detection in two event-subs:

Display Button_Click
ListBox_DoubleClick

Properties > OnClick > EventProcedure (in drop down) > click the ellipsis. This creates the event-sub (do same for dbl click)

Private sub btnDisplay_Click()
Dim queryName as string
'Let's say the query name is in column 3 of the listbox
queryName = listBox1.Column(2, listBox1.ItemsSelected(0))
DoCmd.OpenQuery queryName
End Sub

 

[dderolph]

Can someone download this zipped file of the database, open it, and see whether the frmQueriesDialogBox works the way it should? The reason I'm asking this is that I have some suspicion that it does, indeed, work but that the problem is with my installation of Access 2007 on my notebook PC running Vista Home Premium SP1.

If you look at it, I'd like to mainly know two things. When you open frmQueriesDialogBox, is focus placed on the first query in the list box? In other words, is that query automatically selected so that it can immediately be acted upon? And, second, will a query in the list open when double clicked or when the Display button is double clicked?

This database is a class project and contains fictitious data so I'm not concerned about exposing it. I uploaded the zipped file to http://www.geocities.com/daderolph/Tophill2.zip.

 

[jal]

I don't have Access 2007 so I doubt I can view the file. Did you try my advice above?

 

[dderolph]

I tried your advice but it didn't work. My list box only has 1 column; perhaps I made a mistake with your code. How would it be different for a single-column list box?

Again, I would like to verify whether the list box works right on someone else's computer. I saved my database in Access 2002-2003 format and opened it on a computer with Access 2003. The switchboard did not work right in Access 2003 but the frmQueriesDialogBox did work right. Therefore, I'm wondering some issue with the computer on which I'm running Access 2007 is interfering with proper functioning of the frmQueriesDialogBox. So, as a troubleshooting step, I'd like to know whether the frmQueriesDialogBox works right on someone else's computer, running Access 2007.

 

[jal]

If it's only one column, then the query name is in the first column, in this case use column number 0 (the index runs from 0 upwards instead of from 1 upwards).


Dim queryName as string
queryName = listBox1.Column(0, listBox1.ItemsSelected(0))
MsgBox queryName 'for testing purposes
DoCmd.OpenQuery queryName


You might also try the second line as this:

queryName = listBox1.Column(0)

 

[jal]

I personally avoid the Designer as much as possible. I just use blocks of VBA code because it generally seems simpler. Maybe I'll upload you an example.

 

[jal]

Here's a sample. Sorry about the original sample I gave you - I get Access VBA mixed up with 'regular' Office VBA.