Autopopulate a form

[MDI Anne]

I have a form with several subforms. The main part of the form is company
data. One of the subforms contains volunteers names & addresses. Many of
the volunteers work for several companies.

What I would like to do is when I start typing in the volunteers' name, and
select one, the address would autopopulate into the rest of the form. Right
now, I have the volunteer table include the "company number", so I can sort
by that number also. I don't think it NEEDS to be there, but I'm afraid if I
wipe it out...I'll have NO volunteers for any companies.

So...2 questions:

If I change primary keys, which means I would have to get rid of the
duplicates, how can I keep the data associated?

How do I autopopulate the rest of the data once I have the name chosen?

Thanks in advance...

 

[Kev Cole]

Hi,

I assume the details of your volunteers are stored in a seperate table ?

 

[MDI Anne]

Yes. The name, address, city, state and zip are in a different table. AND I
also have a column in this table to "connect" them to the companies they
volunteer for.

 

[Ken Sheridan]

You have a many-to-many relationship type between Companies and Volunteers,
so you need a third table to model this relationship. This table,
CompanyVolunteers say, will have two foreign key columns CompanyID and
VolunteerID referncing the primary keys of Companies and Volunteers.
Volunteers needs no foreign key referencing Companies. CompanyVolunteers
might also have other columns representing the attributes of each volunteer's
connection with the company in question, e.g. the date they started. The
primary key of this table is a composite one of CompanyID and VolunteerID.

The subform should be based on the CompanyVolunteers table and linked to the
parent form on CompanyID. It will have a combo box bound to the VolumteerID
column set up with properties like this:

Name: cboVolunteer

ControlSource: VolunteerID

RowSource: SELECT VolunteerID, FirstName & " " & LastName, Address,
City, Zip FROM Volunteers ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 5
ColumnWidths 0cm;8cm;0;0;0;

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

To show the other data for the selected volunteer add three unbound text
boxes, txtAddress, txtCity and txtZip to the subform with ControlSource
properties of:

=cboVolunteer.Column(2)
=cboVolunteer.Column(3)
=cboVolunteer.Column(4)

The Column property is zero-based so Column(2) is the third column and so on.

Ad other controls to the subform for any other columns, e.g. DateStarted
form the CompanyVolunteers table. Don't include a control in the subform for
ComapanyID, however. That column will automatically be given the current
CompanyID via the linking mechanism.

Before you can do this of course you have to create the CompanyVolunteers
table and fill it with the necessary rows. Having set up the table you can
do this very simply with an 'append' query:

INSERT INTO CompanyVolunteers (CompanyID, VolunteerID)
SELECT Companies.CompanyID, Volunteers.VolunteerID
FROM Companies INNER JOIN Volunteers
ON Comaonaies.CompanyID = Volunteers.ComanyID;

Once you have the table filled and the subform set up, and are happy that
its showing the right volunteers per company you can delete the CompanyID
column from the Volunteers table.

You can now insert as few or as many volunteers per company as you wish via
the subform, and each volunteer can be assigned to as few or as many
companies as necessary.

Ken Sheridan
Stafford, England

 

[MDI Anne]

Ok...so hang with me while I give this a try...

 

[MDI Anne]

Well...you would think I'd have a "many to many" relationship between the 2
tables...but I don't. It's a one to many...and I have tried to get it many
to many...but can't figure it out. I guess my quest is bigger than I
thought, or that I'm able to comprehend...

Wanna hold my hand thru it??

 

[MDI Anne]

Bound and determined (using the awesome instructions you've given me!!) I
think I'm getting somewhere.

I truly appreciate your help on this!!

 

[MDI Anne]

Excellent!! Thank you for your help and kindness!