From what is sel;ected in a combo box populate a text box 1

[DPROMICS]

I have a subform where patient clinic visit information is entered.

On this subform, amoung the many other combo and text boxes, there are two boxes of interest, one a combo box and the other a related text box. The combo box allows a user to select the clinician's initials that saw the patient in the clinic visit. The text box I would like to contain the full name of the clinician that saw the patient in the clinic visit.

The Combo and the Text boxes are bound to fields in a table called Tbl_Coumadin_Clinic.

The clinician's initials combo box is labeled CBo_Clinic_Staff_Initials. It is bound to the field in the clinic visit table called Clinician_Staff_Initials. The combo box gets it drop down list data from a table called Tbl_Practice_Staff.

The text box is labled TBo_Clinic_Staff_Full_Name. It is bound to an entry in the Tbl_Coumadin_Clinic table called Clinican_Full_Name.

The idea is that when the user selects a clinician's initials in the combo box, the text box is populated with the selected clinician's full name. That will enable including the clinican's full name, rather than just their initials, in the clinic visit report that will be printed at a later time. Both the clinician's initials and the clinician's full name will be saved in the record created for the patient clinic visit in the Tbl_Coumadin_Clinic table. Yes a piece of data will be in two places in the database. Long story . . . start with HIPAA (Health Insurence Portability and Accountability Act) requirements. Ugh!

OK, I have searched the posts on this forum but haven't been able to find what I need. Nor have a couple of Access books been of much help. I know what I have to do. I just don't know exactly how to do it. Can it be done at the combo box - text box level or do I need some code in an After Update function?

Any suggestions, examples, etc. would be greatly appreciated.

Best Regards All,

 

[swtrader]

The initials and the names should be in one table (tblClinicians ?) with a primary key.

There should be a field in the 'main' visit table that 'looks up' the initials and name of the clinician and stores the foreign key (the primary key from tblClinicians).

The combo box on the form is the result of this lookup field. You can have an unbound text box on your form that fills in the name but it's not really needed if you have the intials. If you want it to display, set its rowsource to the name of the combo box. Use a column reference to pull the proper data (the name). The text box rowsource would be something like: cbxClinician.column(2) (the column number is the number of the column that has the name in it in the combobox....column numbers start with zero so the 3rd column is column 2.)

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[dhookom]

Good post swtrader. Deserves a star...

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]

 

[DPROMICS]

The Tbl_Practice_Staff has a primary index and contains the initials and full names of the practice staff.

I still need to get the full name of the clinician in to the clinic visit table.

Yes, database theory says that one stores indexes (foreign) keys in tables. The problem with that is that if an entry is deleted from the practice staff table the index in to the practice staff table stored in the clinic visit table is no longer valid. Yes, it happens . . . staff come and go. Plus with the HIPAA rules and regs actual data not indexes must be available in the tables. This was a big problem we encountered with another company's Pacemaker/ICD program. Yes, it conformed to database theory and normalization rules. But . . . reality doesn't always conform to theory and rules.

Anyway, I still need to get the full name along with the clinician's initials saved in the new record that is saved in the clinic visit table when the user clicks on the command button, "Save Clinic Visit data". Which works quite nicely.

Therefore, while I understand the linking of what was selected in the combo box to the text box, I still need to get the full name associated with the initials saved in the clinic visit record. So how can that be done given that teh text box is unbounded?

Best Regards,

 

[dhookom]

You can use code in the After Update event of the combo box that selects the initials.

Me.TBo_Clinic_Staff_Full_Name = Me.CBo_Clinic_Staff_Initials.Column(1)

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]