Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Question Unanswered: Problem in Duplicate entry

    Hi All,

    I have problem in Duplicate entry. it's as below:

    Here both fields (ChasisNo & Service) type 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

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    As I can understand you, I suggest to put an index
    on the fields ChasisNo and Service, (in the table), and put
    UNIQUE = YES (properties for that index).
    Look at "DemoIndex3FieldsA2002.mdb" (attachment, zip).
    Look at "Index3Fields" (attachment, zip, word).
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    I got it this way:

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •