DLookup searching

[avagodro]

I have a database that has some tables joined via relationships.
The main table is a customer table, for gathering the first name, last name, address, etc. Then there are others are linked to it.
I have worked up a DLookup command

On Error GoTo ErrorHandler
Dim varFound As Variant
varFound = DLookup("LastName", "tblCustomers", "LastName = '" & Me.txtLastName & "' AND FirstName = '" & Me.txtFirstName & "'")

If Not IsNull(varFound) Then
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "There is a Duplicate Record found. Are you sure you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Duplicate Record Found" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
Screen.PreviousControl.SetFocus
DoCmd.SetWarnings (False)
DoCmd.GoToControl "txtFirstName"
DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
DoCmd.GoToRecord , , acNewRec


End If


End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 3021 Then ' no current record
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

Does anyone know of a good way that if, for example, the search found multiple records that it would display the multiple records in a popup form. You could then select the record that you want in the popup and it would retrieve the record? It would be similar to a search function, I believe.

 

[Pat Hartman]

Your code is in the wrong event. You need to move it to the FORM's BeforeUpdate event. That way, if the user says no to the save, you just cancel the update with:
Cancel = True
Get rid of all the DoCmd code and the set focus.

If you want to display the "duplicate" records, you can't use the DLookup() method. I would use a DCount() to determine if there are any records found. If records are found, I would open another form to show the duplicates.