Solved: How to run a specific query based on form data

[albychang321]

Hi, I have a question about how to call a particular query from a Form.

This is the setup:

I’m using Access 2007 on Windows XP.

I have a form where the name of a query is displayed depending on what the user selects. These queries are already built and I just want to be able to call a specific query based on what the user selects.

The name of the query is put together based on what the user chooses in a couple of combo boxes. That query name is stored in a List Box on the form that is named QueryNameToUse. I want to be able to press a button and have that query pop up.

Is there a way to call that particular list box when the button is pressed? This is the code on that event procedure:

------------------
Private Sub Show_SP_Click()
On Error GoTo Err_Show_SP_Click

Dim stDocName As String

stDocName = Forms![FormName]![QueryNameToUse]
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Show_SP_Click:
Exit Sub

Err_Show_SP_Click:
MsgBox Err.Description
Resume Exit_Show_SP_Click

End Sub
-------------------

When I run this however, I get the following error:
“The expression you entered has a field, control, or property name that Microsoft Office Access can’t find.”

I’m guessing my syntax is off or QueryNameToUse is not actually storing the value I want. Do you know what I need to do to get this to work correctly?

Thanks in advance!

[CreganTur]

Welcome to the forum- it's always good to see new members.

It is very easy to pull data from a form object and use it as a part of a VBA procedure. In your case, I believe the problem is how you are pointing to the desired form object.

Since the listbox is on the same form that this code is behind, you can point to it using the Me keyword. Me refers to the form that the code module is behind. This means that you can refer to your listbox using "Me.QueryNameToUse". When you do this, it will automatically pull the value of the selection (but only for the first column. If you have multiple columns, then this gets a bit more complicated).

One of the best things about the Me keyword is that it works with intellisense. Type "Me.", and you'll get a list of all of the form objects, properties, and methods that you can use with the Me object.

The following code should work for you. Also, you don't need any of the bloat code that access automatically generated for you.

Sub Show_SP_Click()

DoCmd.OpenQuery Me.QueryNameToUse

End Sub

HTH:thumb

[albychang321]

Thank you for the quick response!

I tried that out, but still get an error. This time it is "An expression you entered is the wrong data type for one of the arguments."

Upon further review, I notice that when I look at the form in Datasheet View, "#Name?" is displayed under the QueryNameToUse label. However, when looking at it in Form View, the correct query name is listed. I have the same Select query for both the row source and control source (btw, sorry if this isn't the correct way to use these, I'm a newbie and just kind of forcing things in until it works) and it is:

SELECT WhichTable.QueryToUse FROM WhichTable WHERE (((WhichTable.Argument1)=Forms!FormName!Arg1) And ((WhichTable.Argument2)=Forms!FormName!Arg2));

Do you know why it would display correctly in Form View, but have the #Name? when looking at it in Datasheet View? I guess that may be why I'm getting an error.

Thanks again, sorry for all the questions...

PS Thanks for the tip on the "Me." that will definitely make a lot of my other forms much cleaner.

[CreganTur]

I tried that out, but still get an error. This time it is "An expression you entered is the wrong data type for one of the arguments."

When does this error occur? When trying to load the listbox, or when you try to open the selected query?

What is the data type for the field QueryToUse?


Upon further review, I notice that when I look at the form in Datasheet View, "#Name?" is displayed under the QueryNameToUse label. However, when looking at it in Form View, the correct query name is listed.
Datasheet view is only for viewing records. Form view is for creating interactive user form's. That's why the listbox doesn't work in Datasheet view.


I have the same Select query for both the row source and control source
You only need to set the RowSource. ControlSource links an object to the table than controls the Form, if one exists. It is only useful when you want to pull the field value of a record directly from the controlling table. You don't need to set a cotnrolsource for your listbox, just the rowsource.

Objects without a controlsource are called unbound objects, because their values are not bound to record values in a table or query object.

[albychang321]

The error occurs when I click the button on the form to open the query.

QueryToUse is a Text field in a table.

[CreganTur]

Can you post your database so we can take a look at it and see what's going on? Post a stripped down version if you need to so no sensitive information is revealed. Be sure to provide us with a 2003 compatible DB- most people don't have 2007. And you'll need to zip it in order to post it.

[albychang321]

Sorry about the late reply, I have been traveling recently and didn't get a chance to come back to this.

Unfortunately, I can't send even a stripped down version of the database I'm working on, but I did create a quick simplified version of what I'm trying to do.

Basically there's different queries based off of data in different tables. The form takes inputs to determine which set of data to use and I want to return the query that is chosen based off of those inputs. The query name is stored in a listbox (I think).

[OBP]

This is one way to do what you want.

[albychang321]

OBP -
I have not been able to open the Form on the example database you sent (it keeps saying that "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again" which it still says even after i restart), however I was able to view the VBA. I put the code in and I set up a text box (naming it Text6), but while I can get it to show the table name, I can't get it to show the query name and I think I must be missing something on the form that would return what I need.

[OBP]

That is weird, because both my copies now do the same thing.
What I did was to change the List to a text box, which is populated by the second Column in the Table Combo, which I added to list the query associated with the table.
that is probably the part that you are missing.

[OBP]

Here is another version.

[albychang321]

Thanks OBP!

I had to change my form around a bit, but it works now. Instead of having multiple combo boxes that formed a single input to choose which table, I put it all into one combo box. I'm still not sure how to do it using multiple inputs, but this way works just fine.

Thanks!