R
Rich1234
hi
I am tring to add a new record in a Many to Many relationship using a form.
My tables are:
Vehicles (VRM is P.K.)
Drivers (DriverID is P.K.)
VehiclesDrivers (VRM and DriverID as P.K.)
Warnings (WarningID is P.K.)
DriversWarnings (DriverID and WarningID as P.K.)
The main form is based on vehicles with a continuous subform showing related
driver info (using VRM to link the information)
I have a separate form which is used for editing driver info and inputting
new drivers. This is accessed from a command button on the main form. I'm
using a new form to input because I need to input driver details on a single
form with associated warnings shown on a continuous subform. I can't show
driver warnings as a continuous form on the original because
"VehiclesDrivers" is a continuous subform already (showing multiple drivers
for each vehicle) and you can only have one continuous subform within a
subform. Phew!
This input/edit form consists of a single form based on the following query,
thus only showing drivers related to the vehicle on the main form from which
it is accessed:
SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, VehiclesDrivers.VRM FROM Drivers INNER JOIN
VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;
I then have a continuous subform to show each of the warrnings attributed to
each driver, based on the DriversWarnings table. Combo boxes in the detail
section of the subform allow new warnings from a predefined list to be
selected for each driver.
When I add a new driver using this form, it appears in the Drivers Table and
any new warnings assigned are appearing in the DriversWarnings Table.
However, the VehiclesDrivers table is not picking up this new driver as being
related to the vehicle in the original table. Instead, the driver is not
related to any vehicles.
How can I get the new driver to be related to the original vehicle? I'm
sure the VRM field must be needed to relate the new driver to original
vehicle. When I include a textbox linked to VRM, it is populated with the
VRM of the vehicle for existing records. When I go to a blank record, it is
empty. I enter driver information and click on the textbox. Even if I
manually type in the VRM, Access says, "...cannot find a record in table
"Drivers" with matching key field(s). I'm confused because when I go to the
drivers table, the new entry IS listed!
There are two many to many relationships on the go here... Vehicles to
Drivers (VehiclesDrivers junction table) and Drivers to Warnings
(DriversWarnings junction table), only one of which is picking up the changes
I need... What am I doing wrong?
TIA,
Rich
I am tring to add a new record in a Many to Many relationship using a form.
My tables are:
Vehicles (VRM is P.K.)
Drivers (DriverID is P.K.)
VehiclesDrivers (VRM and DriverID as P.K.)
Warnings (WarningID is P.K.)
DriversWarnings (DriverID and WarningID as P.K.)
The main form is based on vehicles with a continuous subform showing related
driver info (using VRM to link the information)
I have a separate form which is used for editing driver info and inputting
new drivers. This is accessed from a command button on the main form. I'm
using a new form to input because I need to input driver details on a single
form with associated warnings shown on a continuous subform. I can't show
driver warnings as a continuous form on the original because
"VehiclesDrivers" is a continuous subform already (showing multiple drivers
for each vehicle) and you can only have one continuous subform within a
subform. Phew!
This input/edit form consists of a single form based on the following query,
thus only showing drivers related to the vehicle on the main form from which
it is accessed:
SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, VehiclesDrivers.VRM FROM Drivers INNER JOIN
VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;
I then have a continuous subform to show each of the warrnings attributed to
each driver, based on the DriversWarnings table. Combo boxes in the detail
section of the subform allow new warnings from a predefined list to be
selected for each driver.
When I add a new driver using this form, it appears in the Drivers Table and
any new warnings assigned are appearing in the DriversWarnings Table.
However, the VehiclesDrivers table is not picking up this new driver as being
related to the vehicle in the original table. Instead, the driver is not
related to any vehicles.
How can I get the new driver to be related to the original vehicle? I'm
sure the VRM field must be needed to relate the new driver to original
vehicle. When I include a textbox linked to VRM, it is populated with the
VRM of the vehicle for existing records. When I go to a blank record, it is
empty. I enter driver information and click on the textbox. Even if I
manually type in the VRM, Access says, "...cannot find a record in table
"Drivers" with matching key field(s). I'm confused because when I go to the
drivers table, the new entry IS listed!
There are two many to many relationships on the go here... Vehicles to
Drivers (VehiclesDrivers junction table) and Drivers to Warnings
(DriversWarnings junction table), only one of which is picking up the changes
I need... What am I doing wrong?
TIA,
Rich