Creating query based on parameter value in VBA

[spudracer]

I've searched all over for this, and I know as soon as I post it, I'll find the answer somewhere, but I need your help!

I've got a database that's a monster. It has at least 15 different queries, and I want to simplify it by doing away with these queries and using code to generate the queries.

VBA continues to be a mystery to me, but I want to learn it.

What I've got it is tblInventory or qryInventory (if I can generate a query from a previously established query). What I want to do is build a query based on who owns what using the Owner field.

I know this is easy (it has to be), but I just can't seem to wrap my head around it.

 

[Isskint]

You are looking at a Dynamic Query. There are a number of options. The easiest is to create a base query on a table listing all records. Then create a 'Query Options' form based on the same table. Set the criteria section of each field in the Query = to the same control on the form. Add a 'Run query' button to the form and hey presto the query will open with the criteria set in the form.

There are plenty of examples on the forum and the right method will depend on your exact requirements.

 

[spudracer]

I could do that, but that's not what I'm wanting. I've even tried the following:

DoCmd.OpenQuery "qryInventoryActive"
DoCmd.RunSQL "SELECT *, FROM tblInventory WHERE [Owner] = " & DEPARTMENT H & ""

EDIT: Figured it out, but man does my head hurt now.