Parameter Query

Z

ZoZo

Anybody knows how to write a parameter query and pass the
parameter to it using VBA? Your help is appreciated.

Thanks.
 
T

Terri

If I understand what you are wanting, here is how I found
to do it. Put this code in a module
---------------
Function ReturnCustomerId()

ReturnCustomerId = SaveCustomerId

End Function
-----------------------
SaveCustomerId is defined in a module as

dim Global SaveCustomerID as string

-------------------------
From a List of Customers, when the user selects one to
query on I then move this CustomerId to the
SaveCustomerId, then when I run the query, it in turn
runs the function specified in the criteria and returns
the correct record.

Then in your Query the criteria would look like this

ReturnCustomerId()

Hope this helps.

Terri
 
G

GVaught

Try:
PARAMETERS [Enter Whatever here] datatype;
SELECT fieldname, fieldname
FROM tablename
WHERE fieldname=[Enter whatever here]
ORDER BY whatever
 
D

Douglas J. Steele

Based on the Access 97 Help file:

Sub ParameterX()

Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As DAO.Parameter
Dim prmEnd As DAO.Parameter

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _

"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID")
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd

' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#

dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _
prmFirst As DAO.Parameter, dteFirst As Date, _
prmLast As DAO.Parameter, dteLast As Date)
' Report function for ParameterX.

Dim rstTemp As DAO.Recordset
Dim fldLoop As DAO.Field

' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast

' Enumerate recordset.
Do While Not rstTemp.EOF

' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop

Debug.Print
rstTemp.MoveNext
Loop

rstTemp.Close

End Sub

The only changes I made to the above was to add DAO to provide
disambiguation of those objects with the same name in both the ADO and DAO
models.
 
Top