RUN QUERY WITH VARIABLE IN FORM

[ciel220]

Hello,

Is there a way that I can run a query using a variable in a
form?

I have several tables:
TblA
TblB
TblC
...

I have a combo box namely, TableName, which user can browse and select whichever table that want to query with.

What I want is, create a single query (instead of multiples queries for each table) and get the table name to be query through the form.

For instance:
"SELECT * FORM FORMS![MYFORM]![TableName]

How could I do that?
Please shed some light of this!

Thanks a bunch!
ciel220

 

[gnt]

ciel220-
If I understand you correctly, all you want to do is query the table that the user chooses from the combo box? If this is the case then you're right on track with your syntax:

SELECT *
FROM [Forms]![MyForm]![TableName];

Does this not work?

-gnt

 

[ciel220]

Hi,

Thanks for your reply.
I was trying to use that SQL and I got a syntax error from Access. IT says "There is a syntax error on your FROM clause".

Am I fdoing sth wrong or is it possible to do things like that in a SQL clause?

Thanks!

 

[gnt]

It's possible to that, I do it all the time. Is that your whole SQL clause? Or is there more to it? Can you copy and paste the whole thing into your post?

 

[ciel220]

Hi gnt =)

Thanks, here it is:

SELECT *
FROM [Forms]![Search Engine]![TblName];

And I got 'error in the FORM clause' @_@

Thanks!

 

[gnt]

ciel220-
I think you're going to have to set the query's SQL from VBA. Try putting this code in the afterupdate of the combobox:

Dim db As Database
Dim qy As QueryDef

Set db = CurrentDb()
Set qy = db.QueryDefs("YourQueryName")

qy.SQL = "SELECT * FROM " & Me!TblName & ";"

That way, each time you update the combo box the query will change. Let me know if that doesn't work.
-gnt