continous form to find record


S

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.
 
Ad

Advertisements

G

Guest

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

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.
 
G

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?
 
S

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.
 
G

Guest

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

Advertisements

S

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.
 
Ad

Advertisements

G

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
:)
 

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

Top