Assuming that your form is bound to "customers" then you could use the
form.recordsetclone rather than your recordset. Something like this
(untested!)
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
With rst
.FindFirst "address= '" & Me.address & "'"
If Not .NoMatch Then
if msgbox ..... then
Me.Bookmark = .Bookmark
End if
End If
.Close
End With
'End of code
Note that this code will still create a new record even if it moves to the
existing address record. You may want to include a Me.Undo to prevent this
happening. You may also be better off using the BeforeUpdate event rather
than the LostFocus event as then you can cancel the event before moving to
an existing record.
In your code you have used Address.Text. I don't think this will work in the
LostFocus event as the text property is only available when the control is
being edited. It is usually used in the Change event - this would be another
option for your code (when you MUST use the .text property), and would mean
that the address is checked every time the user types a new character into
the address field. If you choose to use the change event it may be better to
make rst a module level variable and use the control's enter event to set
rst, and another event to close it. That way you're not creating and
destroying the rst object every time a character is typed in.
Andrew
"Joe Bond" <j0******@hotmail.com> wrote in message
news:cb*************************@posting.google.co m...
Hi. I have a simple MS Access 2000 form in which I enter some customer
data. When the address field is entered I need to see if a duplicate
record exists. I need to know this *right away* before the remaining
fields are filled out, so I'm trying to call a function on the
Address_LostFocus() event which will do the lookup. If a match is
found, it should prompt the user to either continue, or navigate to
the existing record. This is where I'm stuck (the navigation). I've
tried a bunch of things, but do not have the Access experience to
effectively solve this problem. Please help!
This is what I have so far:
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
strSQL = "SELECT * FROM customers WHERE address ='" & Address.Text &
"'"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
If MsgBox("A record exists with a matching address. Click OK
to edit the existing record.", vbOKCancel) = 1 Then
'navigate to existing record
End If
Else
MsgBox ("Keep Going...")
End If
