Form data from a combo box

[Wlbyfred]

I am having a problem with my forms I created using combo boxes, when I choose the data from the drop down text is what I read on the form. But when I go to the table the data is in number form (single number) not the text from the drop down...any idea what I am doing wrong.
Thanks

[jzwp11]

Welcome to the forum!

Actually, you are doing nothing wrong. You should be storing the numerical value in the table not the text. The other general rule is that your users should never see your tables; all interaction should be through forms. You would set up your forms to show the text (just like you have) since that is what your users will need to see.


As a word of caution, having combo boxes in forms (as you mention) is fine, but having combo or list boxes in your tables is generally not recommended even though Access has the capability. (See this site for the problems table-level lookups can cause).

[Wlbyfred]

Thanks
But is there away for me to change a setting so that I can see the text and run reports for myself. I have set up the form for my users to use and they will not have access to the tables....my set of users are happy with seeing a simple form.
Thanks again in advance.

[jzwp11]

You would typically just set up a query and run that to see all of the information. You can also base an Access report on that query if you intent is to print out the data.

[Wlbyfred]

Thanks for your help
Wlbyfred

[jzwp11]

You're welcome

[Wlbyfred]

Ok
I know Iam new at this, but the fact that I set up a query then a report off of that...I still get a report of numbers and not text.
This learning curve thing can drive you crazy...I dont see a way on this reply for me to attach a copy of my database for someone to look at...I am sure its something simple because Ia m new at this.

[jzwp11]

To post a copy of the database, first run the compact and repair utility in Access (tools menu). Then zip the database. Click on the Go Advanced button below where you type in your reply to a post. This will show more tools including the attachment tool (a paperclip icon). Make sure there is no sensitive data in the database you post.

[Wlbyfred]

Ok here it is, hopefully there is just a beginners error.
Thanks in advanceworkorder_test.zip

[jzwp11]

OK, first I would recommend that you have more descriptive field names for the ID fields in each of the tables. Second, it is best not to have spaces or special characters in your table or field names because otherwise you have to enclose them in square brackets in queries and code. Third, all joins between tables should be with the key fields in each table. I have made all of those corrections in the attached database. I have also created a query that illustrates how to show the text values from the related table.

Now as to your structure, is a manager associated with a location or just the work order? If a manager is associated with a location can they be responsible for other locations as well? Are there multiple branches within a location?

BTW, I noticed the addresses you had listed. Are you in the Mentor/Painesville OH area? I grew up near the Cuyahoga/Lake county line but am now living in the Dayton, OH area.

[Wlbyfred]

Good morning
Thanks for the help I see what I need to do to the database I have running. You are correct I am in Mentor. I see that I have to edit my joins to get the text to come up. The way I want this thing to work is that staff here fill out the form using combo box selections that load into a table along with text box text, then I want to be able to run a query/report to track WO progress. Changes you have made will make it easier for me to change the data base I have on our server.
Thanks so much for the help

[jzwp11]

that load into a table along with text box text

You should only store the key value not both the key value and the corresponding text (in the Work order table). Storing both is a violation of normalization rules and could compromise the integrity of your data. The text data should only exist in 1 table. As I showed, you can build a query that brings the various tables together and select the various text fields. You can then base your report on that query.

[Wlbyfred]

Good afternoon
I have tried to duplicate your corrections on my database and I am getting more confused...I think I understand it and I cant get the realtionships to work like yours and I am driving myself crazy.
So I dont take up anymore of your time can you recomend a good guide or training material. I have a database right now that is working I just cant get past the query/text/report issues.
Its times like this that you really know how much you dont know
Thanks

[jzwp11]

This site has some good tutorials from what I have heard. What part of establishing the relationships is causing an issue? The fields that make the joins between tables must be of the same datatype. I always have an autonumber field as the primary key of the table. When you join to another table, the corresponding foreign key must be a long integer number datatype field. The autonumber datatype is just a special case of a long integer number.

[Wlbyfred]

I know but it wont work, you have ID joined with the ID of the table, but when I do it it gives me the message"relationships must be on the same number of fields with the same data type" unless I am missing something I ck'd to see that they are...just fustrating. I am going to leave it go for today and look at it fresh tomorrow...missing something and just cant see it