Coding for an event procedure - error?

[philhood2]

I have the following code for an event procedure of a
control on a form.

-----------------------------------------
Dim dbsCurrent As Database
Dim rstRides As Recordset
Dim rstTotal As Integer
Dim strQuerySQL As String

Set dbsCurrent = CurrentDb

strQuerySQL = "SELECT Results.MatchID FROM Results WHERE
(([Results]![MatchID]= [Forms]![Heat]![Results
subform].Form![MatchID]) AND ([Results]![RiderID]= [Forms]!
[Heat]![Results subform].Form![Combo18]));"

Set rstRides = dbsCurrent.OpenRecordset(strQuerySQL)

rstRides.MoveLast
rstTotal = rstRides.RecordCount

Me.RideNo = rstTotal
----------------------------------------

However, I get an error message

Run-time error '3061':
Too few parameters. Expected 2

(The line 'Set rstRides = dbsCurrent.OpenRecordset
(strQuerySQL)' is highlighted if I click debug)

But if I overwrite:

[Forms]![Heat]![Results subform].Form![MatchID]
and
[Forms]![Heat]![Results subform].Form![Combo18]

with numeric values, then the procedure runs and populates
RideNo with a value.

Any ideas what I'm doing wrong?

Any help would be gratefully received.

Thanks

Phil.

 

[Allen Browne]

The Expression Service is not available in the context of the DAO Recordset
to resolve the values of the text box and combo.

Concatenate the values into the string instead, i.e.:

strQuerySQL = "SELECT Results.MatchID FROM Results WHERE
(([Results]![MatchID] = " & _
[Forms]![Heat]![Results subform].Form![MatchID] & _
") AND ([Results]![RiderID]= " & _
[Forms]![Heat]![Results subform].Form![Combo18] & "));"

 

[Dan Artuso]

Hi,
You have to place your form references outside of your quotes so that Access can
evaluate them. The way you have it, you are using the literal strings.

strQuerySQL = "SELECT Results.MatchID FROM Results WHERE " & _
(([Results]![MatchID]= " & _
[Forms]![Heat]![Resultssubform].Form![MatchID] & ") AND (" & _
"[Results]![RiderID]= " & [Forms]! [Heat]![Results subform].Form![Combo18] & "));"

I've assumed that the values are numeric. If they are strings, you'll have to delimit them with quotes:

strQuerySQL = "SELECT Results.MatchID FROM Results WHERE " & _
(([Results]![MatchID]= '" & _
[Forms]![Heat]![Resultssubform].Form![MatchID] & "') AND (" & _
"[Results]![RiderID]= '" & [Forms]! [Heat]![Results subform].Form![Combo18] & "'));"