Recordset: How to find Dups

[Steven547]

I've never really used Recordsets before, so I'm new to this. I understand the concept, it's just the "Extracting" data I'm lost on.
I have a cbo box with account numbers based off Table (A), that populates the form. After the user makes changes, they save the record and it saves to a different table Table (B).
What I want to do, is if the User selects a number from Table A, I want to see if it exists in Table B already. If it does, then prompt a message that is exists. This will prevent duplicate records being entered. I am lost on how to do this. Any advice? Suggestions? Thanks.

 

[PHV]

Have a look at the DLookUp function.

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

 

[Steven547]

I have a DLOOKUP on the initial grab of the data. But how would I compare that to another table in the code? Would I have it be like this?

if (DLookup("[Account Number]", "[TABLE A]", " [Account Number]=cboLookupAccount") = (DLookup("[Account Number]", "[TABLE B]", " [Account Number]=cboLookupAccount")

Or ?

 

[2Grandpa]

Change the Index property of the Record Number in table B to "Yes (No Duplicates)". This will prevent duplicate records from being added.

 

[jamaarneen]

Hi Steven547,

Maybe you should try to place the following code to the 'BeforUpdate' event of your combobox, so you can cancel the event if this number already exists.

If DCount("[Account Number]", "[TABLE A]", "[Account Number] = " & cboLookupAccount) > 0 Then
     [i]your MsgBox[/i]
     Cancel = True
End If

Note: the above ok if "Account Number" is of number type. If it is a string, the code might need little changes:

If DCount("[Account Number]", "[TABLE A]", "[Account Number] = [b]"'"[/b] & cboLookupAccount [b]& "'"[/b]) > 0 Then

Helped? hope so
Ja

 

[TheAceMan1]

How are ya Steven547 . . .

[blue] . . . But how would I compare that to another table in the code? . . .[/blue]

[purple]Use the alternate table in your DLookUp![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886