Pass a value to a parameter query and open the query

[Alan Kidner]

Greetings,

I am sure this should be fairly simple! I have a form
with a text box (unbound) and I want to pass the contents
of the text box to one or more parameter queries and make
them open in the normal way showing the matching records.

Here is the code I have so far. I might be barking up
completely the wrong tree!

Private Sub cmdSearch_Click()
Dim db As Database
Dim qdfParmQry As QueryDef

Me.txtCohort.SetFocus
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Search AON By Cohort")

qdfParmQry.Parameters("[CohortString]").Value = UCase
(Me.txtCohort.Value)

'No problems up to here but how do I open the query so I
can see the results?

'qdfParmQry.Execute 'This gives an error
qdfParmQry.OpenRecordset 'This does nothing
End Sub

Any help would be much appreciated.

Best regards, Alan

 

[Jonathan Parminter]

Hi Alan,
perhaps an easier approach is in the design view of each
query, for the field/column, Cohort, criteria row have a
reference to the control on the form. for example...

forms![formName]!txtCohort

then then cmdSearch_Click() event just needs to open the
query.

docmd.openquery "Search AON By Cohort"

Luck
Jonathan

 

[Alan]

Your solution worked and is much simpler! I didn't know
before how to reference the form from the criteria of the
query. This is very helpful.

Many thanks, Alan.

-----Original Message-----
Hi Alan,
perhaps an easier approach is in the design view of each
query, for the field/column, Cohort, criteria row have a
reference to the control on the form. for example...

forms![formName]!txtCohort

then then cmdSearch_Click() event just needs to open the
query.

docmd.openquery "Search AON By Cohort"

Luck
Jonathan

-----Original Message-----
Greetings,

I am sure this should be fairly simple! I have a form
with a text box (unbound) and I want to pass the contents
of the text box to one or more parameter queries and make
them open in the normal way showing the matching records.

Here is the code I have so far. I might be barking up
completely the wrong tree!

Private Sub cmdSearch_Click()
Dim db As Database
Dim qdfParmQry As QueryDef

Me.txtCohort.SetFocus
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Search AON By Cohort")

qdfParmQry.Parameters("[CohortString]").Value = UCase
(Me.txtCohort.Value)

'No problems up to here but how do I open the query so I
can see the results?

'qdfParmQry.Execute 'This gives an error
qdfParmQry.OpenRecordset 'This does nothing
End Sub

Any help would be much appreciated.

Best regards, Alan

.

.