Comparing data in two different tables

[Guest]

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?

 

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

 

[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

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

 

[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!)

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

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