Comparing data in two different tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lavatress - 14 Nov 2006 17:06 GMT

My objective is to compare two fields that are located in different tables
which are in same database. In a table called ,"TEST", I have a field called
"FILE ID". In another table called,"IDS" , I have a field called
"CasFileID". I want to compare these two fields to check if the users are
duplicating data. If they are duplicating data, I will create a msgbox to
inform them of their duplication. I would like to build the event in the
Before Update Procedure. How can I acheive this by using VBA?

Reply to this Message

kingston - 14 Nov 2006 17:47 GMT

You can use a calculation to see if there are any matching records:
If DCount("[CasFileID]","[IDS]","[CasFileID]=" & Me.FileID.Text) Then
MsgBox "Hey, you bonehead, no duplicates allowed!"
End If

This assumes that the parameter is a number. Otherwise, you'll have to
enclose the input in single quotes.

>My objective is to compare two fields that are located in different tables
>which are in same database. In a table called ,"TEST", I have a field called
[quoted text clipped - 3 lines]
>inform them of their duplication. I would like to build the event in the
>Before Update Procedure. How can I acheive this by using VBA?

Reply to this Message

kingston - 14 Nov 2006 17:51 GMT

That was supposed to be:

If DCount("[CasFileID]","[IDS]","[CasFileID]=" & Me.FileID.Text) > 0 Then
MsgBox "Hey, you bonehead, no duplicates allowed!"
End If

>You can use a calculation to see if there are any matching records:
>If DCount("[CasFileID]","[IDS]","[CasFileID]=" & Me.FileID.Text) Then
[quoted text clipped - 9 lines]
>>inform them of their duplication. I would like to build the event in the
>>Before Update Procedure. How can I acheive this by using VBA?

Reply to this Message

Douglas J. Steele - 14 Nov 2006 18:40 GMT

Actually, it'll work just as well without the > 0, since VBA treats any
non-zero value as True, and 0 as False.

However, it's generally considered better to include the check.

Some people prefer to use:

If Not IsNull(DLookup("[CasFileID]","[IDS]","[CasFileID]=" &
Me.FileID.Text)) Then
MsgBox "Hey, you bonehead, no duplicates allowed!"
End If

the logic being that DLookup will stop at the first match it finds, whereas
DCount needs to go through the entire table.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> That was supposed to be:
>
[quoted text clipped - 17 lines]
>>>inform them of their duplication. I would like to build the event in the
>>>Before Update Procedure. How can I acheive this by using VBA?

Reply to this Message