Validating a field to match a corresponding field in another table

[AccessAllowed]

Kind of an Access newbie here, so bear with me, but I have two tables: a master list of data and another that shares many of the same fields but the data is updated/replaced every so often. Let's say they both share a "Member ID" field. Can I make a validation rule on this field so that you can only enter data that matches what's in the Member ID field in the master table, and if so what would it look like?

Edit: The purpose of this would be to prevent errors. I'd like to get it so that you wouldn't be able to enter a MemberID in the second table unless it already appears in that field in the master table.

 

[AUGuy]

Hi, Thanks for posting your question here. Glad you found us!
I've had a similar implementation of this in one of the databases I manage, hopeefully this might point you in the right direction as well.

This function iterates through each line of the table looking at, in this case, to see whether the passed string (AcctNum) matches what it's comparing to in the table.

When I used this in a form, it worked on the BeforeUpdate event, passing the value of the user-input in the textbox into the function. You could test this easily with a command button to adjust it to your needs.

If you're not sure how to tailor this to your situation, reply with the specifics on your table/field names and i'll adjust it for you accordingly.

Hope this helps.

Public Function VerifyAcct(AcctNum As String)
Dim strSQL As String
Dim rsAcctNumber As Recordset
 
Set dbDatabase = CurrentDb
strSQL = "SELECT * FROM CAM_Portfolio_Query WHERE [Account-Number] = " & Chr(34) & AcctNum & Chr(34)
 
Set rsAcctNumber = dbDatabase.OpenRecordset(strSQL, dbOpenDynaset)
 
If rsAcctNumber.EOF Then
    MsgBox "The account number you entered: " & AcctNum & ", is not a current CAM loan." & Chr(10) & "Please verify your entry before continuing.", vbOKOnly, "Account Number"
 
End If
End Function

There's probably several ways to solve this problem and this is only one of them, if anyone else feels there's a better way feel free to jump in. This probably gets clunky if you're dealing with mass amounts of data but for my needs it worked well.

Edit: I just realized that this was not in the VBA subforum! This might be more advanced than you're comfortable with as far as using VBA and calling functions, if so let me know and I'll come up with an alternative.

 

[AccessAllowed]

Thanks for your help! I do think, though, that might be a little too advanced for me at the moment. What I was thinking of doing was creating a relationship between the two tables and have the corresponding fields be the primary/foreign key and then enforcing referential integrity. Do you think that would produce the desired effect?

 

[AUGuy]

Yep Mine was used in a complex form that I had. What you've described should work with PK/FK's. Sorry for the confusion.

 

[bijuvijayaraj]

hai, try this if it can help you.
Create a look up (combo box) for the identical field of second table (u can find it in the properties of the field in table design view) by using the first tables identical field as input. Set the limit to list property to yes to validate data entry.

 

[AccessAllowed]

Thanks to both of you, AUGuy and bijuvijayaraj!