How to auto-fillin related fields?

  • Thread starter Thread starter hkgary33 via AccessMonster.com
  • Start date Start date
H

hkgary33 via AccessMonster.com

Hi all,
I've built a table named STU_DATA which stored student name and it's ID, it's
design is as follows:

STU_NAME ID
David 1234
John 1235
.....


I've also built a form to let user choose STU_NAME by pull-down combo box,
and a text box for user to input the student ID.....what I would like to ask
is that how can I amend in the form control so that each time when the user
choose STU_NAME in the combo box (which will then display all STU_NAME fields
in the STU_DATA table to let user choose), then the ID text-box will
automatically search that student's name in STU_DATA table and fill in that
student's ID.......


THANKS!!!!!
 
hkgary33,
Your combo box can do that for you.
Bind your combobox to the StudentID field. Set up the combo query with StudentID in
the first column and StudentName in the second,
Set NoOfColumns to 2
ColumnWidths to 0" ; 1.25" (adjust 1.25 to suit your name width)
Set ListWidth to 1.25"

What this does is shows the user a list of students names (IDs are hidden by 0" width)
User selects a name, and that name is displayed in the combo.
BUT... what's really stored in the table is the StudentID.

There is no need to "capture" the StudentName, were just capturing the StudentID, and
"displaying" the associated StudentName.
In any subsequent form, query, or report, the StudentName can be rederived from the
StudentID value, so no need to capture it.
 
Your combobox's rowsource should be something like:
SELECT STU_NAME, ID FROM STU_DATA Order By STU_NAME

In the combobox, set the Column Count property to 2. In the Column Widths
property, put:
1";0"
This will display the name and hide the ID. In the comnbobox's AfterUpdate
event put this code:

Me.txtID = Me.cboSTU_NAME.Column(1)

This will pass the value from the second column, the Id, to the textbox.

Barry
 
Back
Top