I am an enthusiast in Access trying to do some god for the local chapter of the Salvation Army. I have been asked to create a database that when individuals information is entered, it checks to see if they have already registered either as a primary, a spouse, as another adult living in the household, or as a child. Every individual is requried to provide proof of residence and a proof of a valid SSN. I have choosen the SSN to check for duplicates in the related tables. I have the Primary and the spouses SSNs listed ont eh same table so checking for duplicates there is easy. What I am having problems with is checking the table with the other adults lliving in the household a duplicate SSN with the primary member. I have been trying to use DLOOKUP for two days now and can't seem to get it right. The Form is called "FormInformation" and contains subformscalled "Children subform" and "OtherAdults subform" each drawing from a table with the same name. The Primary Key in the Table "FormInformation" is [PrimarySSN] and the other tables are related to it through that key.
I got this little snipet of code form a website but I couldnot get it to work for my case. Any help would be greatly appreciated.
Dim x As Variant x = DLookup("[PrimarySSN]", "FormInformation", "[PRIMARYSSN] = '" & "Table![OtherAdults]![SSN] = '") On Error GoTo CustID_Err If Not IsNull(x) Then Beep MsgBox "That value already exists", vbOKOnly, "Duplicate Value" Cancel = True End If CustID_Exit: Exit Sub CustID_Err: MsgBox Error$ Resume CustID_Exit