Roger Carlson - Many to Many

[Emma]

Hi Roger,

I was wondering if you could help me. I created a database with a many to
many relationship based on the example you gave me. There is an Inbetween
table which connects the two tables Passengers and Trips. Everything is
working as expected except the fact that when I'm in the Passenger form and
enter a new Trip ie New York 2008 it creates a new Trip I want it to reuse
the Trip Names if they've already been entered once. Does this make sense? Is
there some code I could add to check if the Trip Name already exists then
don't add it as a new Trip?

Thanks Emma

 

[Roger Carlson]

I'm sorry, I'm not sure I follow. Are you asking if there's a way to keep a
passenger from being book on the same trip multiple times?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Emma]

No Let's say I have three passengers Ada, Heidi , Susan. Ada goes to New York
2009, Heidi goes to Buffalo 2009 and Susan goes to New York 2009. I enter the
names of the trip in the datasheet with the list of trips for each passenger.

When I go to the Trip screen Instead of getting one listing for New York
with Ada and Susan both in the list. I'm getting two listings for New York.
One for Ada and one for Susan. The only way I've seen around this is to hard
input the data ie copy the passenger list from Excel and paste it in . But
this defeats the purpose of having a database as far as I'm concerned.

 

[Roger Carlson]

I generally ask people to keep questions on the newsgroups unless it is a
question about one of my samples. Since this appears to be about my sample
(or how to implement it), why don't you contact me off-line. You can find
my email address on my website.

 

[Fred]

Couple of side notes:

Make sure the trip record has been truly duplicated as you describe- look at
the table directly.

Certain M to M views (e.g. multitable queries) make it look like there are
duplicated records when in fact there aren't.

 

[Ken Sheridan]

Emma:

I think the apparent problem results from how you are entering the data. To
enable you to assign passengers to a trip, or to assign a trip to a passenger
what you need in essence are two forms each in single form view, each with a
subform in continuous form or datasheet view.

The first form will be based on the Passengers table, so you can either go
to an existing passenger or enter a new passenger, and assign them to one or
more trips in the subform.

The second form will be the converse of this, i.e. based on Trips with a
subform in which you can assign passengers to the trip.

In both cases the subform will be based on your 'Inbetween' table. In the
case of the Passengers form it will be linked to the parent form on
PassengerID, or whatever you've called the key fields on which the two tables
are related. This subform will contain a combo box bound to the TripID (or
whatever its called) field from which you can select a trip. The combo box
will be set up like this:

ControlSource: TripID

RowSource: SELECT TripID, Trip FROM Trips ORDER BY Trip;

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

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.

The Trips form will be linked to the parent form on TripID (or whatever) and
will contain a combo box from bound to the PassengerID field (or whatever its
called) from which you can select a passenger. In this case you'd set up the
combo box like this:

ControlSource: PassengerID

RowSource: SELECT PassengerID, FirstName & " " & LastName FROM
Passengers ORDER BY LastName, FirstName;

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

Ken Sheridan
Stafford, England