Using MS Access/Report/Query


Oh, sorry, Scott but yes, you were right, the combo box is set Bound Column 1.

In Master Data table, there are tags called Task Owner (which I do not want and is rarely populated here) and Analyst Name (which I DO want) and both of these have the data type of Text.  The Analyst Name field shows names which are chosen by the users from a combo box I set to show the table [Picklist-Task Owner] which is also a Text data type.  

It's done this way because after the project was pushed out and people were using the database, management decided they didn't care about the Task Owner, which was a field populated when I imported spreadsheets and showed other employees outside our department.  I already had the pieces in place, had the picklist created with the same format I was about to have to create for an Analyst Name picklist so I used what was already there. It saved time (I thought) and I didn't have to get the users out of the database so I could change the backend.

Ok, first, you did the right thing in reusing existing objects. Not a problem at all. However, you should not have Analyst Name as a text field. This is not how to use a relational database properly.

From your earlier description of the picklist (I prefer the term lookup table). That table has an ID field, which I suspect is an autonumber and a the Task Owner field, which contains the name. The field in your Master data should be a Long Integer and hold the ID value not the name value. First, because it takes less storage to store a number than a name, second because it  creates redundant data and finally because the data might change. Lets say an Analyst changes their name.
By using a code as the foreign key you only need to change the name in one place. You should be using queries to pull the description.

But the way your data is, your code won't work. Since the Bound column of your combo is 1, the combo returns the ID which is the first column in your rowsource. So if your table is actually storing a text value of a name, since the combo returns a number it won't match. Either you have to change the bound column to 2 or change the datatype of the foreign key (whihc you have to do off hours).

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

P.S. This site limits followups (which is why you had to create new questions). If you want to continue this, I suggest using the Access forum at I monitor that site as well and there are no limits to followups.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.