Combo Box not showing the correct Field

[Atlascycle]

I have a report based off of a Query, 2 of the Fields are displaying the records Primary key instead of the Field that I am trying to Display. The query shows the correct field but the report will not.

What Property controls which value is displayed?




Thank you in advance for any help offered.

Jason

[jzwp11]

The column widths property govern what is displayed in the combo box.

The field with the first non-zero width is the one that is displayed.

[Atlascycle]

I have Attached my project.

The Report is the LOTO Survey Rpt. When the Report Opens it is Asking for a Machine No. Entering A1 will pull up a record, The Machine type and Location fields are the ones that are giving me problems.

I was unsure of where I needed to adjust the column widths, in the report or in the table.


Jason

[Atlascycle]

I tried placing a copy of the database in a compressed folder and it has a file size of 6671 KB but it will not upload.

[jzwp11]

Run the compact & repair utility in Access (tools menu) & then zip it.

[Atlascycle]

Compact and repair does not seem to do anything.
I have a linked table to another database. could that be causing the upload problem?

Finally

[jzwp11]

I was unsure of where I needed to adjust the column widths, in the report or in the table.

The column widths property is a property of the combo box in the report.

I was able to open the report in design view but since the machine type is based on the linked table and you did not provide that, I cannot determine the row source nor the appropriate column widths.

The row source of the combo box will look something like this

SELECT fieldname1, fieldname2 etc. FROM TableName

The column widths property would look like this:

1";2"; etc. Each width corresponds to a fieldname in the row source

For those you do not want the user to see set the width to zero. The one that will be displayed after the user moves away from the combo box, is the fieldname that corresponds to the first non-zero width. In the example below, the 3rd field will show after moving away from the combo box, but both the 3rd and 4th fields will show when the user clicks on the dropdown arrow.

0";0";1";2"

[Atlascycle]

Attached is the other File with the linked tables.

thanks again for your help


Jason

[jzwp11]

OK, at the heart of your problem is that you have lookups (combo/list boxes) in your tables. Although Access has this capability, it is generally not recommended. This site explains why. I see that you use them extensively, so it will take a lot of effort to get them cleaned up.

Also, you have some normalization issues with your database. In the LOTO table for example you have fields for various steps of 3 different types. These should all be records in a related table.

The normalization issue is critical and will have to be fixed before you work on any forms, reports or queries.

Also, it is generally recommended to not have spaces or special characters in your table or field names

[Atlascycle]

Thank you for the help. I will work on cleaning it up and post progress in the morning.


Jason

[Atlascycle]

Is there a way to limit the choices for a field without doing the lookup?

[jzwp11]

The best approach is to put all of the choices as records in a table. If some choices are only applicable to some things but not all things, you can add a field to that table to distinguish the related choices. You could then use a query to filter based on that field. You can base the combo box's row source on that query.

For example, in your LOTO table you had the following items in a value list for the energysources field: "480 vac";"240 vac";"208 vac";"110 vac";"Compressed Air";"Gravity";"Hydraulics"

The 480 VAC, 240 VAC, 208 VAC, 110 VAC all refer to electrical so you may have a table that has a field to identify whether the energy source is electrical, pneumatic etc. That structure would look something like this

tblEnergySources
-pkEnergySourceID primary key, autonumber (I always have an autonumber field in each of my tables)
-txtEnergySourceName
-fkESCategoryID foreign key to tblEnergySouceCategories (this would have to be a long number integer datatype field)

In the above table, you would have a record for each of you value list items


tblEnergySourceCategories
-pkESCategoryID primary key, autonumber
-txtESCategoryName

The above table would hold a list of categories (each category would be a record): electrical, pneumatic etc.

If you just want to show those energy sources that are electrical, you would create a query based on tblEnergySources where the category field corresponded to electrical.


You would still use lookups (list or combo boxes) but at the form level not in the table.

[Atlascycle]

So After I create the Tables in this way then I Add the relationships in the Relationships window, linking the -pkESCategoryID primary key, autonumber to the -fkESCategoryID, Correct?

When you are creating your Tables do you use the pk and the fk in the field names?


I did the preceding and then filled in the fields in the tblEnergySources and manually entered the CategoryID number.

Should I have waited and created a form to do so?

Thanks Again for the help

[jzwp11]

So After I create the Tables in this way then I Add the relationships in the Relationships window, linking the -pkESCategoryID primary key, autonumber to the -fkESCategoryID, Correct?

Yes

When you are creating your Tables do you use the pk and the fk in the field names?

I do include the prefixes. It helps me to keep things straight. I also use prefixes for other field types that help identify the datatype of each field:
txt=text
dte=date/time
long=long number integer field that is not a foreign key
log=logical (yes/no)
sp=single precision number
dp=douple precision number
curr=currency

It is up to you if you want to use the prefixes. Just make sure not to have spaces or special characters in your table or field names otherwise you have to enclose those names in square brackets. That can be a hassle when writing queries, macros and code since it adds a lot of work and if you forget to add the brackets, things won't work correctly.

I took a look at your database. For the primary key, you just called it "ID", I would recommend using a field name that is more descriptive. If all of your tables have a primary key field name of "ID" it will be very confusing! It is more important to assign a descriptive name compared to using the prefixes.

Should I have waited and created a form to do so?

Not necessarily. For simple stuff while doing development work, you can enter it directly in the tables. Of course, your users should never have access to or even see your tables. All user interaction with data should be through the forms you create.

[Atlascycle]

Would you suggest to just add new Fields to the LOTO Table to eleminate the look up fields or to manually change the display control to text box?

I created the tblEnergySources and the tblEnergySourcesCategories and added the relationships. In the previous lookup field I was able to have more than one value for each record for that field, will I be able to do that again? Is that Recommended or is there a better way to achieve this.

Or should I have another table that will list all of the energy sources per machine?