Duplicate Warning form to appear on screen

[Sheila.deJesus]

Help!

I have 2 tables with the following fields:

Table1
CustID - (Autonumber/PK)
FirstName
MiddleName
LastName
Address
City
ContactNumber

Table2
CustID
VehID - (Autonumber/PK)
PlateNumber
FrameNumber
ProdNumber.... etc.

Property of all fields in Table2 are set to Indexed(No Duplicates).

I need a warning message form to appear on the screen if User adds a new record of PlateNumber which already exist. Then, automatically shows User who currently owns that PlateNumber.

Thanks!


Sheila

 

[Sheila.deJesus]

I used this code, but it's not working. Someone please help!

On BeforeUpdate property of PlateNumber

Private Sub PlateNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim msg As String
Dim strCriteria As String
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

strCriteria = "[PlateNumber]='" & Me.PlateNumber.Text & "'"
With rst
.MoveFirst
.FindFirst strCriteria
If .NoMatch Then
Cancel = False
Else
Cancel = True
Me.PlateNumber.Undo
Msg = msg & DLookup("[LastName]", "[tblCustomerData]", "[CustID]=" & !CustID) & vbCrLf
Msg = msg & "Would you like to see the record?" & vbCrLf Msg = "Would you like to see the record?" & vbCrLf
If MsgBox(msg, vbQuestion + vbYesNo, "PLATE# TAKEN!") = vbYes Then
Me.Bookmark = rst.Bookmark
End If
End If
End With
Exit_PlateNumber_BeforeUpdate:
Set rst = Nothing
Exit Sub
ErrorHandler:
msg = "Error " & Err.Number & " in " & Me.Name & ": PlateNumber_BeforeUpdate" & vbCrLf
msg = msg & Err.Description
Msgbox msg, vbCritical, "AN ERROR OCCURRED"
Resume Exit_PlateNumber_BeforeUpdate
End Sub

 

[ejstefl]

Is it stopping on a certain part of the code? Does it do anything? Please provide more detail than simply the fact that it is not working.

 

[CEH]

Sheila,
Here's a very simple code for something similiar..... Checks for duplicate social security numbers......

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.SocialSecurity.Undo

Else:
End If
End Sub

This one does not open who has the duplicate....... But I suppose you could add your code to the "If Not IsNull" portion to open the duplicate record.

Maybe this will help.........

 

[Sheila.deJesus]

Hi ejstefl!

What i meant by not working is that it doesn't give me notification. I intentionally added record (Plate Number in particular) which has duplicate. The code should have notified me that there's a duplicate record, right? Nothing appears on the screen and i was able to fill in all the other empty fields on the form.

Sheila

 

[Sheila.deJesus]

Hi Curtis!

It works!!!! Thank you very much!!!!!!!!!


Sheila