Relationships?

D

Damon

I am trying to figure out how to correlate to tables such
that if I enter an invoice number in Table B, and that
invoice number has already been entered in Table A, then a
message will appear that alerts me to the duplication.
 
J

John Vinson

I am trying to figure out how to correlate to tables such
that if I enter an invoice number in Table B, and that
invoice number has already been entered in Table A, then a
message will appear that alerts me to the duplication.
Click to expand...

Well, if you have a Form you're using to enter the data, you can use
the BeforeUpdate event of the InvoiceNumber control to do so:

Private Sub txtInvoiceNo_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[InvoiceNo]", "[Table B]", "[InvoiceNo] = " &
Me!txtInvoiceNo) Then
MsgBox "This invoice number exists in TableB", vbOKOnly
<do something appropriate>
End If
End Sub

A Relationship will not work - you could establish a one-to-one or
one-to-many relationship between the tables, but this would only
prevent adding a record which did NOT match, and not give any warning
if the records DID match.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top