SOS! How to add new record to many to many relationship using a fo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to aadd records to a many to many relationship.
I have these tables:
Vehicles (Primary Key VRM)
Drivers (Primary Key DriverID
VehiclesDrivers (Primary keys VRM and DriverID)

I know how to add an entry in the driver table using a form, and how to add
a vehicle to the vehicles table using a form. I have a main form, Vehicles,
with Drivers as a subform. I have set up the subform to show only drivers
who are already related to the vehicle. The problem is how to enter a new
vehicle on the main form then add new drivers associated with it in the
drivers subform.

When I just add a new driver on the subform, access says, "Field Cannot Be
Updated." However, the drivers table does get updated with this new driver,
although the VehiclesDrivers table doesn't.. The query on the subform is
SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex,
Drivers.Address, VehiclesDrivers.VRM FROM Drivers INNER JOIN VehiclesDrivers
ON Drivers.DriverID=VehiclesDrivers.DriverID;

How do I add new records to the drivers table which are also related to the
vehicle displayed in the main form (ie write an entry into both the drivers
table and the VehiclesDrivers table ?)
 
I have also tried an "Add new driver" button on the main form, which opens a
form to add a new driver. Once again, I can add a driver to the drivers
table (without errors) but can't add an entry to the VehiclesDrivers table.
I have tried including the VRM field in the add new driver form but it
appears blank when the form is opened in Add Mode. What am I doing wrong?
Do I have to add to the drivers table first, before adding a new record to
the VehiclesDrivers?

:
g
 
Rich1234 said:
I have also tried an "Add new driver" button on the main form, which opens
a
form to add a new driver. Once again, I can add a driver to the drivers
table (without errors) but can't add an entry to the VehiclesDrivers
table.
I have tried including the VRM field in the add new driver form but it
appears blank when the form is opened in Add Mode. What am I doing wrong?
Do I have to add to the drivers table first, before adding a new record to
the VehiclesDrivers?
Click to expand...

On your add new driver button, make sure you set the OpenForm action to open
the form as a dialog (acDialog). Then, in the form's unload event, cancel
the unload if it was called by your button (look at OpenArgs), and instead
make the form invisible. Now, you have access to the new driver in the
other form to add to your calling form. After you add the driver, close the
popup form.

HTH;

Amy
 
Hi Amy

Can I tell you the way I have got this set up. Maybe I haven't been too
clear.
There is an "edit drivers" button on the detail section of the continuous
subform which allows the user to edit each existing driver for the vehicle.
This button opens the "drivers" form to show only that particular driver,
whose details can be edited successfully. On this drivers form is the
warnings subform, using the combo box to select new warnings from the
DriversWarnings table. All of this works fine.

The problem is in writing to the VehiclesDrivers table. I still can't work
this out. If I add an "add new driver" button, where should it go - on the
main (Vehicles) form or on the drivers subform within the main page? Does it
matter? I need the new driver entry to be related to VRM (so it can save to
VehiclesDrivers table), that is why I ask this. I am trying to open the same
"drivers" form that I use to edit existing drivers (with DriversWarnings
continuous subform) in "add new record" mode so it doesn't show any existing
records (How do I do this? I've tried setting it to
DoCmd.OpenForm stDocName, , acNewRec
but it is still showing all the drivers, and is not showing the blank form I
would like.)

I have put the add new drivers button in the header of the subform.
I've also tried opening the drivers form as dialog by putting acDialog in as
follows:
DoCmd.OpenForm stDocName, , acDialog, stLinkCriteria
Is this right? This doesn't seem to make any difference. The warnings
subform is always on the drivers main form, in case I wasn't clear before.

In the form's unload event, how do I cancel the unload if it was called by
the button by looking at OpenArgs? - and how do I make it invisible? (Which
form's unload event - the drivers mainform or the DriversWarnings subform?) I
don't understand!

Or do I have to use a different form for "Add Drivers?" (ie not the same
form I'm using for editing existing drivers?) And is it this form's unload
event I need to look at? And how does I then add driver warnings for this
new driver? And how is this driver assigned to the VehiclesDrivers table?

What am I doing wrong? I can't believe this is giving me so much trouble!
There must be a way to do this. I've got a dealine to get this database
ready for use by Monday and I'm getting worried! Please help if you can
 
Rich1234 said:
Hi

I am trying to aadd records to a many to many relationship.
I have these tables:
Vehicles (Primary Key VRM)
Drivers (Primary Key DriverID
VehiclesDrivers (Primary keys VRM and DriverID)

I know how to add an entry in the driver table using a form, and how
to add a vehicle to the vehicles table using a form. I have a main
form, Vehicles, with Drivers as a subform. I have set up the subform
to show only drivers who are already related to the vehicle. The
problem is how to enter a new vehicle on the main form then add new
drivers associated with it in the drivers subform.

When I just add a new driver on the subform, access says, "Field
Cannot Be Updated." However, the drivers table does get updated with
this new driver, although the VehiclesDrivers table doesn't.. The
query on the subform is SELECT Drivers.Surname, Drivers.[First
Names], Drivers.DOB, Drivers.Sex, Drivers.Address,
VehiclesDrivers.VRM FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

How do I add new records to the drivers table which are also related
to the vehicle displayed in the main form (ie write an entry into
both the drivers table and the VehiclesDrivers table ?)
Click to expand...

Rich, if I understand you correctly, you want to use the subform both to
choose existing Driver records for the subform, and also to add new
drivers who don't yet exist in the Drivers table, both entering them in
the Drivers table and associating them with this vehicle in the
VehiclesDrivers table at the same time. So long as you include the
necessary fields in the subform's recordsource query, I don't see any
fundamental barrier to your doing this. However, your query as quoted
above doesn't include the necessary fields.

You need to include VehiclesDrivers.DriverID in the query. Try this; it
works for me:

SELECT
VehiclesDrivers.VRM,
VehiclesDrivers.DriverID,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex,
Drivers.Address
FROM
Drivers
INNER JOIN
VehiclesDrivers
ON Drivers.DriverID =VehiclesDrivers.DriverID;

I'd suggest using a combo box on the subform, bound to the DriverID
field, with a rowsource that lets you select existing drivers. But if
you want to add a new driver that isn't in the list, you should be able
to just type the name and other data into the text boxes bound to those
fields.
 
If I'm reading correctly, heres my take on the issue.
drop the inner join on the your subs record source and just plain select the
fields you want displayed in the subform. make sure one of those fields is
vehicle
set the subs LinkChildField and LinkMasterField to vehicle.
set your subs allow additions property to yes

this way when you select a vehicle in your parent form, your sub will
automatically only show the drivers for that vehicle. when you enter a new
driver in the sub, it will automatically get associated with the vehicle that
is displayed in the parent form. when you enter a new record on the parent
form the sub will automatically go blank (cuz there are currently no drivers
associated with the new vehicle you are about to enter in the new record) and
when you start to add drivers to the new vehicle you just added, they too,
will automatically get associated with the new vehicle you just entered. the
only button you'd need is on the parent which would be something like 'enter
new vehicle' and its OnClick code would be: docmd.GoToRecord ,,acNewRec
(also make sure your parent forms all additions property is set to yes)
 
Thanks Guys.
This works... but I can't get the combo box to work. I want it to be able
to be used as a "search" facility so that the user can see if the driver they
are about to input already exists in the database (ie already assigned to
another vehicle.) At the moment I can click on a driver in the list, but the
text at the bottom of the Access screen says, "Control can't be edited.. it's
bound to the unknown field VehiclesDrivers.DriverID. Why does it say this,
when the VehiclesDrivers.DriverID field is included in the recordsource for
the subform, and is the ControlSource for the combo?

The row source for the combo is the Drivers table.
The control source for the combo is VehiclesDrivers.DriverID
The record source for the subform is:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings, Drivers.DriverID,
VehiclesDrivers.VRM, VehiclesDrivers.DriverID FROM Vehicles INNER JOIN
(Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID) ON
(Vehicles.VRM=VehiclesDrivers.VRM) AND (Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM);

(I have no idea why (Vehicles.VRM=VehiclesDrivers.VRM) is included 3 times
at the end of the query.. Access puts this in automatically after I've used
the SQL statment Query Builder.)

Ideally I'd like the entry, once clicked, to automatically add this driver
to the VehiclesDrivers table for this particular vehicle, and populate the
fields in the subform below.

The drivers are listed OK (First Name, Surname and DOB appear in the combo)
.... but that's as far as I can get.

If you can enlighten me, once more I'll be very grateful. Is this the best
way to do a driver search to see if the driver is already in the database,
and then assign to this vehicle?

Thank you.
Dirk Goldgar said:
Rich1234 said:
Hi

I am trying to aadd records to a many to many relationship.
I have these tables:
Vehicles (Primary Key VRM)
Drivers (Primary Key DriverID
VehiclesDrivers (Primary keys VRM and DriverID)

I know how to add an entry in the driver table using a form, and how
to add a vehicle to the vehicles table using a form. I have a main
form, Vehicles, with Drivers as a subform. I have set up the subform
to show only drivers who are already related to the vehicle. The
problem is how to enter a new vehicle on the main form then add new
drivers associated with it in the drivers subform.

When I just add a new driver on the subform, access says, "Field
Cannot Be Updated." However, the drivers table does get updated with
this new driver, although the VehiclesDrivers table doesn't.. The
query on the subform is SELECT Drivers.Surname, Drivers.[First
Names], Drivers.DOB, Drivers.Sex, Drivers.Address,
VehiclesDrivers.VRM FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

How do I add new records to the drivers table which are also related
to the vehicle displayed in the main form (ie write an entry into
both the drivers table and the VehiclesDrivers table ?)
Click to expand...

Rich, if I understand you correctly, you want to use the subform both to
choose existing Driver records for the subform, and also to add new
drivers who don't yet exist in the Drivers table, both entering them in
the Drivers table and associating them with this vehicle in the
VehiclesDrivers table at the same time. So long as you include the
necessary fields in the subform's recordsource query, I don't see any
fundamental barrier to your doing this. However, your query as quoted
above doesn't include the necessary fields.

You need to include VehiclesDrivers.DriverID in the query. Try this; it
works for me:

SELECT
VehiclesDrivers.VRM,
VehiclesDrivers.DriverID,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex,
Drivers.Address
FROM
Drivers
INNER JOIN
VehiclesDrivers
ON Drivers.DriverID =VehiclesDrivers.DriverID;

I'd suggest using a combo box on the subform, bound to the DriverID
field, with a rowsource that lets you select existing drivers. But if
you want to add a new driver that isn't in the list, you should be able
to just type the name and other data into the text boxes bound to those
fields.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Rich1234 said:
Hi Amy

Can I tell you the way I have got this set up. Maybe I haven't been too
clear.
There is an "edit drivers" button on the detail section of the continuous
subform which allows the user to edit each existing driver for the
vehicle.
This button opens the "drivers" form to show only that particular driver,
whose details can be edited successfully. On this drivers form is the
warnings subform, using the combo box to select new warnings from the
DriversWarnings table. All of this works fine.

The problem is in writing to the VehiclesDrivers table. I still can't
work
this out. If I add an "add new driver" button, where should it go - on
the
main (Vehicles) form or on the drivers subform within the main page? Does
it
matter? I need the new driver entry to be related to VRM (so it can save
to
VehiclesDrivers table), that is why I ask this. I am trying to open the
same
"drivers" form that I use to edit existing drivers (with DriversWarnings
continuous subform) in "add new record" mode so it doesn't show any
existing
records (How do I do this? I've tried setting it to
DoCmd.OpenForm stDocName, , acNewRec
but it is still showing all the drivers, and is not showing the blank form
I
would like.)
Click to expand...

OK, you have vehicles listed in a continuous subform, with an edit drivers
button for each vehicle. What's the main form, and how is it related?

I would think that you could put the add new driver button next to the edit
drivers button, whereever makes sense. Its physical location should not
matter.
I have put the add new drivers button in the header of the subform.
I've also tried opening the drivers form as dialog by putting acDialog in
as
follows:
DoCmd.OpenForm stDocName, , acDialog, stLinkCriteria
Is this right? This doesn't seem to make any difference. The warnings
subform is always on the drivers main form, in case I wasn't clear before.
Click to expand...

If you open it as a dialog, that only means that execution of everything
else in access will pause until the form is hidden or closed. Is that what
you meantt? I don't think you actually have the acDialog constant in the
right place for it to work as you think it will. acDialog should actually
be the 6th parameter, not the third.
In the form's unload event, how do I cancel the unload if it was called by
the button by looking at OpenArgs? - and how do I make it invisible?
(Which
form's unload event - the drivers mainform or the DriversWarnings
subform?) I
don't understand!
Click to expand...

You'd have to actually USE OpenArgs, which would be the 7th parameter for
the OpenForm method. That would put it right after acDialog, if acDialog
were moved so as to work as expected.

To look at OpenArgs on close, you'd declare a module wide variable in the
declarations section (Private IsCanceled As Boolean). Then, in the load
event, you'd set IsCanceled to true if the form was not called with
arguments and IsCanceled if it was called with arguments.

In the form's unload code, you'd use something like

Private Sub Form_Unload(Cancel As Integer)
If Not IsCanceled Then
Cancel = True
IsCanceled = True
End If
Me.Visible = False
End Sub

which would allow you to cancel the close of the form and simply hide it
until the main form can grab the value and finish the close.

Or do I have to use a different form for "Add Drivers?" (ie not the same
form I'm using for editing existing drivers?) And is it this form's
unload
event I need to look at? And how does I then add driver warnings for this
new driver? And how is this driver assigned to the VehiclesDrivers table?
Click to expand...

I wouldn't think so. My standard code for this is

Private Sub Form_Open(Cancel As Integer)
Cancel = False
If Me.OpenArgs = "New" Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
isCanceled = False
Else
isCanceled = True
End If
End Sub

This takes you to a new, blank record if the form is called with an argument
of "New." It also sets the isCanceled variable as discussed above.

HTH;

Amy
 
Rich1234 said:
Thanks Guys.
This works... but I can't get the combo box to work. I want it to be
able to be used as a "search" facility so that the user can see if
the driver they are about to input already exists in the database (ie
already assigned to another vehicle.) At the moment I can click on a
driver in the list, but the text at the bottom of the Access screen
says, "Control can't be edited.. it's bound to the unknown field
VehiclesDrivers.DriverID. Why does it say this, when the
VehiclesDrivers.DriverID field is included in the recordsource for
the subform, and is the ControlSource for the combo?
Click to expand...

Very odd. Something's wrong.
The row source for the combo is the Drivers table.
The control source for the combo is VehiclesDrivers.DriverID
The record source for the subform is:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings,
Drivers.DriverID, VehiclesDrivers.VRM, VehiclesDrivers.DriverID FROM
Vehicles INNER JOIN (Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID) ON
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM);

(I have no idea why (Vehicles.VRM=VehiclesDrivers.VRM) is included 3
times at the end of the query.. Access puts this in automatically
after I've used the SQL statment Query Builder.)
Click to expand...

Something's very odd indeed. Open the Relationships window and look at
the relationships you've defined between these tables. Be sure to click
the Show All button to see all tables and relationships. Do you have
multiple copies of the same relationship defined betwen Vehicles and
VehiclesDrivers. Delete duplicates.

Edit the SQL of the query to remove the duplicate join expressions.
Ideally I'd like the entry, once clicked, to automatically add this
driver to the VehiclesDrivers table for this particular vehicle, and
populate the fields in the subform below.
Click to expand...

That should work with the SQL that I gave you. It works for me.
The drivers are listed OK (First Name, Surname and DOB appear in the
combo) ... but that's as far as I can get.

If you can enlighten me, once more I'll be very grateful. Is this
the best way to do a driver search to see if the driver is already in
the database, and then assign to this vehicle?
Click to expand...

I don't know if it's the best way, but it's a fine way. If you also use
the subform to add new drivers (which works for me, but not yet for you)
you'll probably want to requery the combo box in your subform's
AfterUpdate event, to make sure that newly added drivers appear in it
right away.
 
Sorry for being a bit slow but I think I need a recap in order to better
understand your situation and help you find a solution. tell me if I've
missed anything here
your main forms' record source is 'vehicles' which shows all info about a
selected vehicle. you have a subform that you want to show all the drivers
about the selected vehicle on the main form. you want to both, be able to
enter a new vehicle on the main form and then list drivers for it in the
subform, and be able to list new drivers for a vehicle that is already in the
vehicles table by just entering new driver info into the subform when the
appropriate vehicle is showing on the main form. your vehicles table is all
about the specifics of each vehicle and the drivers table is all about the
specifics of the drivers while the vehiclesdrivers table is a bridging table
that lists all the drivers associated with all the vehicles. when you enter a
new driver in the subform you want the info of that driver to get saved to
both the vehiclesdrivers table and the drivers table at the same time? and
where is this trouble-making combobox of drivers located, on the parent form
or on the subform? I got lost as to what your goal is and exactly how your
tables and forms are built.
 
I just created a dummy DB with 3 tables. 1 testV that has dummy info about
vehicles, 1 testD that has dummy info about drivers, and 1 testDV that lists
what drivers drive what vehicles. 1 form that lists all the info about a
vehicle, 1 subform that lists all the drivers for the displayed vehicle and
the detailed info about that driver. in the main form I can enter a new
vehicle as well as look at existing vehicles. in the sub I can add existing
drivers to an existing vehicle or a new vehicle or I can add a new driver to
an existing vehicle or a new vehicle. when I add an existing driver to a
vehicle a new entry is entered into my testDV table, when I enter a new
driver into the sub that new driver is added to the testDV table as
registered to the vehicle that was displayed when I entered the new driver as
well as being entered into the drivers table at the same time. I choose an
existing driver via a combobox of existing driver ID's. when I choose an
existing driver the rest of the fields auto-populate with that drivers
details. when I enter a new driver I just type that driver a new driver ID
into the combobox. If this is what your looking for then I'll tell you how
its done. (considerably simple) if it's not what your after then let me know.
 
Allow me to correct what just said in my last post as I didnt explain it
quite right.
In my test subform, if you want to select an existing driver you chose them
from a drop down that auto populates the rest of the driver info, if you
enter a new driver you just enter their new driverID into a text field that
is bound to the driverID and that automatically gets added to the drop down
list. all of this with done with done with one line of VBA that you dont even
have to open the VB editor to do and all the SQL has just one simple inner
join. the rest of it is straight forward SQL selects
 
Hi Dirk

I don't understand why the last statement in the record source for the
subform was included 3 times. I have deleted it and it didn't reappear.
Perhaps I made an error inputting it. That aside, the relationships window
is behvaing oddly.. I was going to submit a post on this one. Not all
relationships show up in the window, even when I click "Show All." The
relationship line between Vehicles and VehiclesDrivers disappears next time
the window is opened, even if you've just saved it before closing the window!
Is this is a serious bug??

Back to the point in hand...
I have put your SQL in the record source for the drivers subform (and have
included the fnc below:)
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID, Drivers.DriverID,
Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings FROM Drivers INNER
JOIN VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;

The combo control source is VehiclesDrivers.DriverID.
Row source: SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First
Names], Drivers.DOB FROM Drivers ORDER BY [Surname], [First Names], [DOB];

I still get the message, "Control can't be edited; it's bound to the unknown
field VehiclesDrivers.DriverID." This is foxing me as
VehiclesDrivers.DriverID is in the record source for the form and shows up in
the field list in design view.

Dirk Goldgar said:
Rich1234 said:
Thanks Guys.
This works... but I can't get the combo box to work. I want it to be
able to be used as a "search" facility so that the user can see if
the driver they are about to input already exists in the database (ie
already assigned to another vehicle.) At the moment I can click on a
driver in the list, but the text at the bottom of the Access screen
says, "Control can't be edited.. it's bound to the unknown field
VehiclesDrivers.DriverID. Why does it say this, when the
VehiclesDrivers.DriverID field is included in the recordsource for
the subform, and is the ControlSource for the combo?
Click to expand...

Very odd. Something's wrong.
The row source for the combo is the Drivers table.
The control source for the combo is VehiclesDrivers.DriverID
The record source for the subform is:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings,
Drivers.DriverID, VehiclesDrivers.VRM, VehiclesDrivers.DriverID FROM
Vehicles INNER JOIN (Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID) ON
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM);

(I have no idea why (Vehicles.VRM=VehiclesDrivers.VRM) is included 3
times at the end of the query.. Access puts this in automatically
after I've used the SQL statment Query Builder.)
Click to expand...

Something's very odd indeed. Open the Relationships window and look at
the relationships you've defined between these tables. Be sure to click
the Show All button to see all tables and relationships. Do you have
multiple copies of the same relationship defined betwen Vehicles and
VehiclesDrivers. Delete duplicates.

Edit the SQL of the query to remove the duplicate join expressions.
Ideally I'd like the entry, once clicked, to automatically add this
driver to the VehiclesDrivers table for this particular vehicle, and
populate the fields in the subform below.
Click to expand...

That should work with the SQL that I gave you. It works for me.
The drivers are listed OK (First Name, Surname and DOB appear in the
combo) ... but that's as far as I can get.

If you can enlighten me, once more I'll be very grateful. Is this
the best way to do a driver search to see if the driver is already in
the database, and then assign to this vehicle?
Click to expand...

I don't know if it's the best way, but it's a fine way. If you also use
the subform to add new drivers (which works for me, but not yet for you)
you'll probably want to requery the combo box in your subform's
AfterUpdate event, to make sure that newly added drivers appear in it
right away.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Hi Kelly, Dirk and Amy! My last post responded it Dirk's post and I didn't
mean to exclude you!!
Thanks for your posts. I will recap and clarify things here:

Tables: Vehicles (PK VRM)
Drivers (PK DriverID)
VehiclesDrivers (PK VRM and DriverID)
Warnings (PK WarningID)
DriverWarnings (PK DriverID and WarningID)

Main form is Vehicles (based on Vehicles table).
Continuous Subform is Drivers, using this SQL to show driver info related to
vehicle:
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID, Drivers.DriverID,
Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings FROM Drivers INNER
JOIN VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;

I need to display onscreen the drivers in the continuous subform and their
warnings... Because there are two many to many relationships involved in
showing a vehicle and driver(s) who have warning(s) assigned to them, I am
usng the fncDriversWarnings to show the warnings for each driver in another
textbox next to each driver in the cont subform. That works fine (thanks
Dirk!)

I can now input new drivers to the subform direct and they are added to the
drivers and VehiclesDrivers tables successfully. I can add warnings from
opening an additional form.. this works fine.

The problem is in making sure users don't input a driver to a vehicle who
already exists in the database. I am using a combo as described in my last
post to show all drivers in the database and it is not working for assigning
the selected driver to the vehicle.
If you can help (or is there another way to do this?), I will be a happy man!

Thanks
Rich


Rich1234 said:
Hi Dirk

I don't understand why the last statement in the record source for the
subform was included 3 times. I have deleted it and it didn't reappear.
Perhaps I made an error inputting it. That aside, the relationships window
is behvaing oddly.. I was going to submit a post on this one. Not all
relationships show up in the window, even when I click "Show All." The
relationship line between Vehicles and VehiclesDrivers disappears next time
the window is opened, even if you've just saved it before closing the window!
Is this is a serious bug??

Back to the point in hand...
I have put your SQL in the record source for the drivers subform (and have
included the fnc below:)
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID, Drivers.DriverID,
Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings FROM Drivers INNER
JOIN VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;

The combo control source is VehiclesDrivers.DriverID.
Row source: SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First
Names], Drivers.DOB FROM Drivers ORDER BY [Surname], [First Names], [DOB];

I still get the message, "Control can't be edited; it's bound to the unknown
field VehiclesDrivers.DriverID." This is foxing me as
VehiclesDrivers.DriverID is in the record source for the form and shows up in
the field list in design view.

Dirk Goldgar said:
Rich1234 said:
Thanks Guys.
This works... but I can't get the combo box to work. I want it to be
able to be used as a "search" facility so that the user can see if
the driver they are about to input already exists in the database (ie
already assigned to another vehicle.) At the moment I can click on a
driver in the list, but the text at the bottom of the Access screen
says, "Control can't be edited.. it's bound to the unknown field
VehiclesDrivers.DriverID. Why does it say this, when the
VehiclesDrivers.DriverID field is included in the recordsource for
the subform, and is the ControlSource for the combo?
Click to expand...

Very odd. Something's wrong.
The row source for the combo is the Drivers table.
The control source for the combo is VehiclesDrivers.DriverID
The record source for the subform is:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings,
Drivers.DriverID, VehiclesDrivers.VRM, VehiclesDrivers.DriverID FROM
Vehicles INNER JOIN (Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID) ON
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM) AND
(Vehicles.VRM=VehiclesDrivers.VRM);

(I have no idea why (Vehicles.VRM=VehiclesDrivers.VRM) is included 3
times at the end of the query.. Access puts this in automatically
after I've used the SQL statment Query Builder.)
Click to expand...

Something's very odd indeed. Open the Relationships window and look at
the relationships you've defined between these tables. Be sure to click
the Show All button to see all tables and relationships. Do you have
multiple copies of the same relationship defined betwen Vehicles and
VehiclesDrivers. Delete duplicates.

Edit the SQL of the query to remove the duplicate join expressions.
Ideally I'd like the entry, once clicked, to automatically add this
driver to the VehiclesDrivers table for this particular vehicle, and
populate the fields in the subform below.
Click to expand...

That should work with the SQL that I gave you. It works for me.
The drivers are listed OK (First Name, Surname and DOB appear in the
combo) ... but that's as far as I can get.

If you can enlighten me, once more I'll be very grateful. Is this
the best way to do a driver search to see if the driver is already in
the database, and then assign to this vehicle?
Click to expand...

I don't know if it's the best way, but it's a fine way. If you also use
the subform to add new drivers (which works for me, but not yet for you)
you'll probably want to requery the combo box in your subform's
AfterUpdate event, to make sure that newly added drivers appear in it
right away.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Rich1234 said:
Hi Dirk

I don't understand why the last statement in the record source for the
subform was included 3 times. I have deleted it and it didn't
reappear. Perhaps I made an error inputting it. That aside, the
relationships window is behvaing oddly.. I was going to submit a
post on this one. Not all relationships show up in the window, even
when I click "Show All." The relationship line between Vehicles and
VehiclesDrivers disappears next time the window is opened, even if
you've just saved it before closing the window! Is this is a serious
bug??

Back to the point in hand...
I have put your SQL in the record source for the drivers subform (and
have included the fnc below:)
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID,
Drivers.DriverID, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID)
AS Warnings FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;
Click to expand...

Do not include Drivers.DriverID in the list of selected fields.
The combo control source is VehiclesDrivers.DriverID.
Row source: SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First
Names], Drivers.DOB FROM Drivers ORDER BY [Surname], [First Names],
[DOB];

I still get the message, "Control can't be edited; it's bound to the
unknown field VehiclesDrivers.DriverID." This is foxing me as
VehiclesDrivers.DriverID is in the record source for the form and
shows up in the field list in design view.
Click to expand...

Tell me if removing Drivers.DriverID from the field list of the
subform's recordsource query solves the problem.
 
So everything is working fine now except when entering a new driver a check
needs made to see if the driver being added to a vehicle isnt already
assigned to another vehicle. you might need to look to some VBA for this one.
like maybe on the AfterUpdate of your subs driverID field you could put
something like:
If Dcount("driverID","VehiclesDrivers","driverID='" & me!driverID & "'")>0
then
msgbox "this driver is already assigned to a vehicle."
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
end if

or something similar that checks and stops the adding of a new driver that is
already assigned.

or if you just want a combobox to only show drivers that are currently
unassigned to any vehicle then you might put some SQL on the combobox's row
source like this:
SELECT drivers.driverID FROM drivers LEFT JOIN vehiclesdrivers ON drivers.
driverID = vehiclesdrivers.drvierID WHERE (((vehiclesdrivers.drvierID) Is
Null));
 
Thanks Dirk and Kelly for your posts.
Just to confirm, I am looking for a way to see if a driver already exists as
being assigned to another vehicle, so that the user will not input the same
driver twice but instead just select the driver and the driver then will be
assigned to multiple vehicles (and will populate the driver info fields in
the sibform for this particular vehicle.)

I have tried Dirk's idea of removing Drivers.DriverID. Here are the results

Yes it does solve this problem in the short term.. but I now cannot save the
form or database without the whole of Access crashing! Unbelievable. This
has never happened consistently before and has just crashed the last 4 times
I have tried this. Some of the VB is missing when I reopen- I have not
checked everything, but the command buttons on the main form have lost all
their VB and now do nothing! when I re-open Access so I have to go back to
the backup version I saved at lunchtime - fortunate I made a backup. (I am
resisting the very strong urge to say harsh words about Microsoft!)

The combo is in the header section of the subform. I am a bit confused as
to why it does work anyway because in design view, the combo says for its
recordsource VehiclesDrivers.DriverID, "Error- No such field in the field
list." And there isn't! Yet the recordsource query for the subform includes
the VehiclesDrivers.DriverID field! Despite this, in form view it is
working! Am I missing something here, or is this very strange indeed?

Here's the recordsource for the subform:

SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID, Drivers.Surname,
Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings FROM Drivers INNER
JOIN VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;

Anyway, it works short term (until I need to save, when Access crashes,
negating any benefit and creating more work.) Drivers.DriverID was in the
record source for the subform because I had been using Drivers.DriverID on an
"edit driver profile" button on each record, to open the corresponding record
in a "Drivers" form where users can edit warnings for the driver. As
Drivers.DriverID had been removed from the recordsource, I have instead been
using VehiclesDrivers.DriverID to find the correct driver, which works.

Has anyone else experienced anything similar???? Does this all stem from
the peculiar issue in the relationships window, where Vehicles is still not
showing as being in a relationship with VehiclesDrivers, even after I create
the relationship, close the window and say yes to saving the changes in the
rel. window? I still haven't got to the bottom of this issue which worries
me, Microsoft!

just for the record, the "Add Driver" button sets
Me.AllowAdditions=true then opens a new record using the standard code.. and
it works fine. All of it does til I save!

Please please help if you can! I have spent all afternoon on this.



Dirk Goldgar said:
Rich1234 said:
Hi Dirk

I don't understand why the last statement in the record source for the
subform was included 3 times. I have deleted it and it didn't
reappear. Perhaps I made an error inputting it. That aside, the
relationships window is behvaing oddly.. I was going to submit a
post on this one. Not all relationships show up in the window, even
when I click "Show All." The relationship line between Vehicles and
VehiclesDrivers disappears next time the window is opened, even if
you've just saved it before closing the window! Is this is a serious
bug??

Back to the point in hand...
I have put your SQL in the record source for the drivers subform (and
have included the fnc below:)
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID,
Drivers.DriverID, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID)
AS Warnings FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;
Click to expand...

Do not include Drivers.DriverID in the list of selected fields.
The combo control source is VehiclesDrivers.DriverID.
Row source: SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First
Names], Drivers.DOB FROM Drivers ORDER BY [Surname], [First Names],
[DOB];

I still get the message, "Control can't be edited; it's bound to the
unknown field VehiclesDrivers.DriverID." This is foxing me as
VehiclesDrivers.DriverID is in the record source for the form and
shows up in the field list in design view.
Click to expand...

Tell me if removing Drivers.DriverID from the field list of the
subform's recordsource query solves the problem.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I have done a step by step edit then save then edit then save to locate
exactly which change leads Access to crash upon saving. It happens as soon
as Drivers.DriverID is removed from the subform record source.

Rich1234 said:
Thanks Dirk and Kelly for your posts.
Just to confirm, I am looking for a way to see if a driver already exists as
being assigned to another vehicle, so that the user will not input the same
driver twice but instead just select the driver and the driver then will be
assigned to multiple vehicles (and will populate the driver info fields in
the sibform for this particular vehicle.)

I have tried Dirk's idea of removing Drivers.DriverID. Here are the results

Yes it does solve this problem in the short term.. but I now cannot save the
form or database without the whole of Access crashing! Unbelievable. This
has never happened consistently before and has just crashed the last 4 times
I have tried this. Some of the VB is missing when I reopen- I have not
checked everything, but the command buttons on the main form have lost all
their VB and now do nothing! when I re-open Access so I have to go back to
the backup version I saved at lunchtime - fortunate I made a backup. (I am
resisting the very strong urge to say harsh words about Microsoft!)

