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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Attached is the sample with multicolumn combo box. Field txtManager is equal to column 1 (columns are started from 0) in combo box cboPhone
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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)
cansevinAuthor Commented:
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!


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

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could try using a DLookup in the ControlSource of that form:

=DLookup("PersonInCharge", "tblOfficePhones", "OfficeName='" & Me.Office &"'")
cansevinAuthor Commented:
When put the below in the Control Source it comes with #Name?

=DLookUp("PersonInCharge","tblOfficePhones","OfficeName='" & [Me].[Office] & "'")
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then you have something wrong in the DLoopup. I don't have your database, so I can't get the names and such exactly correct.

The syntax for DLookup is as follows:

DLookup("The field you want to return", "The table containing that field", "Any criteria you need to supply")

If the form you're running this on is NOT "MainForm", then you'd have to change the last part to:

 "OfficeName='" & Forms!MainForm!Office &"'")

Here's the MSDN article for DLookup:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cansevinAuthor Commented:
Awesome... got it to work. Thanks! One last small one... after I update that combo box... how do I tell it to update the DLookup of that field?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can try Me.NameOfYourTextbox.Requery
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.