FindFirst Problem

[Ken]

I can't seem to get a match on a record I know is there
using the FindFirst method. I'm using FindFirst since
this is a dynaset (It's a linked table via ODBC).

Here's my code. As I step through it, it always comes up
as a no match:

Dim db As Database
Dim Rec As Recordset

Set db = CurrentDb()
Set Rec = db.OpenRecordset("MWPAT", dbOpenDynaset)

Rec.FindFirst "'Chart Number' = '" & Me.PatientID
& "'"

If Rec.NoMatch Then
Rec.Close
MsgBox "Patient data not found!", vbCritical, ""
Exit Sub
Else
If IsNull(Rec("Last Name")) Then
strLastName = ""
Else
strLastName = Rec("Last Name")
End If

Thanks in advance.

Ken

 

[Guest]

Take the apostrophes out!

"'Chart Number' = '" & Me.PatientID

It should look like this:
"[Chart Number]='" & Me.PatientID & "'"

But only if Me.PatientID is alpha-numeric do you need to
close it in with apostrophies, otherwise it should look
like this:

"[Chart Number]=" & Me.PatientID

One small annoyance in this whole problem is caused when
you use spaces in a field name. The causes additional
work similar to the problem above. If the field name was
ChartName then the brackets would also not be requried!

"ChartNumber='" & Me.PatientID & "'"

Hope this helps!