continous form to find record

[Song]

I have 2 tables:

tblException tblStudent
SSN SSN
Last
First
M

tblStudent has many records and some records are duplicate

I want to set up a continous form to enter data into tblException. When the
focus moves away from SSN field (BeforeUpdate event, I guess), I want Last,
First M of tblStudent to show next to it. If SSN entered cannot be found in
tblStudent, a warning message should popup and update is canceled. How to
code that? Thanks.

 

[Guest]

What is the relationship between the tables? Are there multiple records in
tblException for each Student or Multiple Students for each exception?

 

[Song]

tblException's SSN is Primary key without duplicate. This table is in my C
drive.
tblStudent is table in the network for which I linked. I cannot modify that
table.
I have not set up relationship between these two.

 

[Guest]

This is a key question I need to know before I can suggest a way to do what
you want:
Are there multiple records in tblException for each Student or Multiple
Students for each exception?

 

[Song]

There are multiple records in tblException. Each record is uniq (SSN)
I want to display Last, First, M of the first record found.
thanks.

 

[Guest]

Create a query to use as your recordset. It should join tblStudent to
tblException on SSN

 

[Song]

Thanks. However, the relationship I set up is 'intermediate' type and would
not allow me to data entry into tblException. I cannot modify tblStudent
structure as it resides in network.

Is there a way to find record in tblStudent without setup relationship?

I just want to enter SSN in tblException and some kind of code to find
record in tblStudent, get Last, First, M fields value and display on my
form?

thanks.

 

[Guest]

Yes, you could create a recordset and load the fields it into unbound controls.

 

[Song]

Great! Since I'm new, can you provide code for recordset and find ssn?
thanks.

 

[Guest]

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tblStudent")
rst.FindFirst "[SSN] = '" & Me.txtSSN & "'"
If rst.NoMatch Then
MsgBox "SSN " & Me.txtSSN & " Is not in tblStudent"
Else
Me.txtStudentSSN = rst![SSN]
Me.txtStudentLast = rst![Last]
Me.txtStudentFirst = rst![First]
Me.txtStudentM = rst![M]
End If
set rst = Nothing

Change the names to suit
Garnish with fresh fruit and mint leaves
Sprinkle with powdered sugar