Duplicate record warning (1 Viewer)

I have a database where I enter in invoice details.
I am trying to get a dialog box to pop up on my form advising me if I have keyed the same invoice previously once i tab out of the invoice number field.

Because there are multiple vendors who may have the same invoice number i want to concatenate the [VendorCode] & [InvoiceNo] fields.

You can make the vendor code and invoice number primary key

Disagree with Khawar. You should make the two fields a compound index set to no duplicates. Don't mess about with your PK.

you need a dlookup - if you are used to doing dlookups with a single criterion, then you need to tweak it to use two criterions - so it looks like, effectively.

(where) [vendorcode] = vendorcode and [invoiceno] = invoiceno

note that strings and numbers need to be masked appropriately

Heres a working example of what Dave suggested I used it i to pass the value to another form if no duplicate found but you could use it in the afterupdate of your form control:

Dim stDocName As String
stDocName = "frmTest"

'Checks the table for duplicate entries prior to passing OpenArgs array if true goes to In List Sub.

If DCount("*", "tblTest", "[FormID] = " & Me.FormID) = 0 Then
Else
GoTo In_List_Click

End If

'Fire your form here as no duplicate found.....

DoCmd.OpenForm stDocName, , , , acAdd, acDialog, [FormID]

In_List_Click:

MsgBox "This PPE Item already has a Risk Assessment" _
& vbCr & vbCr & "This command will end", vbInformation _
, "Duplicate information entered..."


good luck John

PS> To concancate your values you could but something like MyInvoiceID: [VendorCode] & "" & [InvoiceNo] in the field section of your query and use MyInvoiceID in your form.

No good! I can't get it to work. I keep getting a VBA error message, "you canceled the prvious operation"


I also wanted to amend the msgbox to say something like "Blocked invoice previously entered on [DateRegistered]. Re: [LogNo]"

Cheers,

Nick

Could you attach a copy of your database so we could have a look? Have you set the form you are opening to allocate the openargs from the acAdd, acDialog, [TxtVendorInvoice]?

Also the Table you are checking is the table your form being opened is passed on?

Probably just syntax so would be easier if we could see the db

John

that error message sounds like the openform action didnt work - there must be something wrong with this.

a) Put a breakpoint in the openevent of the popup form, and see what happens
b) remove the txtvendorinvoice, or acdialog - see if that is part of the problem

DoCmd.OpenForm stDocName, , , , acAdd, acDialog, [TxtVendorInvoice]

Click to expand...

i presume there is something wrong with this statement

put an onerror statement before this, to trap the error (but i think it will just be 2501 - form didnt open)