check for duplicates on entry

[Guest_imported]

Hi,

I made a small database for my company and we found that many of the invoices that we enter are sometimes duplicates because they have been faxed through and an original is handed in later on.

Would somebody be able to show me how I can check that an entry when entered in a field on the form is not already in the system and if so prompt the user telling them so.

Also a NULL or "" duplicate is allowed because an invoice number my have been ripped off.

Any help would be greatly appreciated.
Thanks!

 

[RickSpr]

You can use VBA code in the AfterUpdate event of the control that contains the invoice number. You'd use the DLookup() function to see whether a matching invoice number already exists. Something like this:

    Private Sub txtInvoiceNo_AfterUpdate()
        If Nz(txtInvoiceNo) <> &quot;&quot; Then
            If Not IsNull(DLookup(&quot;InvoiceNo&quot;, &quot;Invoices&quot;, &quot;InvoiceNo = '&quot; & txtInvoiceNo & &quot;'&quot;)) Then
                MsgBox &quot;Warning - Invoice number already exists&quot;, vbInformation
            End If
        End If
    End Sub

This example is for a text box named txtInvoiceNo, a table or query named Invoices, and a field within the table named InvoiceNo. If the invoice number is numeric, remove the apostrophes (single quotes) from the last argument to DLookup. Rick Sprague

 

[DMHamm]

I installed this code and it works great - but how do you force the focus to stay on this field until the user enters a unique number?

 

[EllieFant]

My guess would be to use

FieldName.SetFocus

before the end sub statement.

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

elliefant@qwest.net

 

[willir]

finemores^tpt

There are lots of coding wayys to check this, but in addition to any code, I would setup the table design not to allow duplicate invoice numbers, and not to accept nulls.

For the missing invoices, I would temparily assign an internal one (internal meaning assigned by the data entry person -- MISSING-10-03-03-A type of thing).

An invoice is key to transactions, and consequently, I would really shy away from accepting a null or blank value.

Rick's DLookUp is a great function to use for this check.

Richard

 

[DMHamm]

Nah - doesn't do a thing. I first tried the whole thing in the BeforeUpdate event - the duplicate-check code worked but the SetFocus said I had to save first. Then I moved all the code to the AfterUpdate event, but it just does the duplicate-check and then moves on without setting the focus back to the original field. Hmmm...

 

[DMHamm]

Unfortunately, I am forced to accept both duplicates and nulls (it's a terrible design and a long story).

So how can I set the focus back to this field until they enter a number that is not already being used?