Setting Query Parameters

[FBxiii]

Hi.

I am trying to use code to reference different queries as recordsets but
keep getting parameter errors.

I am using For Each Parameter in qdef.Parameters to retrieve each
Parameter.Name and storing them in an array.

How do I use the Names stored in the array to retrieve the required value or
values? I can then set the parameter using code and use Set rst =
qdef.OpenRecordset

e.g. The value in the textbox : Forms!frmWorkflow!ID

Cheers,
Steve.

 

[Allen Browne]

If all the parameters refer to controls on the form, you should be able to
use Eval() to get Acess to evaluate the name:

dim prm As DAO.Parameter
For each prm in qdf.Parameters
prm = Eval(prm.Name)
Next

Personally, I find it easier and more maintenace-free just to build the SQL
statement as a string in code rather than using the saved query with
parameters. You just concatenate the values into the string, e.g:
strSql = "SELECT * FROM Table1 WHERE ID = " & Forms!frmWorkflowID & ";"

 

[FBxiii]

Thanks for the advice Allen.

My DB uses different Work Queues and filters using saved queries. The
queries can reference different text boxes from various forms so I need to be
flexible.

I have managed to what I wanted though using Forms(*Form
Name*).Controls(*Control Name*) to get and set the required value!!!

Sub a()

Dim qdef As QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs("qryFilter_MRA_Returns")

Dim intParam As Integer
Dim strParams(10) As String

For Each Parameter In qdef.Parameters

qdef.Parameters(intParam) =
Forms(Get_Form_Name(Parameter.Name)).Controls(Get_Control_Name(Parameter.Name))
intParam = intParam + 1

Next Parameter

' Open the recordset and no parameter queries should appear!
Set rst = qdef.OpenRecordset

End Sub

Public Function Get_Form_Name(strParameter As String) As String

' Gets the form name from the passed parameter e.g.
Forms!frmWorkflow!txtUserID
Dim intChr As Integer

' Loop through the parameter to find the 2nd exclamation.
' The parameter always starts with Forms! so we can start at the
' 7th char.

For intChr = 7 To Len(strParameter)
Debug.Print Mid(strParameter, intChr, 1)
If Mid(strParameter, intChr, 1) = "!" Then Exit For
Next intChr

Get_Form_Name = Mid(strParameter, 7, intChr - 7)

Stop

End Function

Public Function Get_Control_Name(strParameter As String) As String

' Gets the form name from the passed parameter e.g.
Forms!frmWorkflow!txtUserID
Dim intChr As Integer

' Loop through the parameter to find the 2nd exclamation.
' The parameter always starts with Forms! so we can start at the
' 7th char.

For intChr = 1 To Len(strParameter)
If Mid(strParameter, Len(strParameter) - intChr, 1) = "!" Then Exit
For
Next intChr

Get_Control_Name = Right(strParameter, intChr)

End Function


Thanks!