Re: SOS! How to add new record to many to many relationship using

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" 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" <Rich1234@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:25ED1C2B-65C2-4191-901E-B231D7E889FF@xxxxxxxxxxxxx
> > > 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)
> >
> >
> >
.

Relevant Pages