Invoice number/Vendor

[Mr2006]

Hi,

We have several vendors who submit invoices to us regularly with invoice number and sometime without invoice number. In the case we have invoice number, in the invoice number field (text), I want to be able to have codes to check and see if the invoice number and the providerID submitted an invoice before, a message comes up saying "invoice was submitted before. Check first. “If invoice was submitted before for different provider, then the invoice number is not rejected.

This field can have the following:
1) can be blank
2) Can have invoice number. But the invoice number can not be duplicate for the same provider.

 

[CaptainD]

What have you tried? (And post a your code)

If you have not tried anything then you could try a "SELECT COUNT(*) FROM tblInvoice WHERE providerID = ## AND InvoiceNumber = '#####'" and then test to see if the count is greater then 0

 

[JoeAtWork]

This is a textbook example of where to use the DLookup function.

 

[Mr2006]

CaptainD,

I have not tried anything yet. Should I use this statement after update event?

I am not sure if i was clear in the begining. We enter invoices from our vendors in our database. After selectiong the provider from combobox and entering the invoice number provided to us from the vendor, I want the event to check if we have recieved this invoice before, if yes from this vendor, messagebox comes up (invoice is arelady in the database. check the invoice number )

I am not very experieinced in this. Please explain in details.

 

[JoeAtWork]

Have you looked up DLookup in the Help files yet?

I would put it in the BeforeUpdate event of the invoice number checkbox.

 

[Mr2006]

Where is the help file?

 

[Remou]

Press F1 in various places, for example, the code window, the property sheet, nearly anywhere.

 

[CaptainD]

You can also Google "Access DLookup" and get several sites that explain it's use.

http://www.techonthenet.com/access/functions/domain/dlookup.php

http://www.mvps.org/access/general/gen0018.htm

 

[Mr2006]

Ok I see the use but I am not sure how will this help me situation. I want to be able to control the field from entering duplicate invoices for the same vendor. For example, sometime a vendor# 45 will submit invoice number 5 to us mistakenly twice. When we enter this invoice in our database, I want a message come up saying this is duplicate invoice. ) Somtime We have another vendor number 544 submit invoice#5, i want the database to except this invoice since it is thefirst time vendor nuymber 544 submit invoice#5.

Now, sometime the vendor will submit invoice but without number, this so the field will be left blank.

 

[PHV]

In the BeforeUpdate event procedure of the invoice number control:

If Trim(Me![invoice number control] & "") <> "" Then
  If Not IsNull(DLookUp("[invoice number field]", "[your table]", "providerID=" & [provider control] & " AND [invoice number field]='" & [invoice number control] & "'")) Then
    MsgBox "invoice was submitted before. Check first. "
    Cancel = True
  End If
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gwinn7]

Dlookup works, but in the long term, you should create your own alternative, or steal one from the Internet, like elookup. Dlookup really sucks for more involved operations.

Gary
gwinn7

 

[Mr2006]

PHV,

Thank you sooo much but it does not work all the time. Sometime it let dupliacte invoice numner for the same vendor to be saved and some time it does not. Below is what I put in before update event:

If Trim(Me![VendorInv#] & "") <> "" Then
If Not IsNull(DLookup("[VendorInv#]", "[Project_tbl]", "providerID=" & [ProviderID] & " AND [VendorInv#]='" & [VendorInv#] & "'")) Then
MsgBox "invoice was submitted before. Check first. "
Cancel = True
End If
End If
End Sub

Thanks