Access 2016 form converting text to ID in a linked table

Question

Hello -

I am new to Access and have been beating my brain and wasting A LOT of time trying to figure out how to correct a problem. I'm using a form to create records which are then linked to a table. The problem is that in my form, the fields all show text, but in the table, in reports, etc, the text is being converted to the ID number rather than text. For example, a tour record for Belville Elementary is showing up as "30" in the table of "Tour Records" rather than the name of the group. The same thing is happening with another field (Tour type) as seen here. I've been trying to figure out how to fix this, and am getting nowhere. Can anyone help?

Answer 1

What you have probably done is create Lookup fields on the table level. This is NOT recommended see Evils of Lookup fields.

So what is actually being stored is the ID, not the description. This is correct design, but there are different ways to display the name. The standard way is to do your lookups ONLY on the form level using list controls. The combobox wizard will set up a combobox that hides the ID column and displays the description column.

For reports, your report should be based on a query that includes the main data table and any tables used for lookups so you can bring in the description column.

Answer 2

This doesn't make any sense to me. The first image is the property sheet the form which is generating the table in question. The second image is the property sheet column in question (from the table view). What is supposed to be entered here for this to work? I am super frustrated and not smart enough for this!

Answer 3

Also, I don't see any options for a "combo box" wizard!

Answer 4

Ok, So you set the field as a Lookup field, but didn't specify a Rowsource. So you really need to set that back to Text box.

Second, you appear to be using a listbox rather than a combobox on the form. But a listbox won't display in datasheet view. You need to change that to a combobox. then you need to go to the Format tab and set the Column Count to 2 and the Column widths to 0";2".

As I said the combobox wizard will walk you through that. The Combobox wizard can be found in the Design ribbon under the controls section when a Form is open in Design view. Its the icon that shows a drop down list.

Answer 5

I was actually able to get my previous problems sorted out! Now I have a new issue that I can't figure out. I screwed up a table somehow and not only did I lose a bunch of data, now I can't add data without incident and I don't know.

1. There is a field appearing on the design view which doesn't show up on datasheet view.  It's the last field listed as "group name"

2. When I try to add a new record, I'm getting the following error message (see attached shot).

3. When I was able to add a new group to this table, it was appearing "blank" on the form all of a sudden. This was all working so great yesterday, and I don't know what I did to screw it up! I've attached some screenshots.

Also, you're being very helpful! Thank you! I am about to flip my desk over! I am SO frustrated!