Comparing data in two different tables

L

Lavatress

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?
 
K

kingston via AccessMonster.com

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.
 
K

kingston via AccessMonster.com

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
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
Click to expand...
[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?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


kingston via AccessMonster.com said:
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
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
Click to expand...
[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?
Click to expand...
Click to expand...
Click to expand...
 
Top