Many-to-many problem

[Lisa Reber]

Hi - I've been trying to upgrade / normalize my main
Contacts database in a new db. I have the following tables:

Contacts: PK ContactID / plus contact info fields
VehicleMakes: PK VehID / plus vehicle make (mustang)
ContctVehicles: PK ConVehID, FK ContactID, FK VehID
ContactVehicles additional fields are:
VehMake:
SELECT tblVehicleMakes.VehID, tblVehicleMakes.VehMake FROM
tblVehicleMakes ORDER BY tblVehicleMakes.VehMake;
Year (lookup tblYears)
VehType(lookup value list - car;truck; etc.)

The problem I am having is to show/sync both the VehID and
VehMake. I use the contacts form / ConVehicles subform
to enter info. When I chose the VehicleMake, the correct
list of makes appears with correct vehID, but the vehID
field doesn't fill automatically. I did have difficulty
with error message: "cannot update because a value is
required in tblVehID" (I think - just changed a table).
This went away when I cleared the 'enforce referential
integrity' checkbox on the relationship between
tblContact Vehicles & tblVehicleMakes. That seems like
it NEEDS ref integ.

Hope I've explained this adequately - as I do it, I realize
that I have no idea what's wrong. Thanks a lot in advance.

Lisa

 

[Roger Carlson]

On my website, see sig below, is a small sample database called
"ImplementingM2MRelationship.mdb" which should show you how to implement a
M:M relationship in a form/subform. Generally, I create a main form based
on one "One-side" table and the subform on a JOIN of the other two. Take a
look at the sample and see if you can puzzle it out.