Access question regarding lookup fields and entering data directly into tables.

I'm new to Access, and have been understanding the program fairly well.  But I've run into a dilemma.  I'm using queries to populate combo boxes to make entry into datasheet view faster and more consistent (I'm not currently using forms to enter the data).  So in table design view, I'm setting the type for the field to short text, creating a query to another table which has the categories I want to select from, and all is well.  

But I'm reading that I shouldn't be doing it this way.  But the alternative is entering info via primary key ID in some other table and of course there's no way to tell what numbers are associated with employee names, company names etc.  Furthermore, some of my lookups have no started returning numbers (primary key ID's i'm guessing, since something has failed with the look up) instead of the associated information.

So how do I resolve the phantom numbers where readable info once was issue, and also, how do i circumvent this problem without using a lot of forms.  This DB is just for me and one other person, both of which are IT staff so making a lot of pretty forms seems like an unnecessary project.  Thanks!
Who is Participating?
als315Connect With a Mentor Commented:
No, but you can't change type of field when it is used in relationships. You can add this relationship again. It is not real change of field's type, it is master, where proper field type will be selected. You can do all this work manually. Look at picture, where all changed fields are marked
pdebaetsConnect With a Mentor Commented:
You may have some orphaned records. That would explain why you have numbers in some "combo box" field rows and the associated value in others. You should define relationships between these two tables, link up the primary key in the categories table with the (foreign) key in your main table and specify "enforce referential integrity".

You don't need forms, but they are useful for data validation and as a place to put buttons, charts, list boxes, etc...
LB1234Author Commented:
Wow, I need to proofread better.  The paragraph above is a disaster.  Anyway, I have a department and employee table, and I've created a relationship between them (one department to many employees).  However the department field is still showing numbers, as I would expect it had to since it has to be the same data type as the autonumbering ID in department.  So then, how do I display text in a field that's assigned a numerical datatype?  I'm not getting this. THanks for your help.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

als315Connect With a Mentor Commented:
LB1234Author Commented:
Als, watched the whole thing, thanks.  One of the first things she does is break the relationship between the two tables in order to resolve the mismatch data type issues between the primary key and the foreign key in the lookup field.  But what of the relationship?  Are table relationships and lookup fields mutually exclusive?
LB1234Author Commented:
Thanks so much!  So she just forgot to remake the relationship at the end of the video?  I assumed since she didn't recreate the relationship in the vid, it just stayed undone.
Jeffrey CoachmanMIS LiasonCommented:
Here is the deal...

You create a combobox to display the list of values.
The combobox will store the numeric value, but it will also "display" the text value.

Not sure why you are not using a form, ...then this is all easier (IMHO)

See this very simple example of how this normally works.

Study it carefully and see if you can understand all the design aspects and control properties
LB1234Author Commented:
Hi Boag, I'm not using tables because I've already got a bunch of data entered into various fields in existing databases.  Don't want to re-type all that stuff into forms.  I think it'd be much easier to various queries create new tables, append, delete, etc in bulk.
Lookup fields behave unpredictably at times, and most developers who have used Access for many years tend to shun them (see article here:

Combo boxes are a user interface tool and, in my opinion, the correct place for a user to enter and edit data is in forms, not tables.  It is very easy to create simple, workable forms using the wizard and they really don't have to be "pretty" unless you want to put in the effort.  Forms offer all sorts of advantages over tables for data entry and viewing - not least because you can view data from multiple related tables at the same time - for example, you could have a form showing all the fields from your departments table, with a subform listing all the employees in that department.

Anyway, I shall finish preaching now and get back to your combo box problem  :-)

First, as Peter has said, you should set up a relationship between your two tables which enforces referential integrity (RI).  This will ensure that you can never have an employee record with a DepartmentID value which does not exist in the Departments table.

Then you should ensure that all the properties of your combo box are correctly set.  A combo box typically has a RowSource with two fields - the stored value and the displayed value.  In your case, the RowSource should be something like this:

SELECT DepartmentID, DepartmentName FROM Departments ORDER BY DepartmentName;

The ColumnCount property should be 2, corresponding to the number of fields in the RowSource.

The BoundColumn is the column containing the value to be stored - in this case 1 (DepartmentID).

Finally, set the ColumnWidths.  This property has a text value in the form "a;b;c", where a is the width of the first column, and b the second, etc.  If one or more of these values is omitted (for example, "a;;c") then the corresponding column(s) get an equal share of the remaining space.  

The displayed column (the one showing when the combo box is not dropped down) is the first column with a non-zero width.  Putting this together, with a two-column combo box, if we simply set ColumnWidths to "0" then the first column is hidden (zero width) and the second column (unspecified width) occupies the entire combo box.  

Setting these properties correctly should ensure that you always see the DepartmentName, not the DepartmentID.

If you prefer to stick with the table for data entry then these properties can be set in the lookup control in design view, but I highly recommend you use a form.

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
LB1234Author Commented:
Graham, thanks so much for the time and effort involved in writing that!  The issue that I've already got hundreds upon hundreds of records already.  Don't want to have to hand enter them using forms.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

I regularly remove look ups from fields in tables. There is no data lost.  I create a form in continuous view to enter data. It looks a lot like the table gird only better. Now you get the full power of a form. For example, it allows me to add use the "Not In List" to let user add data to the look up list. Something that people were asking about int he comment on youtube.

I should thank the Access Dev team for this "feature". It has been a great source of revenue.
You don't have to re-enter data into forms. The data is stored in your tables. Forms provide a view to the data already in your tables. Forms also allow you to add (update, view, delete) records in your tables.
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.