Auto fill text boxes from combo box *or* print report of one recor

[cprav]

Hi!

I know similar questions have been asked before but I'm obviously not
proficient enough in Access to make teh answers work for me.

First my database structure. (I think it's a bit messy, but it's the only I
found to make it work so far)
-> I have a WORKSHOPS form/table that lists details about upcoming workshops
we offer.
-> This has a subform/table PARTICIPANTS where I list the people who have
signed up for the workshops

I wanted this to be as automated as possible as we have a lot of 'repeat
customers'. Currently it has one drop down box for the NAME of the
participant. This is based on table PARTICIPANTS CONTACT INFO (it has name,
organization and phone number)

The table are related like this:

PARTICIPANT CONTACT INFO 1-->oo PARTICIPANTS oo<--1 WORKSHOPS

First, I'm fairly certain there is a 'cleaner' way of designing it, but not
sure how.

I would like it if when I choose a name "Joan Smith" eg. from the NAME combo
box in the PARTICIPANTS subform, it automatically fills an 'Organization'
text box with the org. Joan works for and a 'Phone' text box with Joan's
phone number.

This will completely streamline the process (especially when adding a lot of
people over the phone) and help stop errors.

IF THIS IS NOT POSSIBLE:

another possibility would be a report that combines all these tables - so I
have the workshops with the participants names combined with the contact info
from the other table. If this is possible, I would want a button people
could press to print just a report for that record (each workshop is a
seperate record)

Does that all make sense? Obviuosly I'm fairly new at this, so detailed
descriptions would be appreciated!

Thanks in advance!

Cprav

 

[Klatuu]

You can use the Dropdown (combo box) you mention to do this.
Make your combo a multi column combo that returns the 3 fields you want to
use to populate the controls on your form. If you want the user to see only
the name field, set the column widths property of the combo to 0 for the
columns you don't want to see. For example let's say you want the name
column to be 1" and the other 2 field not shown it would be 1;0;0
(assuming the order is name, org, phone)
The bound column in this instance should say 1.

Then in the After Update event of the combo, assign the values of the combo
box columns to the controls on the form. The gotcha here is that in the
bound column property, the first column is column 1; however, in the column
property, the first column is 0. So:

Me.txtName = Me.cboLookup.Column(0)
Me.txtOrg = Me.cboLookup.Column(1)
Me.txtPhone = Me.cboLookup.Column(2)

 

[cprav]

Thank you!

You can use the Dropdown (combo box) you mention to do this.
Make your combo a multi column combo that returns the 3 fields you want to
use to populate the controls on your form. If you want the user to see only
the name field, set the column widths property of the combo to 0 for the
columns you don't want to see. For example let's say you want the name
column to be 1" and the other 2 field not shown it would be 1;0;0
(assuming the order is name, org, phone)
The bound column in this instance should say 1.

Then in the After Update event of the combo, assign the values of the combo
box columns to the controls on the form. The gotcha here is that in the
bound column property, the first column is column 1; however, in the column
property, the first column is 0. So:

Me.txtName = Me.cboLookup.Column(0)
Me.txtOrg = Me.cboLookup.Column(1)
Me.txtPhone = Me.cboLookup.Column(2)