Can't find records in Access

[carmined58]

I'm new at VBA, REAL new. I'm using Word and Access 2003. I'm attempting to compare a string obtained from a textbox in a Word user form against data in an Access table. I've been trying to accomplish this using the "find" method. However, even though I know for certain the string is indeed in the field in which I'm looking - I copy it right from the table to my textbox - I still end up at EOF without returning the correct result. Like I said, I'm new at this, so please go easy on me. But I sure would appreciate some help. This is what I'm using:

Private Sub CommandButton1_Click()
strClientName = txtClientName.Value
strSecondName = txtSecondName.Value
strClientNmbr = txtClientNmbr.Value
Dim myRecordset As Recordset
Set myRecordset = CreateObject("ADODB.Recordset")
With myRecordset
.Source = "Clients"
.ActiveConnection = "Ketel"
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open ("SELECT cltnum, cltname From Clients ORDER BY cltname;")
.MoveFirst
End With

strClientName = txtClientName.Value
With myRecordset
.MoveFirst
.Find Criteria:="Cltname = 'strClientName'"

If Not .EOF Then
MsgBox "This record is " & .Fields("Cltname")
Else
.MovePrevious
MsgBox .Fields("Cltname")
End If
End With
End Sub

Thank you!

 

[SkipVought]

Hi,

Why not...

("SELECT cltnum From Clients Where cltname ='" & strClientName & "';")

Skip,
[sub]
[red][/red]
[/sub]