VBA Variables inside DoCmd.RunSQL

Question

Just a question. Situation is as follows:

I have 15 make table queries that pulls data for a different submission clarification code that was used on a claim; i.e. Pull all claims where submission clarification code 5. As of right now I have a macro that will run all 15 queries, but each time I am required to type in the region I am filtering for due to the [What Region?] prompt I had put in the criteria field.

My question is:

Is it possible to use VBA to run all 15 queries using the DoCmd.RunSQL where I only have to type in the region number once and it will apply it to all queries?

My initial thoughts were I would have VBA prompt me for what region I'm filtering for, store that in a variable, and then use that variable in the SQL statement. But I'm not even sure if you can do that? Thanks in advance for any advice that may be given!

Update: So after reading a few threads, I created a mock database to try out some of the concepts and I think I might be on the right track?

Private Sub btnTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim SQLString As String

SQLString = "SELECT tblEmployees.ID, tblEmployees.Last_Name,  tblEmployees.First_Name, tblEmployees.SS_ID INTO Test" _
        & "FROM tblEmployees" _
        & "WHERE (((tblEmployees.ID)=[What number]));"

Set qdf = CurrentDb.QueryDefs("Query1")

qdf.Parameters(0).Value = InputBox("What is the ID Number")

qdf.Execute
qdf.Close

Set db = Nothing
Set qdf = Nothing

End Sub

So to apply this to the 15 queries I would just Dim other variables as DAO.QueryDef right? I'm note really sure i need the SQLString part either? Also, I noticed that when running this mock code it took quite a while for it to create the new table. Is this normal? Also also, the code will not run if the table it is creating already exists. Is there a way to just have the code replace the existing table with the new one? Kind of new to this VBA so thanks for your patience.

Answer 1

Short answer is yes, this is possible. Some keywords you want to familiarize yourself with are "parameters" which are the variables with the prompt and "Querydef" or query definition.

There are quite a few articles detailing how to pass parameter values to a query programmatically. Check out this page for a solid overview of how to accomplish this. Most notably, the last example uses an inputbox to prompt the user to provide the parameter value, which gets you close to what you need. (ie. cmd.Parameters(0).Value = InputBox("Enter a country name"))

Modified to your design, it might be best to create a string variable and ask for the parameter first, then use the variable in declaring the parameters individually, which would permit a single parameter submission that gets applied to all queries.

EDIT

I have adjusted your code to show you how to go about it. You will have to repeat the block for each query.

Private Sub btnTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strParamter As String

Set db = CurrentDb

strParamter = InputBox("What is the ID Number")

Set qdf = db.QueryDefs("Query1")

qdf.Parameters(0).Value = strParameter

qdf.Execute
qdf.Close

'Now move to next query

Set qdf = db.QueryDefs("Query2")

qdf.Parameters(0).Value = strParameter

qdf.Execute
qdf.Close

'...etc.

Set qdf = Nothing
Set db = Nothing


End Sub