Duplicate entry not working

[anujkmehrotra]

Hi All,

i have Duplicate problem in this way:

Two fields (ChasisNo & Service) Datatype are Text and ChasisNo is not and UniqueID because so many services will there for the same ChasisNo.

Now, what i need is that tblSerJobCard has ChasisNo (abc1234) and Service (First;Second;Third).

I want user to choose only one Service either First or Second, for each ChasisNo.


eg:

JobID = 1

ChasisNo = abc1234

Service = First

-------------------------------------------


JobID =2

ChasisNo = abc1234

Service = Second

Code:**********

Private Sub Service_AfterUpdate()
Dim stLinkCriteria As String

If Me.NewRecord Then
stLinkCriteria = "Service = """ & Me.Service & """"
Else
stLinkCriteria = "Service = """ & Me.Service & """ And ChasisNo <> """ & Me.ChasisNo & """"
End If

If DCount("*", "tblSerJobCard", stLinkCriteria) > 0 Then
MsgBox ("You have already done this service to the customer."), vbExclamation, "Error"
Cancel = True
Me.Service = ""
Me.Service.SetFocus
End If
End Sub

****************

Please guide me what changes are required??????

using Access2003

Thanks & Regards,
Anuj

 

[anujkmehrotra]

I got it:

Private Sub Service_AfterUpdate()
Dim stLinkCriteria As String
stLinkCriteria = "Service = """ & Me.Service & """ And ChasisNo = """ & Me.ChasisNo & """"

If DCount("*", "tblSerJobCard", stLinkCriteria) > 0 Then
MsgBox ("You have already done this service to the customer."), vbExclamation, "Error"
Cancel = True
Me.Service = ""
Me.Service.SetFocus
End If
End Sub

Thanks & Regards,
Anuj

 

[PHV]

A more readable way:

stLinkCriteria = "Service='" & Me!Service & "' And ChasisNo='" & Me!ChasisNo & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886