Check for Duplicate Entry (1 Viewer)

ccflyer

Registered User.
Local time
Today, 16:22
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.
 

RuralGuy

AWF VIP
Local time
Today, 16:22
Joined
Jul 2, 2005
Messages
13,826
No need for the makeTable query. Use a DLookup() or DCount() function in the AfterUpdate event of the [Invoice Number] textbox. DLookup Usage Samples
 

ccflyer

Registered User.
Local time
Today, 16:22
Joined
Aug 12, 2005
Messages
90
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.
 

boblarson

Smeghead
Local time
Today, 15:22
Joined
Jan 12, 2001
Messages
32,059
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?
 

ccflyer

Registered User.
Local time
Today, 16:22
Joined
Aug 12, 2005
Messages
90
Oh, ok... I see. I fixed the usage, now it works!!

Thanks!
-Chris
 

RuralGuy

AWF VIP
Local time
Today, 16:22
Joined
Jul 2, 2005
Messages
13,826
Thanks for the pick up Bob. I was outside running the chipper for a while.
 

boblarson

Smeghead
Local time
Today, 15:22
Joined
Jan 12, 2001
Messages
32,059
Thanks for the pick up Bob. I was outside running the chipper for a while.

It's good to step away from the computer every so often. Now if I can just get myself to do it too :D
 

modest

Registered User.
Local time
Today, 18:22
Joined
Jan 4, 2005
Messages
1,220
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

Top Bottom