Looping through a tbl to find a certain value

[cwf]

I rum XP and Access 2007

Private Sub Command0_Click()

'this code should open the Contacts tbl and search the LastName field till it finds a match from the if statement there are no controls on the form that use the contacts table or any of its fields.

Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
RS.ActiveConnection = CurrentProject.Connection
RS.Source = "tblContacts"
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic

RS.Open

'Do While Not RS.EOF

If tblContacts.LastName = "brown" Then THIS REFERENCE DOESN"T WORK

MsgBox "The Last Name is " & tblContacts.LastName


Else
Msgbox "Your search found no match!"
End If

RS.MoveNext 'This does not move the recordset? Why?
'Loop

End Sub

[pbaldy]

The direct answer to your question is that it needs to be this:

If RS!LastName = "brown" Then

That said, that's a horribly inefficient way to find out if there's a matching record. I'd open the recordset on an SQL string that included a criteria (in your case, "SELECT * FROM tblContacts WHERE LastName = 'Brown'"). Then test for EOF; if True, there were no records.