Problems with OpenRecordset (using a query)

  • Thread starter Thread starter Phil Hood
  • Start date Start date
P

Phil Hood

I get the following error when running the code below from
a command button on a form:

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

The error seems to relate to the line:

Set rstRiders = dbsCurrent.OpenRecordset(strQuerySQL)

I'm using Access97.

Please help.

Thanks

Phil.


-----------------------------------------
Private Sub History_Click()

Dim dbsCurrent As Database
Dim rstRiders As Recordset
Dim rstTotal As Long
Dim strQuerySQL As String
Set dbsCurrent = CurrentDb
strQuerySQL = "SELECT RiderTeam.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderTeam ON Riders.RiderID =
RiderTeam.RiderID WHERE ((([RiderTeam]![RiderID])=[Forms]!
[Riders]![RiderID]));"
Set rstRiders = dbsCurrent.OpenRecordset(strQuerySQL)

rstRiders.MoveLast
rstTotal = rstRiders.RecordCount

If rstTotal > 0 Then DoCmd.OpenForm "RiderTeam" Else
DoCmd.OpenForm "RiderTeam2"

End Sub
-------------------------------------------

I get the same error if I refer to the query directly
("Query6") rather than incorporating the SQL.

--------------------------------------
Private Sub History_Click()

Dim dbsCurrent As Database
Dim rstRiders As Recordset
Dim rstTotal As Long

Set dbsCurrent = CurrentDb
Set rstRiders = dbsCurrent.OpenRecordset("Query6")

rstRiders.MoveLast
rstTotal = rstRiders.RecordCount

If rstTotal > 0 Then DoCmd.OpenForm "RiderTeam" Else
DoCmd.OpenForm "RiderTeam2"


End Sub
---------------------------------------
 
The SQL statement contains a reference to a control on a form. In the
context of the query, this can be resolved by the Expression Service.
However the Expression Service is not available when you OpenRecordset.

To resolve that, concatentate the value from the text box into the query
string:
strQuerySQL = "SELECT RiderTeam.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderTeam ON Riders.RiderID =
RiderTeam.RiderID WHERE RiderTeam.RiderID = " & [Forms]![Riders]![RiderID] &
";"

Note: If RiderID is a Text field you need extra quotes:
....WHERE RiderTeam.RiderID = """ & [Forms]![Riders]![RiderID] & """;"
 
Hi Allen,

This worked a treat. Many thanks

Phil
-----Original Message-----
The SQL statement contains a reference to a control on a form. In the
context of the query, this can be resolved by the Expression Service.
However the Expression Service is not available when you OpenRecordset.

To resolve that, concatentate the value from the text box into the query
string:
strQuerySQL = "SELECT RiderTeam.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderTeam ON Riders.RiderID =
RiderTeam.RiderID WHERE RiderTeam.RiderID = " & [Forms]! [Riders]![RiderID] &
";"

Note: If RiderID is a Text field you need extra quotes:
....WHERE RiderTeam.RiderID = """ & [Forms]![Riders]! [RiderID] & """;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Phil Hood said:
I get the following error when running the code below from
a command button on a form:

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

The error seems to relate to the line:

Set rstRiders = dbsCurrent.OpenRecordset(strQuerySQL)

I'm using Access97.

Please help.

Thanks

Phil.


-----------------------------------------
Private Sub History_Click()

Dim dbsCurrent As Database
Dim rstRiders As Recordset
Dim rstTotal As Long
Dim strQuerySQL As String
Set dbsCurrent = CurrentDb
strQuerySQL = "SELECT RiderTeam.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderTeam ON Riders.RiderID =
RiderTeam.RiderID WHERE ((([RiderTeam]![RiderID])= [Forms]!
[Riders]![RiderID]));"
Set rstRiders = dbsCurrent.OpenRecordset(strQuerySQL)

rstRiders.MoveLast
rstTotal = rstRiders.RecordCount

If rstTotal > 0 Then DoCmd.OpenForm "RiderTeam" Else
DoCmd.OpenForm "RiderTeam2"

End Sub
-------------------------------------------

I get the same error if I refer to the query directly
("Query6") rather than incorporating the SQL.

--------------------------------------
Private Sub History_Click()

Dim dbsCurrent As Database
Dim rstRiders As Recordset
Dim rstTotal As Long

Set dbsCurrent = CurrentDb
Set rstRiders = dbsCurrent.OpenRecordset("Query6")

rstRiders.MoveLast
rstTotal = rstRiders.RecordCount

If rstTotal > 0 Then DoCmd.OpenForm "RiderTeam" Else
DoCmd.OpenForm "RiderTeam2"


End Sub
---------------------------------------
Click to expand...


.
Click to expand...
 
Back
Top