Check for Duplicate Entry

ccflyer

Registered User.
Local time
Today, 17:51
Joined
Aug 12, 2005
Messages
90
Hey Guys,

I'm working on an invoice database and have run into a little snag due to my lack of VB knowledge. I was wondering if you guys might be able to help out.

I've got a invoice entry form which has two text boxes [name] and [invoice number]. The user enters the name first, then the invoice number second. What I want to happen is when the user moves out of the [invoice number] field, if there is another invoice with the same name and invoice number in the table, I want the background of the [invoice number] text box to turn red. This allows the user to visually see if he/she has entered this combination before.

Here's what I was thinking of...
1. User leaves [Invoice Number] text box
2. A Make Table Query is run (check_duplicates) which returns records with the same [name] and [Invoice Number] as the form currently has.
3. Somehow (VB) check to see if there are any records in the table.
4. Turn [Invoice Number] text box red or green on form.

Any help with how I might code this would be greatly appreciated :)

Thanks,
Chris C.
 
Ok... here's what I've got so far. Right now, when this is run, I get the following error:

Code:
Run-time error '2001':
You cancelled the previous operation

Here is my code so far:

Code:
Private Sub InvoiceNumber_AfterUpdate()

    If DLookup("Invoice_Number", "LOE/Facility Construction - Updated", "Invoice_Number = " & Forms![LOE/Facility Construction Form]!InvoiceNumber) Then
        Me.InvoiceNumber.BackColor = 4342527
    Else
        Me.InvoiceNumber.BackColor = 32768
    End If
    
End Sub

Any Suggestions?
-Chris C.
 
Yeah, you are using the Dlookup wrong. DLookup will return a value or string and you currently don't have it doing anything. Basically, you are saying with your current statement

If InvoiceNumber Then
...etc.

If InvoiceNumber is what?
 
Oh, ok... I see. I fixed the usage, now it works!!

Thanks!
-Chris
 
Thanks for the pick up Bob. I was outside running the chipper for a while.
 
Just a supplement to what Bob wrote,

The DLookup() will return a Value or a Null.

You just need to test if it found anything with IsNull():
Code:
If [b][color=blue]IsNull([/color][/b]DLookup("Invoice_Number", _
     "LOE/Facility Construction - Updated", _
     "Invoice_Number = " & Forms![LOE/Facility Construction Form]!InvoiceNumber)[b][color=blue])[/color][/b] Then
 ...
End If


But I would prefer evaluating it as a string:
Code:
If DLookup("Invoice_Number", _
     "LOE/Facility Construction - Updated", _
     "Invoice_Number = " & Forms![LOE/Facility Construction Form]!InvoiceNumber) [b][color=blue]& "" = ""[/color][/b] Then
 ...
End If
 

Users who are viewing this thread

Back
Top Bottom