how do i check for records that already exist in another table

G

Guest

im entering information for job applicants on a form. (linked to my
applicants table) when i enter the NI number (unique to all applicants) i
want to make sure this number doesnt match any NI Numbers I have stored in
another table for people i dont want to be applying. (excludes table)

ie: so if i enter a NI number i want it to flag up to me if this is the NI
number of an applicant i know shouldnt be applying.

please help!!!
 
T

tina

you can check the NI value you enter in the form against the excludes table
by adding the following code to the NI control's BeforeUpdate event, as

If DCount(1, "NameOfExcludesTable", "NI = " & Me!NIControlName)>0 Then
Cancel = True
Msgbox "This NI is in the excludes table."
End If

(watch for word wrap - "If" through "Then" above should be on one line.)
substitute the correct table, field and control names, of course. also, the
above code assumes that the NI field in the table is a number data type, not
text. suggest you read up on the DCount() function in Access Help, so you'll
understand how it works.

hth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

insert into - want only records that don't already exist 2
Check if code already exist in a different table 1
use lookup to check for existing entries 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Increment a number based on instances in another table 1
Update one table with another using criteria 1
How do I specify a formula to look in another cell for a sheet name? 1
Count ID from one table that matches another and show in a form... 2

Top