Report only shows Combo box primary key instead of text (1 Viewer)

[Sticky99]

Been struggling with this for a few hours, I have a form with a Combo box to select customer details, which works fine, however,, when I print a report the Combo box selection shows the record ID and not the Customer name (which is the combo box selection), can anyone help stop me pulling more hair out!

 

[theDBguy]

Hi. One of the simplest/quickest fix is to change the Textbox on your report into a Combobox with a Row Source that looks up the Text value of the Primary Key from your lookup table.

 

[CJ_London]

for your combo on your report have you set the number of columns to at least 2 and assuming the ID is the first field, set the column width to 0?

 

[zeroaccess]

CJ is likely right - and the combo box wizard will do that for you.

Other ideas: Are you using a query as the recordsource for the report? If so, convert your table-based recordsource to a query and:

Draw a line in the query design to make a relationship between your Customer field in one table and the Customer ID in your Customers table.

Then, add the Customer field to the query. The DB will know the Customer Name based on the relationship and will look it up. This will make it a simple affair to add controls to the form that display properly.

 

[vba_php]

Been struggling with this for a few hours, I have a form with a Combo box to select customer details, which works fine, however,, when I print a report the Combo box selection shows the record ID and not the Customer name (which is the combo box selection),

Sticky, see attached. is this what happened? if you created a combo in a form, then simply created a report based on the form, you will have happen what you're talking about.

 

[Sticky99]

Thanks guys for your replies, I haven’t had a chance to try an god them yet, Hoping to get round to it later today. I’ll post back the results. Again, thanks all for taking the time to get me out of a hole!

 

[Sticky99]

CJ is likely right - and the combo box wizard will do that for you.

Other ideas: Are you using a query as the recordsource for the report? If so, convert your table-based recordsource to a query and:

Draw a line in the query design to make a relationship between your Customer field in one table and the Customer ID in your Customers table.

Then, add the Customer field to the query. The DB will know the Customer Name based on the relationship and will look it up. This will make it a simple affair to add controls to the form that display properly.

Hi ZeroAccess,
My report is based on a query which pulls all the correct data from the form and sub-form with the exception of the customer name, which is the combo box selection, this returns the ID in the field. Could you expand on your solution of converting the table based recordsource as I'm a little uncertain on how to go about this. Apologies for the ignorance!

 

[Sticky99]

for your combo on your report have you set the number of columns to at least 2 and assuming the ID is the first field, set the column width to 0?

Hi CJ_London, yes I have set these as above.

 

[Uncle Gizmo]

I've covered this combo box issue in a YouTube video which you can find on my website here:- https://www.niftyaccess.com/combo-box/ ... look for the section:- "Combobox - Displaying a Number?" video number 6...

 

[Uncle Gizmo]

It is good practice to store information in the smallest possible format. This is normally achieved by storing "repeating information" in a table. Each row in the table is identified by a unique identity an "ID field"... For example you could have a table which stores customer information, it might store:- salutation; surname; first name; address; telephone number. Each row of "information" would have a unique ID.

When you want to use this information somewhere else, let's say you want to provide the customer details for an orders table, in the orders table you only need one field to identify the customer. You store the ID from the customer table in this field. But that's not a very user friendly method because the user is never going to know which customer any particular number refers to. This is easily solved by using a combo-box as effectively a up look-up agent.

When you create your "Orders Form" based on your orders table, it will very likely create a text-box for the customer field. As suggested this text box will just display the number relating to the customer. However if you change this text box into a combo box, you can do this by right clicking on it, then selecting the third option in the list "Change To" now you have a combo box. It's always a good idea to follow a naming convention so immediately find the combo box name in the property sheet under the tab "Other" and prefix it with "cbo".

Also the Combo Box default name might not mean much, and may contain spaces. Remove the spaces and rename it something meaningful to your programming processes. Now whilst you've got the property sheet open, select the data tab, select the ellipsis (...) at the end of the "Row Source" property and you will be taken to the query building tool. Select a Table, in this case you want the Customers Table. Select the customer ID field and the customer name field so they appear next to each other in the query builder grid. You need a minimum of two Fields, the ID field and the customer name field. You may want other Fields, for instance you may have a Boolean field to flag a discontinued customer. Select this field and set the criteria option to "False". Now discontinued customers will not show in your results. You don't want to see the "Boolean field" in the combo-box so make sure the "Show" checkbox is unchecked.

Close the query builder Grid and re-open the property sheet for the combo-box as we've got just a couple more things to check/set-up. On the data tab make sure that the "bound column" property is set to 1 and "limit to list" is set to true (Yes)... Select the format tab and set the column count property to 2, and the column widths property add the Text "0, 2" ...

