Form question

[Learning Access]

I am making a form that I would like to allow a look up field to allow me to
look up an employee's social security number and then populate all tabs in
the form with information from the tables on that employee. I know how to do
a drop down to pick an employee but is there a way to type a number in and
have it pull from the tables and make the employee name populate at the top
of the form and then all other information from the tables to populate into
the form's tabs....can someone help? Thanks!

 

[John W. Vinson]

I am making a form that I would like to allow a look up field to allow me to
look up an employee's social security number and then populate all tabs in
the form with information from the tables on that employee. I know how to do
a drop down to pick an employee but is there a way to type a number in and
have it pull from the tables and make the employee name populate at the top
of the form and then all other information from the tables to populate into
the form's tabs....can someone help? Thanks!

One way to think about this is to view a Form as a movable window. The Form
does NOT contain any information; it's like a lens that you can focus onto the
table or tables to see the data that's there.

What is the Recordsource of this form? Are you trying to just view the data
that already exists, or are you trying to copy data from the employee table
into another table (if so, DON'T!)?

To directly answer the question, it's almost always better to allow the user
to *SELECT* a value from a combo box listing all the valid values, rather than
forcing them to type one of 999,999,999 possible SSN values to see if it might
match. You can sort the combo by SSN, and use Access' autocomplete feature to
jump to the right number after a few keystrokes. If you don't want to use
this, you can use the AfterUpdate event of a textbox to jump to the employee's
data:

Private Sub txtFindSSN_AfterUpdate()
Me.Filter = "[SSN] = '" & Me!txtFindSSN & "'"
Me.FilterOn = True
End Sub

This will fail if the user types a nonexistant SSN of course.

 

[Learning Access]

Thank you - I will try this. The recordsource for the soc sec number is in a
table along with other employee information that I want to see in the form.
I am simply trying to take the information that exists in the table and see
it in form view. We have over 900 employees so it would be easier to type
the soc sec number in and then have the information populate in the form for
easy look up. I have the form set up in tabs of information that also should
populate from the data in the tables for each also. Once I set up this
function to select by soc sec number, how do I make the table information
link into the form, that doesn't seem to be working?
--
Beth McLaren

I am making a form that I would like to allow a look up field to allow me to
look up an employee's social security number and then populate all tabs in
the form with information from the tables on that employee. I know how to do
a drop down to pick an employee but is there a way to type a number in and
have it pull from the tables and make the employee name populate at the top
of the form and then all other information from the tables to populate into
the form's tabs....can someone help? Thanks!

One way to think about this is to view a Form as a movable window. The Form
does NOT contain any information; it's like a lens that you can focus onto the
table or tables to see the data that's there.

What is the Recordsource of this form? Are you trying to just view the data
that already exists, or are you trying to copy data from the employee table
into another table (if so, DON'T!)?

To directly answer the question, it's almost always better to allow the user
to *SELECT* a value from a combo box listing all the valid values, rather than
forcing them to type one of 999,999,999 possible SSN values to see if it might
match. You can sort the combo by SSN, and use Access' autocomplete feature to
jump to the right number after a few keystrokes. If you don't want to use
this, you can use the AfterUpdate event of a textbox to jump to the employee's
data:

Private Sub txtFindSSN_AfterUpdate()
Me.Filter = "[SSN] = '" & Me!txtFindSSN & "'"
Me.FilterOn = True
End Sub

This will fail if the user types a nonexistant SSN of course.

 

[John W. Vinson]

Thank you - I will try this. The recordsource for the soc sec number is in a
table along with other employee information that I want to see in the form.
I am simply trying to take the information that exists in the table and see
it in form view. We have over 900 employees so it would be easier to type
the soc sec number in and then have the information populate in the form for
easy look up. I have the form set up in tabs of information that also should
populate from the data in the tables for each also. Once I set up this
function to select by soc sec number, how do I make the table information
link into the form, that doesn't seem to be working?

Since I have no idea what you're doing, all I can say is "I don't know". If
the form is showing no data but allowing new data to be entered, maybe its
Data Entry property is set to Yes - if so try setting it to No.

A "soc security number" does not have a Recordsource. A Form has a
Recordsource; a control (textbox, combo box, etc.) on a form has a Control
Source.

What I would do (given appropriate tables, which I don't know if you have
either) is use a Combo Box (not a textbox) on the form; you can create an
unbound combo using the combo box toolbox wizard to create a combo "Use This
Combo to Find a Record". Again... forcing the user to accurately type nine
digits is an unnecessary imposition on the user, when they can use the same or
fewer keystrokes to SELECT a valid SSN from a combo box.

Perhaps you could post the SQL view of your form's Recordsource property, and
the code that you have put into the form.