Passing parameter to query without using Enter Parameter window

  • Thread starter Thread starter Mike P
  • Start date Start date
M

Mike P

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
 
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
 
THANK YOU so much. This worked wonderfully ! ! ! Now a little fine tuning...

Two more questions.
1) I've enhance the logic to put the output from 4 queries in a single xls
file. Since I am generating the output file name, not using the Const. The
process does not generate a new worksheet for each query, it overrights the
previous worksheet. Therefore, I do not have one xls with 4 worksheets. I
have an xls with 1 worksheet overwritten four times. It seems like if i make
the filename a Const, I get 4 workwsheets, but i want to use a variable
filename, not a constant. Any ideas?

2) I would like to make the worksheet name differenet than the query name.
Is this possible too?

Thanks again,
Mike


Ken Snell (MVP) said:
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 said:
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
Click to expand...
Click to expand...
 
Comments inline..

--

Ken Snell
<MS ACCESS MVP>


Mike P said:
THANK YOU so much. This worked wonderfully ! ! ! Now a little fine
tuning...

Two more questions.
1) I've enhance the logic to put the output from 4 queries in a single xls
file. Since I am generating the output file name, not using the Const.
The
process does not generate a new worksheet for each query, it overrights
the
previous worksheet. Therefore, I do not have one xls with 4 worksheets.
I
have an xls with 1 worksheet overwritten four times. It seems like if i
make
the filename a Const, I get 4 workwsheets, but i want to use a variable
filename, not a constant. Any ideas?
Click to expand...

The default behavior of the TransferSpreadsheet action is to use the query
name as the new Range value in the EXCEL workbook that is receiving the
data. If the Range does not exist in that EXCEL file, a new worksheet is
created with the same name as the Range, and the data are written on that
worksheet. If the worksheet already exists with the Range name and the Range
is on that worksheet, TransferSpreadsheet will overwrite the data on that
worksheet. If the Range doesn't exist on that worksheet, even though the
worksheet has the same name as the Range (also the query name), a new
worksheet is created with a "1" appended to the worksheet name, and the data
are written onto this new worksheet. See this blog by Alex Dybenko, MVP, for
more details about how TransferSpreadsheet works for exports, and how the
undocumented Range argument can be used at times:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

Quite honestly, the easiest way to ensure that the data are written onto
separate worksheets is to ensure that the exported query's name is different
from the other queries being exported. You can do this by appending some
"incrementing"/"changing" value to the end of the query name in the
TransferSpreadsheet method.

2) I would like to make the worksheet name differenet than the query name.
Is this possible too?
Click to expand...

See my info above about the "Range" argument as detailed in Alex' blog. It's
tricky and can be inconsistent, so it's best to rename your query to the
name you want the worksheet to have and then do the export.

Thanks again,
Mike


Ken Snell (MVP) said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top