Wrong data in table

[bespra]

When I add or update records using my form, it returns the ID number in the fields on the table instead of the actual data. How do I fix that?

[alansidman]

Are you using lookup tables with combo boxes?

[bespra]

yes, the data shows in the form, but when I go to the table it shows the id number of the item.

[alansidman]

This is the way it is supposed to work. If you need to see the information, that the number represents, then you create a report or query and link the lookup table to the "main" table. This is how relational databases are designed to run. It makes them faster, more accurate and takes up less storage space.
Alan

[bespra]

The recordsource is a query so when I add a record why does it still show the item id?

[alansidman]

I don't understand the issue. Perhaps, if you post your database sanitized for confidential information it might help. Also, I noticed that you are using AC2010. Post as a earlier version so you can get maximum reviews.

[bespra]

Here is a sample of by database showing the information going from the form to the table as id numbers instead of data. Please help me figure out what I am doing wrong. I have saved this as Access 2003

[alansidman]

The recordsource is a query so when I add a record why does it still show the item id?

Why is your recordsource for the form a query? Why not the table that the query gets its data from? How are you inputting data? Using the form? You should change this to the table. Typically, you use a query as your record source for your form when you have unbound text boxes and update the table using VBA.

Anyway, if you want to see the values (text) that the tables reflect then you will need to do a query and link the lookup tables to the primary table (tblIran). Your tables are for storing data only. They are used to in queries to extract the information that you want in the style/format that you want.


You have also set up the program fields as lookups in a table. This is a general no-no. This should be done in a form. Read this about look up tables.

http://access.mvps.org/access/lookupfields.htm

They also should not be 4 fields but one with a one to many relationship with the Program table. This is a violation of normalization. Here is a link that you should read before you go on.

http://www.deeptraining.com/litwin/d...aseDesign.aspx


Alan

[bespra]

The 4 pgms are because the type of activity is being charged to four different programs.

Once I figure out how to get text in the fields on the table I will be starting with a table with no data. The form is to be used to populate the table, I am trying to figure out why I am getting id instead of the text in the activity and detail fields. I read the articles and setup my foreign key and changed the relationships but I am still getting id instead of text in those 2 fields.

[alansidman]

You will always get the ID in the table. This is the way Access is set up and designed. It is the efficiency of a database. As I have said in earlier threads, if you want to see the data, then you need to look at it in a query and not in the table if you want to use combo boxes. Relational databases are not spreadsheets and you need to lose the spreadsheet mentality when you are looking at tables. Aa table is where you "dump" all the information in no rationale sorted manner. In Queries and reports you extract the data in the form you wish to see it. You really should change the Programs to normalized. It will cause you issues down the road and you need to change the look up for it from the table to a form as noted in the link I provided you.

[bespra]

Thanks much. I have redone my tables and queries.