Pulling Existing Table Data w/ a Combo Box

[Guest]

I have a data entry form used to input information about a home property. On
this form, I want to pull-in information from 2 other tables table via a
drop-down combo box. Here's a detailed explanation....

On the right side of the form, I want to visibly display information about
the LENDER and the REALTOR associated with that property. They both have
their individual tables. One table that stores all my LENDERS and one table
that stores all my REALTORS. On the top right, I want a combo-box that
allows me to select any one of my LENDER records. And immediately following
that box, I then have "disabled" fields that display LENDER PHONE, LENDER
ADDRESS, LENDER FAX, LENDER EMAIL, LENDER MOBILE, etc. These fields update
strictly based on whichever record I select from the LENDER drop-down
combo-box.

Then on the bottom right, I want another combo-box that allows me to select
any one of my REALTOR records. On this form I then also have "disabled"
fields that display REALTOR PHONE, REALTOR ADDRESS, REALTOR FAX, REALTOR
EMAIL, REALTOR MOBILE, etc. These fields update strictly based on whichever
record I select from the REALTOR drop-down combo-box.

I already have a QUERY in place that scans all the records in my PROPERTY
table with all the records in my CONTACTS table, even though I don't use all
the fields. This was done to associate a CONTACT with the PROPERTY record.
That works successfully. So with every property I can successfully change
the CONTACT drop-down combo box and all the other fields update as they are
stored in the CONTACT TABLE (i.e. CONTACT PHONE, CONTACT ADDRESS, CONTACT
FAX, CONTACT EMAIL, CONTACT MOBILE, etc.) simply by chosing the CONTACT
record in the combo box. I now need to do the same for LENDER and REALTOR
and I don't know how.

I've been struggling w/ this literally all day until I threw in the towel
moments ago. Is there a way to use DLookUp to solve this problem? Is there
another way to accomplish what I'm trying to do? If I add the LENDER and
REALTOR tables to my Query, Access responds w/ "Too many fields".

In short, how can I pull in data from my LENDER and REALTOR tables via a
drop-down box and then have all the related fields on my PROPERTY form
display correctly. I can send screen shots if needed.

Thanks so much. Any help is very greatly appreciated.

Thanks in advance.

Regard,

Frank

 

[Guest]

Hi, Frank.

If I understand you correctly, an easy way to do this is using the Column
property of your combo boxes. Include all the fields you wish to display in
the Row Source of your combo box. You needn't display them in the drop-down
list; just set their Column Width to 0". You can then display them in a
textbox by referring to their Column number in the combo box. Column is
0-based, the first column is 0, then 1, etc. To specify the third column,
e.g., set a textbox' Control Source to:

= MyComboBox.Column(2)

Hope that helps.

Sprinks

 

[Guest]

Sprinks,

How do I include all the fields I wish to display in the Row Source of my
combo box? Via a query? Through Expression Builder?

Note: Any other specifics I need to note on the combo box? Does my PROPERTY
table need to have a field called REALTOR NAME and LENDER NAME? And do those
fields need to be linked to the REALTOR table and LENDER table respectively?

If you can instruct me how to successfully include all the fields in the Row
Source of the combo box, that may help. Thanks.

Frank

 

[Guest]

Firstly, to take advantage of the *relational* aspect of Access, the only
field from either Realtor or Lender that you should store in the Property
table is the foreign key, i.e., the field corresponding to the primary key.
You can have access to any other field in this table by linking on this field
through a query.

To assign the Row Source of your combo boxes, it's easiest to use the
wizard. In form design view, toggle on Wizards by selecting View, Toolbox to
display the Toolbox toolbar, and toggle on the button with the magic wand and
stars. Place a new combo box, tell the wizard to look up values in a table
or query, select the relevant table (Realtors or Lenders). Select all of the
fields you'd like to display AND the table's primary key, as the first
column. In the next dialog, choose Hide Key Field (recommended), then tell
Access in what field of Properties you'd like to store the field. This
should be a numeric field corresponding to the numeric key.

Access will set the Column Widths property to 0";x1";x2", etc., where x1, x2
are widths in inches to allocate to the display in the drop-down list.
Assuming your Realtor or Lender name is the 2nd column, and that's all you
need to see when picking, set the other column widths to 0". Now, when the
user selects a value, the Name will be displayed in the combo box, because
its the first column with a non-zero column width. But what is STORED in the
field specified in the Control Source is the key field.

Use textboxes for the other fields as described earlier.

Hope that helps.
Sprinks

 

[Guest]

Sprinks,

That worked 100% perfectly. I am very, very greatful. Thanks for saving
the day. Folks like you make this community all worth while. Thanks again.

Frank