problems with query used as recordset

[Kevin_S]

Hi All -

I am trying to use a query with a where clause as the source for a recordset and I keep getting this funky error:

Error -2147217904 (No value given for one or more required parameters)

The strange part of this is that if I copy/paste the exact same SQL into the query designer and launch the query from my form it runs fine... just not as the source for the recordset? Here is the exact code:

Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strSITEID As String

strSITEID = Forms!frmTOC!subCombo!cmboListSite
Set rst = New ADODB.Recordset
strSQL = "SELECT dbo_BAS_SkidPier.UniqueSPID, dbo_BAS_SkidPier.SITEID FROM dbo_BAS_SkidPier WHERE (((dbo_BAS_SkidPier.SITEID)=[forms]![frmTOC]![subCombo]![cmboListSite]));"
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
MsgBox rst.RecordCount

rst.Close
Set rst = Nothing

Now the criteria is not null or empty (checked already) and if I take the where clause out the code runs fine...? Any ideas?

Thanks,
Kev

 

[Kevin_S]

Nevermind... Got it

Thanks Anyway...
Kev

 

[snicker]

I'm having the same problem. here is my code:

Private Sub cboClientName2_AfterUpdate()
Dim rst As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT Count([TrustPinNumbers].[PinNumber]) AS CountOfPinNumber FROM TrustPinNumbers WHERE ((([TrustPinNumbers].[ClientNum])=[Forms]![BCBTaxPayments]![txtClientNum2]));"
rst.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
MsgBox rst.RecordCount
If rst.RecordCount < 1 Then
MsgBox "test"
End If

How did you fix it?

Thx Mike

 

[pono1]

Mike,

Something like this (untested) should get you closer to a solution.

Dim rst As New ADODB.Recordset
Dim SQL As String

SQL = "SELECT Count([TrustPinNumbers].[PinNumber]) AS " & _
	"CountOfPinNumber FROM " & _
	"TrustPinNumbers WHERE " & _
	"[TrustPinNumbers].[ClientNum]=" & _
"[Forms]![BCBTaxPayments]![txtClientNum2];"

rst.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic


If rst.RecordCount < 1 Then
	MsgBox "test"
Else 
	MsgBox rst.RecordCount
End if

Regards,
Tim