The combo is in the header section of the subform. I am a bit confused as
to why it does work anyway because in design view, the combo says for its
recordsource VehiclesDrivers.DriverID, "Error- No such field in the field
list." And there isn't! Yet the recordsource query for the subform includes
the VehiclesDrivers.DriverID field! Despite this, in form view it is
working! Am I missing something here, or is this very strange indeed?

Here's the recordsource for the subform:

SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID, Drivers.Surname,
Drivers.[First Names], Drivers.DOB, Drivers.Sex,
fncDriverWarnings(VehiclesDrivers.DriverID) AS Warnings FROM Drivers INNER
JOIN VehiclesDrivers ON Drivers.DriverID=VehiclesDrivers.DriverID;

Anyway, it works short term (until I need to save, when Access crashes,
negating any benefit and creating more work.) Drivers.DriverID was in the
record source for the subform because I had been using Drivers.DriverID on an
"edit driver profile" button on each record, to open the corresponding record
in a "Drivers" form where users can edit warnings for the driver. As
Drivers.DriverID had been removed from the recordsource, I have instead been
using VehiclesDrivers.DriverID to find the correct driver, which works.

Has anyone else experienced anything similar???? Does this all stem from
the peculiar issue in the relationships window, where Vehicles is still not
showing as being in a relationship with VehiclesDrivers, even after I create
the relationship, close the window and say yes to saving the changes in the
rel. window? I still haven't got to the bottom of this issue which worries
me, Microsoft!

just for the record, the "Add Driver" button sets
Me.AllowAdditions=true then opens a new record using the standard code.. and
it works fine. All of it does til I save!

Please please help if you can! I have spent all afternoon on this.



Dirk Goldgar said:
Rich1234 said:
Hi Dirk

I don't understand why the last statement in the record source for the
subform was included 3 times. I have deleted it and it didn't
reappear. Perhaps I made an error inputting it. That aside, the
relationships window is behvaing oddly.. I was going to submit a
post on this one. Not all relationships show up in the window, even
when I click "Show All." The relationship line between Vehicles and
VehiclesDrivers disappears next time the window is opened, even if
you've just saved it before closing the window! Is this is a serious
bug??

Back to the point in hand...
I have put your SQL in the record source for the drivers subform (and
have included the fnc below:)
SELECT VehiclesDrivers.VRM, VehiclesDrivers.DriverID,
Drivers.DriverID, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverWarnings(VehiclesDrivers.DriverID)
AS Warnings FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;
Click to expand...

Do not include Drivers.DriverID in the list of selected fields.
The combo control source is VehiclesDrivers.DriverID.
Row source: SELECT Drivers.DriverID, Drivers.Surname, Drivers.[First
Names], Drivers.DOB FROM Drivers ORDER BY [Surname], [First Names],
[DOB];

I still get the message, "Control can't be edited; it's bound to the
unknown field VehiclesDrivers.DriverID." This is foxing me as
VehiclesDrivers.DriverID is in the record source for the form and
shows up in the field list in design view.
Click to expand...

Tell me if removing Drivers.DriverID from the field list of the
subform's recordsource query solves the problem.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Back
Top