Check for Duplicates (1 Viewer)

jeran042 · Sep 12, 2018

I have a data entry form that takes numerous pieces of information. Mainly up to 5 invoice numbers (in 5 separate bound text boxes). In the after update of each text boxes I have this piece of code running:

Code:

Private Sub Invoice_1_AfterUpdate()
    If Me.Invoice_1 <> "" Then
        If Duplicate_invoice(Me.Invoice_1) = 6 Then
            Me.Invoice_1 = ""
            Me.Assigned_To.SetFocus
            Me.Invoice_1.SetFocus
    
        End If
    End If
End Sub

Private Sub Invoice_2_AfterUpdate()
    If Me.Invoice_2 <> "" Then
        If Duplicate_invoice(Me.Invoice_2) = 6 Then
            Me.Invoice_2 = ""
            Me.Assigned_To.SetFocus
            Me.Invoice_2.SetFocus
        
        End If
    End If
End Sub

Private Sub Invoice_3_AfterUpdate()
    If Me.Invoice_3 <> "" Then
        If Duplicate_invoice(Me.Invoice_3) = 6 Then
            Me.Invoice_3 = ""
            Me.Assigned_To.SetFocus
            Me.Invoice_3.SetFocus
        End If
    End If
End Sub

Private Sub Invoice_4_AfterUpdate()
    If Me.Invoice_4 <> "" Then
        If Duplicate_invoice(Me.Invoice_4) = 6 Then
            Me.Invoice_4 = ""
            Me.Assigned_To.SetFocus
            Me.Invoice_4.SetFocus
        End If
    End If
End Sub

Private Sub Invoice_5_AfterUpdate()
    If Me.Invoice_5 <> "" Then
        If Duplicate_invoice(Me.Invoice_5) = 6 Then
            Me.Invoice_5 = ""
            Me.Assigned_To.SetFocus
            Me.Invoice_5.SetFocus
        End If
    End If
End Sub


Function Duplicate_invoice(invoice_f As String) As Byte
    Dim VName_Inv As String
    Dim VNum_Inv As String
    Dim i As Byte
    
    For i = 1 To 5
        VName_Inv = Nz(DLookup("Vendor_Name", "qryINV_LOOKUP", _
        "Invoice_" & i & "= '" & invoice_f & "'"), "")
        
        VNum_Inv = Nz(DLookup("Vendor_Number", "qryINV_LOOKUP", _
        "Invoice_" & i & " = '" & invoice_f & "'"), "")
        
        If VName_Inv <> "" Then Exit For
    Next

    If VName_Inv <> "" Then Duplicate_invoice = MsgBox("This invoice may already exist and relates to:" & Chr(10) & _
    "Vendor Name:" & Chr(9) & VName_Inv & Chr(10) & _
    "Vendor Number:" & Chr(9) & VNum_Inv & Chr(10) & "Do you want to delete and enter other value?", vbYesNo + vbInformation, "Duplicate Entry!")
    
    
    
End Function

This is currently working great, and has served a VERY valuable purpose. However, now we are approaching 55k records, this code seems to take a while to run. My question is, how can I tweek this or modify this to be more efficient, or run faster?

Please bare in mind that while I did create the database (my actual first ever Access database) that this code resides in, I am no longer in that department, so spending the time to redesign the tables schema to only take 1 invoice per record in no longer an option.

Any help or suggestions would be greatly appreciated!

Ranman256 · Sep 12, 2018

couldnt you just enter codes into a keyed table? Thus preventing duplicates.
No code needed.

plog · Sep 12, 2018

The big issue is your query isn't a normalized data set. You shouldn't be checking 5 different fields for your invoice, each invoice should be in its own row that simply has 1 invoice column that contains the value.

Why is your query like that? Why are you using a query at all--presumably the invoice is held in a table, why not look into that table? Please tell me the table isn't structured similiarly with 5 different invoice fields.

jeran042 · Sep 12, 2018

Ranman256 & plog,

Both of you raise good questions. There are instances where duplicates are acceptable, for example if we are paying an invoice with no invoice number, say like a telecommunications invoice where they use an account number. So ruling out duplicates all together is not an option.

Plog, you are certainly correct, the table and the query are not normalized. I was asked to (very quickly) take an existing excel spread sheet and make a database out of it. Not having the time to properly plan or format the start data was the downfall.

What I am trying to do is work with what I have.

Also: The, "qryINV_LOOKUP" is actually "tblInvoiceLog", I was toying with the idea of limiting the amount of time I was looking back. So looking for duplicates from greater than 1/1/2017, as opposed to 1/1/2014

plog · Sep 12, 2018

My advice then, is to set this up properly and use a database as its intended. Otherwise you're asking nurses to put band-aids on broken bones.

Check for Duplicates (1 Viewer)

jeran042

Registered User.

Ranman256

Well-known member

plog

Banishment Pending

jeran042

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread