Need help with RowSource in Lookup

[Ron A.]

When selecting a modelID for a related table I have all the fields show from
the lookup of the Models table because I have many different years and makes
related to a model type, but when selecting the correct model only the year
shows in the field of the related table. Is there a way I can select what
columns show?

 

['69 Camaro]

Hi, Ron.

Don't use a lookup field. For an explanation of why, please see the tip,
"The Evils of Lookup Fields in Tables," on the following Web page:

http://www.mvps.org/access/lookupfields.htm

Instead, use a combo box on a form (where all data entry should be made
anyway). The combo box can use a multi-columned query as its Row Source
Property with the Column Count and Column Widths Properties set to only show
the necessary columns. The first visible column will be the column that is
displayed in the combo box. For example, if the query has four columns with
the last three of them visible (the first column is the primary key, so use
0" width to make it invisible), the second column will show in the combo box
after the user makes the selection:

Column Count: 4
Column Widths: 0";0.5";1";1"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Pat Hartman\(MVP\)]

Create a query that joins the main table to the lookup table. Use that as
the RecordSource for the form. You will be able to select whatever fields
you want from the lookup table and bind them to controls on your form. No
code is required. As soon as the ModelID is chosen from the combo, the
related fields will auto fill. To avoid accidentally changing this
"related" data on this form, it is best to set the Locked property to Yes
for those controls.