Form Fill in Process (HELP)

[Hansford cornett]

I have a stand alone main form with an ssn input field on it called NewSSN.

I have addresses in a PRIMARY SSN table and a SECONDARY SSN table.

I want to input the NewSSN into the main form and search the Primary SSN
table for the address. If the Primary SSN is not located then I want it to
search the NewSSN through the Secondary SSN Table. In either case I want it
to return the complete address record for the NewSSN when found.

All fields in both tables are the same accept the SSN in the Primary Table
is PSSN and the one in the Secondary Table is IRNS.......

I would like for the results to fill in the bottom portion of the form or a
separate form. Either way would work.

HELP PLEASE and thanks for this great group of individuals in advance....

Dwight

 

[Barry Gilbert]

It sounds like DLookup might be your best bet. Something like this in
the AfterUpdate of the NewSSN textbox:
' You'll need to change the control, table and field names to those
in your db.
Dim strAddress As String
strAddress = Nz(DLookup("Address","PrimarySSN","[PSSN] = '" &
Me.txtNewSSN & "'"),"")
If strAddress = "" Then
strAddress = Nz(DLookup("Address","SecondarySSN","[IRNS] = '" &
Me.txtNewSSN & "'"),"")
Endif
If strAddress<>"" Then
' Assuming you want the whole address in one textbox...
Me.txtAddress = strAddress
Endif

HTH,
Barry