The "bound column" is the column where the data from the combo box will be taken and fed back into your orders table, and you want the customer ID which will be bound column 1, the first, the left most part of the SQL/Query, in this case the ID. The column width property of the Combo Box is a way of specifying what actually appears in the actual combobox, is Visible, is what the user sees. In this case we have have query which returns two columns, the ID and the customer name. But we don't want to see the ID, we just want to see the customer name. To do this set the "column widths" to 0; 2 --- This shows Nothing of the ID, but 2cm of the customer name. Some versions of MS Access will not show centimetres but inches so the figures might be slightly different for your version of MS Access.

There are several other properties you can change for a combobox, however these property's I have explain here are the basics and you will seldom find yourself changing anything else.

 

[Sticky99]

It is good practice to store information in the smallest possible format. This is normally achieved by storing "repeating information" in a table. Each row in the table is identified by a unique identity an "ID field"... For example you could have a table which stores customer information, it might store:- salutation; surname; first name; address; telephone number. Each row of "information" would have a unique ID.

When you want to use this information somewhere else, let's say you want to provide the customer details for an orders table, in the orders table you only need one field to identify the customer. You store the ID from the customer table in this field. But that's not a very user friendly method because the user is never going to know which customer any particular number refers to. This is easily solved by using a combo-box as effectively a up look-up agent.

When you create your "Orders Form" based on your orders table, it will very likely create a text-box for the customer field. As suggested this text box will just display the number relating to the customer. However if you change this text box into a combo box, you can do this by right clicking on it, then selecting the third option in the list "Change To" now you have a combo box. It's always a good idea to follow a naming convention so immediately find the combo box name in the property sheet under the tab "Other" and prefix it with "cbo".

Also the Combo Box default name might not mean much, and may contain spaces. Remove the spaces and rename it something meaningful to your programming processes. Now whilst you've got the property sheet open, select the data tab, select the ellipsis (...) at the end of the "Row Source" property and you will be taken to the query building tool. Select a Table, in this case you want the Customers Table. Select the customer ID field and the customer name field so they appear next to each other in the query builder grid. You need a minimum of two Fields, the ID field and the customer name field. You may want other Fields, for instance you may have a Boolean field to flag a discontinued customer. Select this field and set the criteria option to "False". Now discontinued customers will not show in your results. You don't want to see the "Boolean field" in the combo-box so make sure the "Show" checkbox is unchecked.

Close the query builder Grid and re-open the property sheet for the combo-box as we've got just a couple more things to check/set-up. On the data tab make sure that the "bound column" property is set to 1 and "limit to list" is set to true (Yes)... Select the format tab and set the column count property to 2, and the column widths property add the Text "0, 2" ...

The "bound column" is the column where the data from the combo box will be taken and fed back into your orders table, and you want the customer ID which will be bound column 1, the first, the left most part of the SQL/Query, in this case the ID. The column width property of the Combo Box is a way of specifying what actually appears in the actual combobox, is Visible, is what the user sees. In this case we have have query which returns two columns, the ID and the customer name. But we don't want to see the ID, we just want to see the customer name. To do this set the "column widths" to 0; 2 --- This shows Nothing of the ID, but 2cm of the customer name. Some versions of MS Access will not show centimetres but inches so the figures might be slightly different for your version of MS Access.

There are several other properties you can change for a combobox, however these property's I have explain here are the basics and you will seldom find yourself changing anything else.

Thanks Uncle Gizmo for your reply, I do not have an issue with the Form Combo box returning the correct text, it is when I try to print a report that I get the issue of just the ID returning in the customer field. Shouldn't the customer field in the report return the text shown in the combo box in the Form?

 

[Uncle Gizmo]

Shouldn't the customer field in the report return the text shown in the combo box in the Form?

If it is just a text box on your report, then it will quite correctly just show the number in the table field that it relates to.

If you want to show the related customer details, then change the text box to a combobox in exactly the same way as I describe above. The newly created combobox on the report will display the customer name.

 

[Uncle Gizmo]

If you don't want to have a a combo box displayed on your report, many people don't like the little arrow, there are two ways of avoiding the "Arrow" problem. One; create a rectangle control and cover the portion of the the combo box which you don't want to see.

The other; base your report on a query and add a look up based on the customer table to show the information you require from the customers table.

 

[zeroaccess]

Combo boxes on a report will not show their drop down arrows

 

[Sticky99]

Thanks Guys, all sorted with the Combo solution. Again great help from you guys!

 

[Uncle Gizmo]

Combo boxes on a report will not show their drop down arrows

Ah! yes, I was anticipating a non-existent problem! Covering the combo-box arrow is usually done on continuous forms where you want the combo box to look like a text box... I got a bit carried away!

 

[theDBguy]

Thanks Guys, all sorted with the Combo solution. Again great help from you guys!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[zeroaccess]

Ah! yes, I was anticipating a non-existent problem! Covering the combo-box arrow is usually done on continuous forms where you want the combo box to look like a text box... I got a bit carried away!

Now if only they would allow this as a Property on forms.