Unexpected result from Query run from VBA

Question

I have problem in the following code :

Set con = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    stSql = "SELECT * FROM [SB Items] "
    stSql = stSql & "WHERE [SBID]=" & Me![SBID] & " AND [ItemNumber]=" & intBtn
    rs.Open stSql, con, 1    ' 1 = adOpenKeyset

    ' If no item matches, report the error and exit the function.
    If (rs.EOF) Then
        MsgBox "There was an error reading the SB Items table."
        rs.Close
        Set rs = Nothing
        Set con = Nothing
        Exit Function
    End If

Since when i execute the above sql command inside stSql which resulting in only ONE row

The control is going to the error block

and message box poping out saying "There was an error reading the SB Items table"

Answer 1

2 Things:

1. I noticed that you set stSQL twice, I'm guessing for reasons of line breaking. An easier way to do this is to use " _" (a space followed by an underscore). This lets you carry a command over to the next line.

2. Change your Msgbox error from:
   MsgBox "There was an error reading the SB Items table."
   to:
   Msgbox "Error: " & err.desc

Edit: The err.desc gives you the exact error code generated. It isn't always helpful, but it's the best you can get.

Answer 2

Use this code to see how many records you get back.

if rs.Supports(adApproxPosition)=true then
  i=rs.RecordCount
  MsgBox "The number of records is: " & i
end if
'
' If no item matches, report the error and exit the function.
If (rs.EOF) Then
'and on as you have it

Also, report back err.desc for more info, if there is any.

Finally, check that Con is attached to the database you think you are using!

Answer 3

You don't have an error block, you have a check for rs.eof.

I tend to prefer checking for zero rows with

if (rs.eof) and (rs.bof)..

since this condition can only be true if the record set is empty and not just when the pointer isn't where you expect it to be or a fat cursor is still populating.

Are you sure the code isn't doing exactly what it was designed to do and showing the message when there are no rows?

Try a debug.print stSQL to get the actual query being run and try to run that directly against the database. This will confirm two things:

1. The Query is formed like you expect it including the values you are pulling off the form
2. The data in the table actually contains a row that should have been caught.