What's wrong with this recordset function? 1

[drazeni]

Hi everybody,

My aim is to return a count on the number of records returned from a table, matching the user entry on a form field (the value for which I pass from the form field "preorder_no" to the variable "var_preorder_no" and then include the variable in the strSQL definition) so that an IF statement, on the form calling this function, would then evaluate the answer and perform the appropriate action.

Please peruse the following code and tell me what's wrong with creating this recordset. One thing I noticed was that if I randomly picked an order number from the table and explicitly state it in the strSQL variable, it then returns the records.

Function CountRecords(var_preorder_no As String) As Long

'this function counts the number of records in a query

Dim myDB As Database
Dim ctr As Integer, num As Integer
Dim myrs As Recordset

On Error GoTo points_Err:

strSQL = "SELECT * " _
    & "FROM PreOrders " _
    & "WHERE preorder_no = " &

var_preorder_no

 & ";"


Set myDB = DBEngine.Workspaces(0).Databases(0)
Set myrs = myDB.OpenRecordset(strSQL)

If myrs.BOF = True And myrs.EOF = True Then
    num = 0
Else
    myrs.MoveFirst
    myrs.MoveLast
    num = myrs.RecordCount
End If

myrs.Close

CountRecords = num

Exit Function

points_Err:
MsgBox "Error " & Err & "has occurred " & vbCrLf & Error
Exit Function

End Function

I get the following error: "Error 3061 has occurred. Too few parameters. Expected 1" and it seemingly doesn't recognise the "var_preorder_no" in the strSQL definition.

If there is an alternative method to return a record count to a variable, based on a form field, I'M ALL EYES .

Any comments will be appreciated (newbie to recordsets). "All is not as it seems"

 

[jhall156]

If preorder_no is a string as I suspect it is then you need to wrap it up in some quotes. I prefer to use char(34) some people like to double up the quotes.

You could make your query SELECT count(fieldname) As NumRecs FROM tablename WHERE condition


then MyResult = MyRecordSet.Fields("NumRecs&quot.Value

If it were SQL Server T-SQL it'd be easier, but as it is your method is as good as any I'll warrant. JHall

 

[ssecca]

THe variable you are passing is a string it needs quotes to run correctly

Try this

"WHERE preorder_no = '" & var_preorder_no & "';"

The single quotes inside the double quoted area will solve the problem.

Good Luck
Tom

 

[drazeni]

You guys are truly KINGS (or queens if you're female )!!!! It worked (I always battle with the quote story).

Thank you SSECCA, after a lot of playing around, your solution turned out to be simple.

"All is not as it seems"