Thanks to the help in this question:
I have code in the Before Update section of a text box and when a duplicate value is found in the table I have it open a duplicate query so that the user can see what records are related to the value in question. The only problem I face is that because this code is in the Before Update section, it finds the duplicate and opens the query before the record is saved. Therefor.. the duplicate query shows nothing if this is the first occurrence. Whenever I try to add the save command into the Before Update section, I get a runtime 2115 error.
Is it at all possible to save a record in the Before Update section so that my query isn't empty on first-time duplicates? Or any other suggestions of showing duplicate values?
Thanks in advance
Private Sub Lot_No_BeforeUpdate(Cancel As Integer)
Dim varExists As Variant
Dim intResponse As Integer
varExists = DLookup("[Lot No]", "[Complaints]", "[Lot No] = " & Chr(34) & Me.[Lot No] & Chr(34))
If Not IsNull(varExists) Then
strMsg = "The lot number you have entered already exists in the table. Would you like to view a list of duplicate entries to investigate previous relatives?"
intResponse = MsgBox(strMsg, vbYesNo, "Duplicate Lot Number")
If intResponse = vbYes Then
DoCmd.OpenForm "Duplicate Lot Numbers"