Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    Unanswered: Validation Rule to Confirm Duplicates?

    I have a form with Invoice Numbers. Since we use many vendors, occassionally an invoice number may be duplicated, so I did not make it a primary key or a unique value field. If an invoice number get entered that is a duplicate, I need a window to pop up basically stating "are your sure?" I think this will probably an "If" statement in the "After Update" for my field. Can anyone point me in the right direction to learn how to do this?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    It could be something like:

    Id DCount("*", "Tbl_Invoices", "Invoice_Number=" & InvNumber) Then
        MsgBox "Are you sure...
    Have a nice day!

  3. #3
    Join Date
    Dec 2009
    I figured it out. Ended up using:

    Dim strWhere As String
    Dim varResult As Variant

    With Me.InvoiceNumber
    If IsNull(.Value) Or (.Value = .OldValue) Then
    'do nothing
    strWhere = "InvoiceNumber = """ & .Value & """"
    varResult = DLookup("LogID", "PurchasingLog", strWhere)
    If Not IsNull(varResult) Then
    MsgBox "This Invoice Number is already used in Log ID " & varResult
    End If
    End If
    End With

    Put it in the AfterUpdate.

Posting Permissions

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