Re: Passing parameter to query without using Enter Parameter window

I've posted (at end of my post here) some example code to do essentially
what you want; this code was written to allow filtering by managers (in your
case, customer number) and exporting each manager's data to a separate EXCEL
workbook file.

For your situation, you will want to use a query that does not have a
parameter for CustomerNumber in it. This code (after you've modified it)
will do the filtering for you without having that parameter in the query.

This should give you an idea of how to approach your scenario. Post back if
you have additional questions for your specific setup.


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
worksheets in a single EXCEL file
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "PutEXCELFileNameHereWithout.xls"
Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>



"Mike P" <MikeP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:54EFF762-914C-40E6-BC3A-896DC8AB5FF7@xxxxxxxxxxxxxxxx
I have a query that accepts a parameter to run. The queary with the
parameter, CustomerNumber, runs fine when I do one at a time and the Enter
Parameter window pops up. However, I wish to pass the parameter to the
query
programaticlly and not have the Enter Parameter window pop up. This will
allow me to pull the parameters from a tmp table and produce outputted xls
without requiring human intervention.

I realize I can take the SQL logic and write it in the code, but the SQL
is
large and complex. It would be much easier to just feed the parameter
into
the query programaticlly. Is this possible? I have read a bunch, but
can't
find the anser.

Dim strCustomerNumber As String, _
strQuery As String,
strFile As String

Do while moreCustomerNumbersExists
strCustomerNumber = "test123" ' this will be put in a loop and pulled
from a tmp table
strFile = CustomerNumber & ".xls"
'How do I call my query by sending in the paramenter strCustomerNumber
so
that the Enter Parameter window does not pop up???
' something like - qryGetCustomerData, Parameter=strCustomerNumber" ? ? ?
?
DoCmd.OutputTo acOutputQuery, "qryGetCustomerData", acFormatXLS,
strFile, True
Loop



.