Customising parameter queries with forms

[Guest]

I want to create a form that allows users to customise a parameter query, so
that they can set the parameter field as well as the parameter value.

In my form, I have two unbound text boxes that the user fills out,
[ParameterField] and [ParameterValue]. I want the WHERE statement in my query
to be something like WHERE
[Forms]![MyForm]![ParameterField]=[Forms]![MyForm]![ParameterValue], but from
what I can tell it does not read the parameter field as an actual field in
the query. I am using Access 2002.

 

[John Webb via AccessMonster.com]

Murp,

One solution to this problem would be to modify the query SQL via VBA. I
assume you have a command button on your form that displays the query -
well if you attach the following piece of code to the command button click
event it should work:

Private Sub CommandBtn1_Click()

Const conSQL_BEGIN = "Enter your beginning SQL here, i.e SELECT *"
Const conSQL_END = "Enter ending SQL here, i.e ORDER BY clause"

Dim strSQL as string
Dim strParamField as string
Dim strParamData as string

strParamField = Me.ParameterField
strParamData = Me.ParameterValue

'Check that both fields have values
If strParamField = "" Or strParamField = "" Then Exit Sub

strSQL = strParamField & " = '" & strParamData & "'"

CurrentDb.QueryDefs("YOUR QUERY NAME").SQL = conSQL_BEGIN & _
strSQL & conSQL_END

End Sub

Remember to make sure you have spaces in the constants where they are
needed.

Hope that helps, any probs just shout.

John