Query returns no records when records exist

[SteveWillett]

I have an Access continual form with two buttons to open other forms for the selected record. One button works, finds the record, and opens the form, the other reports a Recordset EOF (shows the "No client with clint ID" message).

Any thoughts on what might cause the same query to fail in one place but succeed in another? I am displaying the SQL in both subroutines and they appear identical.

Here is the code for these two buttons:

Private Sub cmdEditSelectedClient_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String ''

Set db = CurrentDb
strSQL = "SELECT * FROM clients WHERE clientID=" & Me.id
MsgBox strSQL
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
DoCmd.OpenForm "Entry Form", acNormal, , , , , Me.id
Else
MsgBox "No client with clientID = " & Me.id
End If
rs.Close

End Sub

Private Sub cmdRescreen_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT * FROM clients WHERE clientID=" & Me.id
MsgBox strSQL
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
DoCmd.OpenForm "Re-screen Form", acNormal, , , , , Me.id
Else
MsgBox "No client with clientID = " & Me.id
End If
rs.Close

End Sub

 

[PHV]

The two buttons are on the very same form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SteveWillett]

Yes, the two buttons are on the same form. See screenshot:

 

[SteveWillett]

Never Mind - the problem was actually on the Re-screen Form in the Form_Open method...

 

[dhookom]

What have you done to trouble-shoot?
Have you set a breakpoint and stepped through the code?


Duane
Hook'D on Access
MS Access MVP