junction table

[b_a_redman]

Ok, i have three tables, one called Ambulance Trusts, Service and ServiceAT.
The junction table being ServiceAT.

Ambulance Trusts (tbl) Services(tbl)
ServiceAT(tbl)
AmbulanceID ServiceID
Ambulance ID

Service ID

Each field on the tables are primary keys. What the purpose of these tables
is, so that one ambulance can be providing many services, and one serice can
be provided by many ambulance trusts. Im having difficulty on the data entry
side of this. For example, i have created a form based upon the Ambulance
Trusts table, i want to be able to select several services, and all the
relavent data filled in on the conjunction table. I dont no how to select
this data. I dont want the user to have to enter in the ID of anything. What
i want is a combo box or list box whereby the user selects all the services
that the ambulance provides, and it populate all the realvent fields across
the database. When i come back to view that record at a later date, i want it
to show all the services that i selected before, NOT the ID.

 

[Larry Daugherty]

ServiceAT(tbl) should have its own primary key (autonumber works just
fine) The primary keys of the other two tables go into fields in
ServiceAT with datatypes of Long Integer as Foreign Keys.

HTH

 

[Graham Mandeno]

The "traditional" way to do this is with a subform in continuous view.

You have a main form bound to your Ambulances table and a subform bound to
the junction table. They are linked by AmbulanceID.

On the subform, you have a combo box bound to ServiceID, with a RowSource
like this:
Select ServiceID, ServiceName from Services order by ServiceName;

For the combo box, set ColumnCount to 2 and ColumnWidths to 0 (this hides
the first column containing the ID).

Now, to add a service to the list for a particular ambulance record, you
just add a new record to your subform and select the service from the combo.
To remove a service from the list, you just delete the record.

There are other ways to set up a more "user-friendly" interface for this.
One popular way uses a multi-select listbox and my own favourite uses a
subform with checkboxes. Both these methods require some moderately complex
coding, so post back here if you are interested.