Displaying combo box selection in a report

[sstasiak]

When creating a report, what does the control source need to be set to for a field that is supposed to be populated by a combo box selection.

In my instance, I have a form where a Primary Physician is selected from a combo box. This combo box looks to table tblPhysicians for its values. When I set the control source in the form to "Combo96", it gives me the ID associated with the physician in tblPhysicians instead of giving me the name.

What does the control source need to be set to for it to give me the physician name instead of the ID number?

 

[llkhoutx]

Rethink what you are doing. A combo box is not a proper control for a report.

Join the "combo box"'s recordsource in your report's recordsource to get the correct value.

 

[sstasiak]

I understand what you're saying, but not sure how to do it.

I have a combo box in my main form, OncRegMain, that gets its values from a table called tblPhysicians. The [PrimaryPhysician] field in my main table, tblOncReg, is storing the ID# from tblPhysicians when the appropriate one is selected from the combo box.

In my report, I'm not sure what to set the record source to in order to display the physician name instead of the ID number.

 

[boblarson]

If you have the physician name in the combo box's recordset (let's say you have 2 fields selected in your combo - PhysicianID and PhysicianName and you have the ID set to 0" width so that only the name shows up), you can use this in the Report's Text Box controlsource:

=[Forms]![YourFormNameHere].[YourComboBoxNameHere].Column(1)

 

[sstasiak]

Bob

That worked great.

Thanks

 

[sstasiak]

Bob

I've run into another problem. After applying a "Where Condition" for what my form should display, that code no longer works because the report isn't generated from the form, but rather the table that the form enters data into. So now it's getting the ID#'s from the main table.

It seems that the control source needs to be set to something like, =[tblPhysician]![PhysicianName] WHERE [tblMain]![PhysicianID] = [tblPhysician]![PhysicianID]. I don't know if that syntax is correct, but the logic makes sense to me.

Any ideas?

 

[boblarson]

You need to get the CRITERIA from the form. The recordsource for the report should NOT be the table, but a QUERY based on the table and in the criteria of the query, you reference the form's control.

 

[sstasiak]

bob

Not sure if I know how to do that. How would I create the query if the field in my main table is PrimaryPhysician, and fields in table[tblPhysician] being PhysicianID and PhysicianName?

 

[sstasiak]

Right now, the report works just like I want it aside from this one issue, so I don't want to change anything in the report other than this field. The control source for the 'Gender' field in the report is the data in the GENDER field from my main table(this stores a 1 for male and 2 for female) and this is selected from a combo box in the main form. Right now when I view the report, the gender field holds either a 1 or a 2. These are the ID's from the gender table that only has 2 entries, MALE and FEMALE.

Instead of the 1 and 2, I want the name associated with each to be displayed. ie, 1=male and 2=female.

I'm still very new to this so I need a bit of hand holding through this. Hopefully the details that I provided in previous posts is enough for someone to do so.

thanks

 

[boblarson]

Okay, here's a tutorial I made for you on how to change your recordsource in your report and how it links to the form and also how to show the names instead of the code.

http://downloads.btlarson.com/AWF/screenshots/PhysicianReport/PhysSample.html

 

[sstasiak]

Bob

Your help and effort is greatly appreciated. I was able to get it working another way. Not sure if it's the right way, but it works.

Here's what I did in case it helps anyone else(and maybe you can let me know if this is the "proper" way of doing it):

I have one main form with 6 tabs for users to enter new patients. They can also view and edit existing patients from this form.

On the form, there is a button to "View Report", so they can print the patient info and not have to go through all the tabs to view it.

The button has an "OpenReport" action assigned to it with a "Where Condition". The condition says to get the report data from record where the # in the MEDRECNO field in the main form is equal to the # in the MEDRECNO field of the main table.

What I did to fix the combo box issue in the report was to change the bound columns for all combo boxes in the main form to column 2, which was the text column. I had a few fields chosen by combo boxes: physician, gender, diagnosis. Now, in my main table, instead of holding a number value for these fields, the actual text was being recorded.

So now my report is outputting the text rather than the ID associated with that selection.

Now my question is: What are the benefits of storing an ID number in my main table rather than storing the text associated with it?

 

[boblarson]

1. If something changes (misspelled, etc.) you only have to update in one place.

2. It can be indexed and searches can be faster.

3. It follows the rules of normalization.

I'm sure there are more; I just can't think of them, at the moment.