Displaying Field in unrelated table in a form

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

I hope someone can help with this one.

I have a data entry form which is made up mostly of combo boxes which
receive there data from a number of other tables. My question is - is there
some way I can display a field from a unrelated table based on the selection
made in oone of the combo boxes.

I.E. What I would like is when a user selects his name from a drop down list
(Derived from (tblEmployees]) then that users Account number (which is in an
related table [tblAccountDetails]) is displayed in a text box beside the
combo bax
 
One way to do it is to include tblAccountDetails in the combo box row source
and include the Account Number as a hidden column. In code in the
AfterUpdate event of the combo box, you can set a text box to the value of
the hidden column in the selected row using the Column property. For
example, if Account Number is in the third column of the Row Source, your
code would look something like:

Me.txtAcctNo = Me.cmbEmployee.Column(2)

No, that's not a typo. Columns are numbered starting at 0.

If it's not possible to include tblAccountDetails in the Row Source, you
could also use a DLookup to fetch the value from the table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top