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

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200509/1

P.S. what does your fncDriverWarnings function do.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200509/1

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200509/1

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200509/1

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" wrote:

"Rich1234" wrote in message

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?

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.)

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.

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?

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)

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 he

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" wrote:

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" wrote:

"Rich1234" wrote in message

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?

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.)

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.

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?

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)

"Rich1234" wrote in message

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;

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.

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)

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));


--
Message posted via http://www.accessmonster.com

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" wrote:

"Rich1234" wrote in message

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;

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.

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)

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" wrote:

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" wrote:

"Rich1234" wrote in message

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;

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.

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)