Outputing queries with parameters in VBA (1 Viewer)

[FLabrecque]

I want to output a query that uses parameters. How do I do that? Let's say I have 2 parameters, PDIV and PWHSE. How do I change the following line of code to use the parameter?

DoCmd.OutputTo acOutputQuery, strQuery, acFormatXLS, strFile, False

 

[FLabrecque]

*Bump*

Is there a way to do this? Do you need more explanations?

 

[pono1]

I may not understand...but I'll throw out a suggestion. Can you create and save a query that accepts input from two textboxes on a form (hidden or otherwise) using the query's criteria row?

'Criteria for a field in query
[Forms]![FrmParams]![TextBoxName1]

'Criteria for another field in query
[Forms]![FrmParams]![TextBoxName2]

And then fill the textboxes with your variables before outputting to your Excel file using the saved query?

'Set parameters.
   Forms!TextBoxName1 = PDIV
   Forms!TextBoxName2 = PWHSE

'Create file from query.
   DoCmd.OutputTo acOutputQuery, "QryName", acFormatXLS, "C:\File1.xls"

Regards,
Tim

 

[FLabrecque]

Thanks for your suggestion, but I'm not using it with any textbox. What I am doing, is running a recordset with my 30 divisions and warehouses. I want to output the same query, but only with the data relating to them (and thus, the parameters).

Currently, I have a dummy table to which I update the PDIV and PWHSE. It works great, but from a normalization point of view, not so great.

 

[mresann]

Where are the criteria, PDIV and PWHSE coming from, specifically?

 

[FLabrecque]

They are coming from a recordset. I have a table detailing my 30 Divisions & Warehouses. I want to loop my recordset, and output in excel format the same query, but with different parameters.

 

[mresann]

Let me summarize (or summarise for you in UK) this to the best of my understanding of your problem.

• You have a table containing your Divisions and Warehouses.

• Each record in that table corresponds to a division or warehouse, each with several items of data.

• One of the fields in the table correpsponds to a "code", such as 'PDIV' or 'PWHSE'

However, your parameter choices make it hard for me to follow a particular point. You use 'PDIV' and 'PWHSE', which I'm guessing are codes for "Division" and "Warehouse". But you say you have 30 "Divisions and Warehouses". Therefore is the code PDIV unique to a SINGLE record, or is it includive of ALL records that meet the criterion of "Division" (as PWHSE would indicate "Warehouse")?

 

[FLabrecque]

I'm sorry if I've skipped a few explication here and there. It's always easier when you're talking about something you understand. Let's table an example with my table BRANCH. Division and Warehouse are my unique key (put together). Here's a sample:

TABLE BRANCH
DIV (Division) WHSE (Warehouse)
A1 A
A1 B
A1 C
...

I have a recordset which "SELECT * FROM BRANCH ORDER BY DIV, WHSE". So far, so good. This recordset loops for my 30-something divisions and warehouses. Which each loop, I want it to output the query contained in my variable strQuery. This query uses 2 parameters, defined in it as PDIV and PWHSE. The P stands for parameters, and the other characters for the column name to which they apply.

This next line of code outputs the query, but doesn't use the parameters. As a result, I have a superb input box that appears, asking me to enter a value for PDIV, and then PWHSE.

DoCmd.OutputTo acOutputQuery, strQuery, acFormatXLS, strFile, False

What I want, is to know how to output a query with parameters. The solution with textboxes would work, but I'm not asking for a way around the problem. I want a solution.

Can you, yes or no, output a query with parameters through VBA?

 

[mresann]

OK, gotcha. Just change the strSQL query to the tables and fields you use in your other tables. Make sure you use DAO reference in your DB. Incidentally, if you use any query other than a normal SELECT query, you may have to use the DoCmd.SetWarnings command to suppress warnings related to database adjustments.

Sub subListQueries()

PROC_DECLARATIONS:
   Dim rstBranch As DAO.Recordset
   Dim strQuery  As String
   Dim PDIV      As String
   Dim PWHSE     As String
   
PROC_START:
   On Error GoTo PROC_ERROR
   Set rstBranch = CurrentDb.Recordset("SELECT Division, Warehouse FROM BRANCH ORDER BY Division, Warehouse")
   
PROC_MAIN:
   rstBranch.MoveFirst
   Do Until rstBranch.EOF
      'Assign variables to current recordset field values
      PDIV = rstBranch("Division")
      PWHSE = rstBranch("Warehouse")
      
      'build the query from parameters derived from BRANCH table
      strQuery = "SELECT Data1, Data2, Data3 FROM tblCheckStuff "
      strQuery = strQuery & "WHERE Division = '" & PDIV & "'"
      strQuery = strQuery & " AND Warehouse = '" & PWHSE & "'"
      
      'Now run the query
      DoCmd.RunSQL strQuery
      
      'go to next Branch Table record
      rstBranch.MoveNext
   Loop
   
PROC_EXIT:
   Exit Sub

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: subListQueries" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Sub