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?
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:
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)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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] & "'")
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.