Lookup function on Form

[Devon]

I have a form 'Main Form' that is linked to a
table, 'Main Table'. The Main Table is linked to a
separate table 'Fund Table' through the lookup function.
Specifically, I have one of the fields as Fund #, which
uses the lookup feature to find the information through
the Fund Table.

The Fund Table contains several fields, which includes
Fund #, Fund Name, and Fund Style. From the Main Form I
would like to be able to use the lookup feature to find
the fund number, and once that has been entered, I have
two unbound fields on the Main Form, 'Fund Name'
and 'Fund Style' that I would like to use code to
populate these fields.

I realize that I can get this data by doing a multi-table
query, but my audience would like this data populated on
the Main form so that they can be sure they entered the
correct fund #.

Does anyone out there know how this is accomplished?

Thanks

 

[John Vinson]

I have a form 'Main Form' that is linked to a
table, 'Main Table'. The Main Table is linked to a
separate table 'Fund Table' through the lookup function.
Specifically, I have one of the fields as Fund #, which
uses the lookup feature to find the information through
the Fund Table.

Lookup fields are fraught with problems. See
http://www.mvps.org/access/lookupfields.htm for a critique. They're
also not necessary!

The Fund Table contains several fields, which includes
Fund #, Fund Name, and Fund Style. From the Main Form I
would like to be able to use the lookup feature to find
the fund number, and once that has been entered, I have
two unbound fields on the Main Form, 'Fund Name'
and 'Fund Style' that I would like to use code to
populate these fields.

Use a lookup - but roll your own. It is NOT necessary to have a
"lookup field" in the Table. You can use the Combo Box wizard to
create a Combo Box control (a "lookup" if you will), let's call it
cboFunds, based on the Fund Table, and bound to the Fund # field in
the main table.

To verify that it's the right fund, include all the fields (Fund Name,
Fund Style, anything else you want to see) in the Combo Box's row
source query; and put textboxes on the Form with control sources like:

=cboFunds.Column(n)

where (n) is the zero based subscript of the field you want to see.

 

[Devon]

Thanks John, I will give that a try.

-----Original Message-----


Lookup fields are fraught with problems. See
http://www.mvps.org/access/lookupfields.htm for a critique. They're
also not necessary!


Use a lookup - but roll your own. It is NOT necessary to have a
"lookup field" in the Table. You can use the Combo Box wizard to
create a Combo Box control (a "lookup" if you will), let's call it
cboFunds, based on the Fund Table, and bound to the Fund # field in
the main table.

To verify that it's the right fund, include all the fields (Fund Name,
Fund Style, anything else you want to see) in the Combo Box's row
source query; and put textboxes on the Form with control sources like:

=cboFunds.Column(n)

where (n) is the zero based subscript of the field you want to see.


.