Wrong Value from Form/Table

[Arjay418]

Hi:

I have created a form with a combo box named Status. The RowSource is a table where each entry is numbered so that they show up in a particular order in the combo box drop-down menu:


ORDER STATUS
| 1 | Under Inspection
| 2 | Offer Submitted
| 3 | Under Contract
| 4 | Completed

for this combo box on the form, the column count is 2, and the column widths are 0"; 2". The bound column is 1. In this situation, the user can only make choices from the items listed under "status" and they don't see the numbers. Here the value stored in the table is the number, not the words. So i changed the information in the lookup tab in design mode to "combo box" and made other changes to get the table to display the right information.

Here the problem begins. I used the report wizard to create a report and grouped the data on this Status combo box. Now, on the report the group heading is the number. Instead of saying "Offer Submitted" and having records under that, it just says "3". The SQL statement that the report is based on turns out the actual words "Offer Submitted". The only apparent problem is in the report itself. it's almost as though i need to direct the control on the report to bind to a particular column in this combo box.

Need more info? any suggestions?

-rj

 

[Arjay418]

EDIT: in the last paragraph above instead of reading 'it just says "3" ' it should read "just says "2" '

no problems with columns not corresponding to one another.

 

[RoyVidar]

Combo boxes on forms are fine, but using table level lookups very easily leads to such confusion (here's a link The Evils of Lookup Fields in Tables) - see the combo might display a text, but the value is a number... creating lots of fun;-)

Besides, your not going to let your users have access to the tables, so why bother with how the data looks there? The essential stuff, is to display the data correct in forms and reports...

I think I'd advise to reset the lookup tab display control to text box, then recreate the form combo (wizards usually does a decent job). For the recordsource of the report, use a query where you've included the lookup table, and added the status field.

- or you could do it the easy way around, just create a query based on the current recordsource of the report, add the lookup table, collect the status field and add this to the group section.

Roy-Vidar

 

[lespaul]

What is the source for the report? That is where the information is coming from that is being displayed on the report, not the combo box selection. Now, the combobox selection may be a parameter in a query that is the source of the report. Have you checked that the SQL contains the 'STATUS' field from that table in the SELECT statement?

Leslie

 

[Glad2000]

I think the second way from Roy-Vidar is the easier way. Try it and you will see the very good results.
Make a query with the main table, add the Status table (lookup table), then add the Status field from the Status table (lookup table) to the report. That is all.

 

[Arjay418]

thanks for your responses

i will try it and check back in.


--rj

 

[Arjay418]

thank you all for your help. i guess i was just dense. i thought i had tried every combination of ColumnCount, ColumnWidths, and Bound Column values possible without results. It just took a little more tinkering to get them all straight. I had been confused with 0" Column Widths, Column(0) vs Column(1), and so on.

and yet again my computer narrowly averts a confrontation with my baseball bat.

--rj