Findfirst for text string

[ypma]

A little bit of assistance is sort on “FindFirst “, I am getting my knickers in a twist as how to show that the “clientnumber “in Sub650 is in fact a text field . Sub 146 is a working correctly when in this particular case the Client ID is a number . Note the record source for Sub 650 is a query where as Sub146 record source is from linked table. Any pointers would be appreciated I tried adding Chr$(34) but to no avail.

#Private Sub Command146_Click()
If IsNothing(Me.SearchKey) Then
MsgBox " please enter a client id in searchKey Box"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("personal Detaill new", dbOpenDynaset)
With rec
.FindFirst "[Client ID] = " & Me!SearchKey

If Not .NoMatch Then ' we found it
DoCmd.OpenForm "Personal Details Form", wherecondition:="[Client ID] = " & Me!SearchKey
DoCmd.GoToControl "[client id]"
End If
If Not .NoMatch = False Then
MsgBox "No Match"
End If
End With
End Sub#

#Private Sub Command650_Click()
If IsNothing(Me.searchKey) Then
MsgBox " pse enter a client id in searchKey Box"
Exit Sub
End If
Dim db As Database
Set db = CurrentDb
Dim rec As DAO.Recordset
Set rec = db.OpenRecordset("ClientInfoTbl Query", dbOpenDynaset)
With rec
.FindFirst "Clientnumber" = Me.searchKey
If Not .NoMatch Then ' we found it
DoCmd.OpenForm "FRMCLIENTDETAILS", wherecondition:="Clientnumber" = Me.searchKey
DoCmd.GoToControl "[clientnumber]"
End If
If Not .NoMatch = False Then
MsgBox "No Match"
End If
End With
End Sub#

Would appreciate any guidance
Regards Ypma

 

[pbaldy]

You've got the = outside the quotes. If text it would be:

.FindFirst "Clientnumber = '" & Me.searchKey & "'"

or if the value could contain apostrophes:

.FindFirst "Clientnumber = " & Chr(34) & Me.searchKey & Chr(34)

 

[ypma]

Pbaldy: Thank you very much for your quick response, so far so good, the search now opens up, but to a blank record. Do I aso have to change the #wherecondition:="Clientnumber" = Me.searchKey #
to # "Clientnumber = '" & Me.searchKey & "'"#

Regards Ypma

 

[pbaldy]

Yes, it would look the same.

 

[ypma]

Pbaldy: That did the trick , my knickers are now untwisted.

Regards Bob

 

[pbaldy]

Excellent, nobody likes twisted knickers.