Option Box Colum Format (Easy Question)

  • Thread starter Thread starter gwmurray
  • Start date Start date
G

gwmurray

This will be an easy question for anyone out there who has been using
access for more than like a year...

I have a option box... The option box is a table... there are 4 columns
in this table...
Doctors Name | County | Phone | Fax

All someone has to do is click the down arrow key and choose whatever
doctor is assigned to the current client they're working on. here is my
problem... Whenever someone chooses a doctor, all they see is the forst
column "Name" in the drop down list all the columns are displayed, but
when they choose whichever doctor is assigned to the client all they
see is the Doctors Name field.

How do I get the other columns "County" "Phone" and "Fax" to show up in
the combo box after the selected doctor is chosen??

thanks in advanced for your help!
 
Make three more unbound text boxes, use DLookup to get the values from the
table and use a small macro fired by the on change event of the combo box to
requery the other three. Those three unbound text boxes should have their
parameters: Enabled = No, Locked = Yes so user cannot select or cahnge them.

HTH,
Nikos
 
OK. I'll assume the following names, and you'll have so substitute yours
accordingly:

Doctor details table name: tblDoctors

tblDoctors field names: Doctor Name, County, Phone, Fax

Name of Doctor name combo box on your form: cmbName

Open your form in design view, and add three new text boxes next to the
combo. Select each one and open the Properties window (right click on the
text box). On the Other tab, property Name, type in:

txtCounty

txtPhone

tctFax

respectively. Then in the Data tab, property Control Source, type in:

=DLookUp("[County] ","tblDoctors","[Doctor Name] =[cmbName]")

=DLookUp("[Phone] ","tblDoctors","[Doctor Name] =[cmbName]")

=DLookUp("[Fax] ","tblDoctors","[Doctor Name] =[cmbName]")

respectively. On the same tab, set Enabled to No and Locked to Yes for each
text box.

Now select the combo box and on the Other tab of properties put the cursor
in On Change, and click the button with the three dots on the right hand
side. Select Macro builder. This will open up the macro design window. Go to
the first line of column Action in the upper part of the window, and type in
Requery. As you leave this cell, you will see a window with an argument Item
in the lower left hand side. Type in txtCounty. Repeat the process adding
two more Requery actions, with Item arguments txtPhone and txtFax. Save the
macro with a meaningful name, e.g. Refresh Doctor Details.

Back to the form design, you'll notice that the On Change property of the
combo now has the name of the macro you just saved. Save the form and you're
done.

Hope this is all clear. Good luck!

Nikos
 
Gwmurray,

You can't do exactly what you are asking. A combobox can only be bound
to one field, and it can only display the value of one field (not
necessarily the one it's bound to).

There are two (at least) ways to go about this...
1. Make a query which includes the Doctors table as well as the Clients
table, with a join on the Doctors Name field from both tables, and base
your form on this query instead of just on the Clients table. That way,
you can include the County, Phone, and Fax fields from the Doctors table
in the query, and hence on the form, and if you do this, as soon as you
select a doctor in the combobox, the corresponding County, Phone, and
Fax for the selected doctor will automatically be filled in. (Caution:
if you take this approach, you probably should set the Locked property
of the County, Phone, and Fax controls on the form to Yes, as this is
probably not the appropriate place toallow these to be edited.)
2. Put unbound textboxes on the form to display the County, Phone, and
Fax for the selected doctor. In the Control Source of these textboxes,
put the equivalent of...
=[NameOfYourCombobox].[Column](1)
=[NameOfYourCombobox].[Column](2)
and
=[NameOfYourCombobox].[Column](3)
 
Back
Top