updating a text box on a form with info in a combo box on a table

[billcar2006]

Hi all, I have a table called job Info, that has columns that are updated from combo boxes on a form. The form is a setup sheet that we can fill out for each job we do which has job Name, Drawing numbers and links to drawings, cycle times, tools used etc. the info for the list of tools is gotten from 3 linked tables which contain toolbrand, Toolname, and any bits that go with this tool like spare parts. It works up to this point. I now want to make a new form for when a job comes up again that we can view this data but not be able to change the data. Some info being entered doesn't need to be seen again by the machinist after initial entry so to declutter the screen I want a form to just view certain info. So on the new form I have a combo box to call the job name which changes the record number to the correct number. I then have a text box that I have entered in the control source =cbotoolname.column(2). It looks like this works as it is supposed to but because the table info where combo boxes have selected the data, my text box only shows the ID of the entry. Is there anyway around this.
Thanks

[June7]

Are you using lookups in table? http://access.mvps.org/access/lookupfields.htm

The combobox should have a column with actual descriptive text. Don't you have a master table of job names?

[billcar2006]

I am using lookups in the table. These are the fields I am having problems with. I take it that's why I am getting the id numbers and not the text. I thought there maybe a way around that. I am only self taught on access and I have only done a bit. I didn't setup a master table of job name. I just put them in the same table as the lookup fields as a text field.

[June7]

If you don't have a master table of unique jobs, then users can enter whatever they want into the job field?

If you want to provide db for analysis, follow instructions at bottom of my post.

[billcar2006]

Hi, thanks for replies. I have attached my database. If you think its too messy and don't want to look at it I would understand. Database design is not my forte. The want the users to enter the job names.

[June7]

JobInfo table should not allow duplicate job names. Can set the job name field as Index Yes (No Duplicates).

Combobox properties:

RowSource: SELECT ID, JobName FROM [Job Info] ORDER BY JobName;
ColumnCount: 2
ColumnWidths: 0";2.0"
BoundColumn: 1

Why the OnChange event?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

The structure of JobInfo is not normalized and will likely cause frustration.

[billcar2006]

Thanks for looking at it. I didn't know about indexing fields. I have added that. The on change event in the combo box was as I type the first 5 letters of the job name the combo box list is limited to jobs with those letters at the start. For example, we might have 50 jobs that start with 20sm, and down the line we could have a few hundred or more jobs. So I didn't want to have to search through that many jobs.
I knew you would say about the naming of control. I really need to improve that.

What do you mean the table is not normalized.
What I wanted to end up with was 2 forms open. One showing tool info and one showing setup info. Then when we want to change some job info like a different tool is used that the form we view the setup info on, we press a button that opens an edit page on the right record , which is the page I have made.

[June7]

Multiple similar name fields indicates non-normalized db structure. Example: Tip1Holder, Tip2Holder, ... Tip17Holder. What if you encounter a need for an 18th Holder? Or maybe you seldom need more than 5? What if you need to search for a value that could be found in any of the 17 Holder fields? What if you want to sum or count all the occurrences of that value?

This structure will likely cause a lot of frustration.

The combobox AutoExpand property essentially does what the OnChange event code does. Unless the list will grow to thousands of items, the OnChange is not needed.