Showing Code Descriptions not codes in reports

[Prufrock]

I have combo boxes throughout my database which show a code description to the end user but store the code in the database. Now I need to set up some reports where I have to list the description as the answer and in some cases show the code number in a separate box. Currently I get the code when I place a field in the report. This is fine for where I show the code above. I gather I need a text box which is probably doing a dlookup on the code table, loking up the code in the code field on the report and returning the description to the unbound text box.

I am just wondering if there are other more efficient ways to do this and if not what is the specific Dlookup code I require?
Thanks

 

[fdcusa]

I'll assume that your codes and code descriptions are stored in a different table, and for the code selected, the Primary Key of that record is stored in the Main Table as a Foreign Key.

If correct, all you need to do is create a query joining the Main Table and the Codes Table, joined on the PK for the Code records, make this the RecordSource of the Report, and then the Desciption field will be available for the Code selected.

 

[Prufrock]

Thanks for the quick reply. My problem will be I have sometimes 10 lookups in a form that people fill in. The lookups store code and code description. The user only sees code description when data entry occurs. As the code is stored in the database when I draw up a report that reflects the data entry form and allows for a print out it shows the code not the description. This is fine for some reports. However some need the description not the code.

The codes and descriptions are stored in a range of separate tables as this is how I received them from the Government body we are meant to be consistent with. For example a report might contain 12 lookup fields which are storing code which sit in 12 different data tables. With what you say I would need to link all those tables in a query which seems incongruous. Is there a quick way to show the descriptions of all the look up fields in the one report?

 

[fdcusa]

The proper way is to store the Code Description alongside the Code -- and store the PK of the selected Code in the Main Table...

I understand you have constraints against how the data is stored, and it sounds like the data is not normalized. Also, it sounds like the Code is literally being stored rather than the PK of the record -- which is a problem.

Seems the only way for you is to use DLookup in the ControlSource of the control's report to lookup the appropriate table. See

http://allenbrowne.com/casu-07.html

for tips and a faster replacement for DLookup.

 

[Remou]

It is also possible to use a combobox in a table. Howvere, the query suggested by fdcusa is not so difficult and the most flexible way of doing this. I hope that your tables do not contain look-up fields, these are not a good idea.

 

[dhookom]

I would always include the "12 different data tables" in the report's record source so you would have the option of displaying the code or the description. Using DLookup() is much less efficient.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Prufrock]

Thanks Guys, the thoughts you have offered give me food for thought and I will examine the situation and find the best way based on feedback. Thanks again