asked on

Field in form

In a field in my form to look value from a table. What is the easiest way to make that happen?

There is another field in the form called Office which is a drop down selection. This also is the first column of the table called tblOfficePhone. After that is selected on the field I would like one of the other fields to be updated to the 3rd column of the table which is titled Manager.

What's the easiest way to make that happen?

als315

Attached is the sample with multicolumn combo box. Field txtManager is equal to column 1 (columns are started from 0) in combo box cboPhone
DB-test0.accdb

Scott McDaniel (EE MVE )

As als mentions, a multicolumn combo will do what you want. To expand on that a bit:

The RowSource of that combo would be an SQL statement that includes all the data you need to work with - for example: SELECT Phone, Extension, Company FROM tblOfficePhone.

You would then set the combos properties to reflect what you're working with:

ColumnCount: 3
ColumnWidths: 1;1;1

Note in the ColumnWidths above, if you want to hide the second and third columns, you'd enter 1;0;0 instead.

Now in the AfterUpdate event of the combo you can refer to those other columns:

Me.SomeTextbox = Me.YourCombo.Columns(1)
Me.SomeOtherTextbox = Me.YourCombo.Columns(2)

cansevin

ASKER

Thanks Scott... I am trying to do it without a combo box. I have too much code already attached to the field and I am worried if I change the field, it will be too difficult to change all the code associated with it.

I created a query based on the table of information it needs to pull from. The query correctly reading the current field with an expression of [Forms]![MainForm]![Office]. I will copy the query below.

My question is how do I get the 3rd column in the new field box? I need the new field to be the tblOfficePhones.PersonInCharge. After I update that combo box, I would like it to change the new field to the 3rd row. I am trying to do it with "Default Value"... not sure if that is the way.

Hope this makes some sense to you. Any help you can provide would be great!

Chris

SELECT tblOfficePhones.OfficeName, tblOfficePhones.OfficePhone, tblOfficePhones.PersonInCharge, tblOfficePhones.Email, tblOfficePhones.Pronoun
FROM tblOfficePhones
WHERE (((tblOfficePhones.OfficeName)=[Forms]![MainForm]![Office]));

Open in new window

Scott McDaniel (EE MVE )

You could try using a DLookup in the ControlSource of that form:

=DLookup("PersonInCharge", "tblOfficePhones", "OfficeName='" & Me.Office &"'")

cansevin

ASKER

When put the below in the Control Source it comes with #Name?

=DLookUp("PersonInCharge","tblOfficePhones","OfficeName='" & [Me].[Office] & "'")

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial