Display 2 values in same control (1 Viewer)

[snorf3]

I want my combo box control to display two columns: BillingCode and Fee.

Right now, BillingCode is stored in the table.

I want the control to use the stored BillingCode and look up the appropriate fee, and then display both the code and the fee in the report.

I've tried playing with properties and setting up queries, but each time it only displays the code (the bound column) and not the fee.

Help! What am I doing wrong?

 

[Rich]

Which table is billing code stored in?

 

[snorf3]

All the billing code info ([BillingCode], [TypeVisit], [Fee]) are listed in "tblBillingCodes". I use this table as a look-up value in the field [BillingCode] of my table "tblClinical".

So, a specific code is stored in a record in "tblClinical". When I run my report of "tblClinical", I want the [BillingCode] to display not just the code number but the dollar amount [Fee] as well.

Hope this is the info you needed. THanks for your help!

 

[R. Hicks]

Include the table in the recordset the report is based on. Then you need to add the necessary fields form that table to the query. If you have you relational joins correct the query will return all the info you need.

HTH
RDH

[This message has been edited by R. Hicks (edited 09-28-2001).]

 

[Pat Hartman]

If it is possible for the fee associated with a billing code to change over time and you want your reports to reflect the value applicable at the time a record was created, you will need to store the actual fee amount instead of or in addition to the billing code. If you always want the fee to show as the current rate even for old records, then no change is necessary.

 

[snorf3]

Thanks to both of you for your advice. A further questiont then:

How do I get the fee stored along with the billing code? I will obviously need to create a new field in "tblClinical", right?

Right now, I have a combo box listing all the billing codes with the type of visit next to it on my form. I don't want to have to add another combo box and have the user select billing code twice...is there some way to have more than one bound column for the combo box?

Summary: I have one combo box that displays BillingCode, VisitType, and Fee for the user to select. Right now only BillingCode is stored. How can I use one combo box to store both BillingCode and Fee?

 

[Pat Hartman]

Fee should be included in the query used as the RowSource for the combobox. Then in the AfterUpdate event of the combobox put -

Me.txtFee = Me.YourCombo.Column(2)

The columns of the combo are references as if they were a zero-based array. .Column(2) refers to the third field in the combo's RowSource, .Column(0) refers to the first. So adjust the index as necessary to pick up the Fee column.

 

[snorf3]

Thanks pat!

I've added the field [Fee] and added a text box on the form. It fills wonderfully when the BillingCode is selected.

Just one last question. The fee is stored as a text value without the $ symbol. If I change the datatype of [Fee] to currency, it no longer gets updated from the combo box. Is there a simple way to get the $ added in to the beginning of the fee?