Data Selection Based on Another Field In Table

[Guest]

I use Access 2000.

I have these 2 tables(as well as others): tbCompany. tbMeeting

Some companies have the same name but different addresses. Each company has
1 Main Contact person. plus additional individual names related to that
company.

Each Year there is an Annual Meeting. Some companies will attend this
meeting together with some of its individuals.

To enter data in the tbMeeting table I would like to have a ComboBox drop
down named AttendingCompany that shows the CompanyName, CompanyID, Address,
State, And ZipCode.

When I choose a CompanyName from that ComboBox, I would like the MainContact
field in the tbMeeting table to be automatically filled based on the
CompanyID shown (selection) which is the 2nd column.

However, I want to always see the CompanyName in the ComboBox field NOT the
CompanyID.

I am at a loss how to accomplish this ! Any help would be greatly
appreciated.

In addtion, I want to create a form using the tbMeeting together with a
subform that lists all individuals related to the CompanyID. But 1st things
1st.

 

[Guest]

I am assuming your form is based either on tblMeeting or a query based on
that table.

The row source for the combo you want to use should be a query based on
which ever table contains the data. To use the CompanyID but keep in hidden,
make it the first column of the combo. Make it the bound column. Set it's
column width to 0 in the Columnwidth property.

To put the selected item into the table, you will use the combo box's After
Update event and populate which ever control on the form that is bound to the
field in the table you want to update.

 

[Guest]

Hi Klatuu:

Thanks for your quick response!

Yes - my form is based on tblMeeting.
I changed the column order and made the CompanyID the 1st column and 0"
column length. Now I see the Company Name 1st as I wished.

After trying various things to automatically populate the Main Contact Field
on the Form (referring to your instructions) I am still unable to populate
this field correctly.

In the Main Contact field in After Update I entered:
=[Forms]![NBPAMeetingInfoForm]!MM_Name.Column(5)

The result is that it shows correctly the Company ID but not the related
contact (which is column 5). If I change the column number, I still get the
same result.

If I try:
In the Main Contact field in After Update I entered:
=[Forms]![NBPAMeetingInfoForm]![MMAttendCompanyCombo].Column(5)

The result is that it shows correctly the Company ID but not the related
contact (which is column 5). If I change the column number, I still get the
same result.

In addition. I'm not sure if the "Row Source" should be filled in since this
is ony a lookup field. Should the Main Contact on the form be unbound?

Am I placing the code in the wrong field? Combo box or Main Contact?
I would appreciate your further assistance! Thanks