How to get fieldvalue from another table in a form

[Guest]

Hi All,
This must be simple but... Can't get it to work.

My problem: I have a form with data. As a specific field in the form is filled with data the related data from the table must be filled in into a field (non-editable) on the form.

Example: I have a form where i register a student on a course. This form has two comboboxes, one to select the course and one to select the student. These comboboxes are created with the lookup wizard.

When i enter the name of the student into the combobox of this form i want to display his phone#, zipcode, city and his date of birth. This info is stored in de student table. I must make textboxes to hold this info but how do i fill them with the correct data...

I hope someone can help me to solve this. If so, please give all the steps such as, create a textfield.. properties etc.

Thanks in advance!!!

 

[Roger Carlson]

One way is to JOIN your Student table to the Course table in the
RecordSource of the form. Then use the Combo wizard to create a combobox to
select a student. Add the rest of the field onto the form. These should be
bound, but MAKE SURE you set the Locked Property of each textbox to Yes.
When you select a student, the other values will automatically pop in.

Another way is to have your combo box RowSource hold all of the values you
want. Make only the one you want to see in the List visible by setting the
column widths of the others to 0 (zero). Then in the AfterUpdate Event of
the combo, set each of the columns in the combo to a separate, unbound
textbox.

Here's an example:
Suppose you have 4, unbound textboxes named: txtPhone, txtCity, txtZip,
txtDOB

Suppose further that your combo is named cboStudent. The RowSource is a
query as follows:
Select StudentName, Phone, Zip, City, DOB from tblStudents

Set the following properties in the combo box:
Columns: 5
Bound Column: 1
Column Widths: 1,0,0,0,0

Then in the AfterUpdate event of the combo have this:

Me.txtPhone = Me.cboStudents.Columns(1)
Me.txtZip = Me.cboStudents.Columns(2)
Me.txtCity = Me.cboStudents.Columns(3)
Me.txtDOB = Me.cboStudents.Columns(4)

Note: Columns are numbered starting a zero, so Column(1) is really the
second column.


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi All,
This must be simple but... Can't get it to work.

My problem: I have a form with data. As a specific field in the form is

filled with data the related data from the table must be filled in into a
field (non-editable) on the form.

Example: I have a form where i register a student on a course. This form

has two comboboxes, one to select the course and one to select the student.
These comboboxes are created with the lookup wizard.

When i enter the name of the student into the combobox of this form i want

to display his phone#, zipcode, city and his date of birth. This info is
stored in de student table. I must make textboxes to hold this info but how
do i fill them with the correct data...

I hope someone can help me to solve this. If so, please give all the steps

such as, create a textfield.. properties etc.

 

[Guest]

Hello Roger,

Thak you! This works. I choose the last method with the query that fills the data into the formfields.

It wasn't that simple after all. That said it is likely that my skills on Access needs improvement.

Thank you very much!

Problem solved...