Passing Query Parameters & a Combo Box

[Ken]

I have a combo box that I want to change the query based on inputs to the form.

I have no problem setting the record source of the combo box but I want to
use a query that has a parameter.

How do I pass the parameter to the query in my combo box?


Select Case Me.TabEscorts.Value
Case 0: strSQL = "qryType_03"
Case 1: strSQL = "qryType_04"
Case 2: strSQL = "qryType_03B"
End Select

Each of the queries have a parameter, so how to I pass the parm to the query?

Thanks.

 

[Jeanette Cunningham]

Ken,

You can go
Select Case Me.TabEscorts.Value
Case 0: strSQL = "qryType_03"
Case 1: strSQL = "qryType_04"
Case 2: strSQL = "qryType_03B"
End Select
Me.TheCombo.RowSource = strSQL

Jeanette Cunningham

 

[Ken]

Thanks Jeanette,

I can assign the recordset property of the combo box, but the queries have
parameters - so how do I pass the parameter value to the query?

Ken

 

[Jeanette Cunningham]

Ken,
you can use the Where clause of the query instead of a parameter.
This is not a very specific answer, as I don't have enough info about your
controls and queries.
The query is split into 3 parts. The 1st and 3rd parts do not change, so can
be set up as constants
cstrStub and cstrTail.
The where clause changes depending on the selections user makes on the form.
To build the query combine cstrStub, strWhere and cstrTail


Dim strWhere as String

Const cstrStub = "SELECT yadda, yadda, yadda " _
& "FROM tablename " _
& "WHERE "

Const cstrTail = "ORDER BY yadda, yadda"

Select Case Me.TabEscorts.Value
Case 0: strWhere = "[fieldname] = " & yadda yadda & ""
Case 1: strWhere = "[fieldname] = " & yadda yadda & ""
Case 2: strWhere = "[fieldname] = " & yadda yadda & ""
End Select

'you may be able to use
'strWhere = "[fieldname] = " & Me.TabEscorts & ""
'instead of the Select case routine, I don't have enough info from the post
to say whether this would work.

strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.TheCombo.RecordSource = strSQL


Jeanette Cunningham

 

[Ken]

Thanks Jeanette - the query is very complicated to do with strings as there
are a number of expresions in it. I was hoping to use the query and not have
to use a string.

Ken

Ken,
you can use the Where clause of the query instead of a parameter.
This is not a very specific answer, as I don't have enough info about your
controls and queries.
The query is split into 3 parts. The 1st and 3rd parts do not change, so can
be set up as constants
cstrStub and cstrTail.
The where clause changes depending on the selections user makes on the form.
To build the query combine cstrStub, strWhere and cstrTail


Dim strWhere as String

Const cstrStub = "SELECT yadda, yadda, yadda " _
& "FROM tablename " _
& "WHERE "

Const cstrTail = "ORDER BY yadda, yadda"

Select Case Me.TabEscorts.Value
Case 0: strWhere = "[fieldname] = " & yadda yadda & ""
Case 1: strWhere = "[fieldname] = " & yadda yadda & ""
Case 2: strWhere = "[fieldname] = " & yadda yadda & ""
End Select

'you may be able to use
'strWhere = "[fieldname] = " & Me.TabEscorts & ""
'instead of the Select case routine, I don't have enough info from the post
to say whether this would work.

strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.TheCombo.RecordSource = strSQL


Jeanette Cunningham

Thanks Jeanette,

I can assign the recordset property of the combo box, but the queries have
parameters - so how do I pass the parameter value to the query?

Ken