Form/Subform

[Guest]

My organization is a school program. My database tracks students and the
school programs they attend.
The three tables I am working with are tblStudents, tblPrograms and
tblTransporters. The primary key in tblStudents is the student ID which I
assign, the primary key in tblPrograms is an autonumber and the primary key
in tblTransporters is the transporter ID which I assign.

I have a form (frmProgram) with a combo box where I choose the student
(based on tblStudent) and a combo box where I choose the program they attend
(based on tblProgram). I now need to include the transporter (based on
tblTransporter). For any one child and their program there may not be any
transporter or there could be several. For example, they could be on a
different route AM & PM, they could change transporters or routes mid-year,
etc. In order to be able to show possibly several transporter changes within
the same program I added a transporter subform (continuous form) within my
frmProgram. The child and master fields are the ProgramID. If there is more
than one transporter entry needed it works great as long as it is a different
transporter each time. However, if I need to use the same transporter more
than once (same transporter AM & PM but different route number for example)
for the same childs program, it won't let me since the subform is based on
tblTransporter. It tells me I am creating a duplicate value in the index,
primary key or relationship.

What am I doing wrong or should I be doing about it differently? If I need
to describe my database in more depth to clarify, let me know.

Thanks,

Sandra

 

[Ken Snell \(MVP\)]

The error message occurs because you're trying to put the same "unique"
value (a TransporterID) into more than one record in your table, which Jet
and ACCESS rightly forbid, based on how you've set up that table.

What are you storing in tblTransporters? You should use this table to store
just the transporter companies or other parent info. What you then need is a
fourth table that would hold the "routes" available for each transporterID.
Then you'd use that Routes table in your form instead of the tblTransporters
table. What you actually want is the individual route/bus/vehicle/time of
day uniqueness, not the transporter uniqueness, right?

 

[Guest]

Ken

That was just the little nudge I needed to make it work...that 4th table. I
knew it was a simple piece I was missing but just couldn't figure it out.

Thanks,

Sandra