Help! Using a Combo Box to add records to many to many relationshi

[Guest]

Hi

I am trying to learn how to add records in a many to many relationship. I
have spent many hours trying! Here is the setup:
Vehicles table, primary key: VRM (unique identifier)
Drivers table, primary key: DriverID
These two tables are joined to a junction table, VehiclesDrivers, primary
keys VRM and DriverID
Warnings table, primary key: WarningID
Drivers and Warnings tables are also in a many to many relationship and use
the following junction table:
WarningsDrivers table, primary keys WarningID and DriverID

So there are two many to many relationships.

The Warnings table consists of 19 set values which will not be changed. I
need to use a combo box to be able to select and assign multiple warnings for
each driver. I have a main form (vehicles) and a subform, drivers. On this
subform, drivers associated with the vehicle are listed.

I think I have figured out how to add new drivers to the vehicle using the
subform... I can just type them in on the form.
What is confusing me is how I can add new warnings to the drivers. I have a
combo box to list each of the 19 possible warnings. I have set the row
source to Warnings so the 19 fields are listed in the combo.
The Control Source is set to WarningID. I am confused as I have just
found that entries have been written to the warnings table duplicating the
warning values! How do I write to the junction table (DriversWarnings?)

Thank you in advance. I am trying very hard and need to get this sorted...
my living depends on it!

rich

 

[Guest]

I have just been able to get the selected field (warning) from the combo to
be assigned to a driver. The problem is now that it is overwriting an
existing warning instead of assigning an additional warning. what am I doing
wrong?

 

[Amy Blankenship]

I have just been able to get the selected field (warning) from the combo
to
be assigned to a driver. The problem is now that it is overwriting an
existing warning instead of assigning an additional warning. what am I
doing
wrong?

Your combobox is going to just refer to one record.

You need to provide a continuous form that allows you to select one warning
on each form, which will write a new record each time something different is
selected in one of the comboboxes (1 per continuous form). You might be
able to script something that inserted more than one record in the
background, but I'd be worried about conflicting with the native form
functionality in Access, which works quite well, as well as creating a new
problem of how do you get those warnings back on the screen for editing.

HTH;

Amy

 

[Guest]

Thanks Amy.
I'm now using a continuous form as you advise and it's working fine.

Is there any way of preventing a user from being able to select an entry
from the combo list that has already been selected (can it be grayed out or
removed from the combo list?) There are 19 warnings in the list. As things
stand at the moment, a user can select a previously selected item from the
list, which appears in the combo. When the user clicks another field or
tries to close the form, Access says, "The changes weren't successful because
they would create duplicate entries...." Next to each combo is a delete
record button which works fine for deleting "regular" combo entries but not
those that have already been seleted in a previous combo. If I use the
delete button for a duplicate entry, I get the same message as above.

If I click on the combo field and delete the duplicate manually using the
delete key, Access still won't allow the form to close, saying, "Index or
primary key cannot contain a null value"

If you are able to help I'd be very grateful. Either way, thanks for your
help so far.

Rich

 

[Amy Blankenship]

You'd have to set the recordsource of the combobox to not be in the selected
warnings for that driver and probably requery on before update. Can't give
you exact code today, maybe someone else will chime in.

HTH;

Amy

 

[Guest]

I haven't been able to sort this one and am I'm still thinking hard about it.
I understand I need to set the row source to show entries from the
"warnings" table that aren't in the DriversWarnings table for that driver.
Using the query builder, how do you set " not in" for criteria? And even if
I do this, will the previously selected warnings still show up on the combo
boxes (in their pre-click display state) in records above in the continuous
subform?

 

[Amy Blankenship]

I haven't been able to sort this one and am I'm still thinking hard about
it.
I understand I need to set the row source to show entries from the
"warnings" table that aren't in the DriversWarnings table for that driver.
Using the query builder, how do you set " not in" for criteria? And even
if
I do this, will the previously selected warnings still show up on the
combo
boxes (in their pre-click display state) in records above in the
continuous
subform?

In the criteria box, for the WarningID, type

NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)

I am not sure what happens to the value of a Combobox if the list from the
box no longer includes the current value. You could wind up having to have
a separate combobox that populates a plain jane text field. You might be
able to make this seamless by making the combobox invisible and creating a
graphic that looks like the down arrow on the end of a combobox next to the
text box that makes the combobox visible. It's probably a bit more
technical than that, but the basic idea should work.

HTH;

Amy

 

[John Vinson]

In the criteria box, for the WarningID, type

NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)

That'll work fine.

I am not sure what happens to the value of a Combobox if the list from the
box no longer includes the current value. You could wind up having to have
a separate combobox that populates a plain jane text field. You might be
able to make this seamless by making the combobox invisible and creating a
graphic that looks like the down arrow on the end of a combobox next to the
text box that makes the combobox visible. It's probably a bit more
technical than that, but the basic idea should work.

It does show up blank for the other records in the continuous form.
One sneaky way to get the value displayed while still using the
conditional combo box is to carefully superimpose a textbox, based on
a DLookUp perhaps, over the text area of the combo box. Make the
textbox's properties Tab Stop = No, Enabled = No, Locked = Yes so the
user can't do anything to it; when the downarrow (left visible) of the
combo is clicked or the user tabs into the combo, it will "come in
front" and work as usual.

John W. Vinson[MVP]

 

[Guest]

Thanks for your replies. I'm still having problems with this after spending
a lot of time on trying to get it to work.

What should I set the DLookup for the textbox to?
I have tried:
=DLookUp("[Warning]","Warnings", "[WarningID] =" _ & Forms![Associated
Driver Warnings Details Subform]!WarningID)
but I get an error saying the expression contains invalid syntax.
I based this on the this example from the MSDN website:

=DLookup("[ProductName]", "Products", "[ProductID] =" _
& Forms![Order Details]!ProductID)
given a
http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

To recap what I want the lookup to do:
I need to look up the warning from the Warnings table where WarningID is the
same as in the current record in the Associated Driver Warnings Details
Subform (continuous subform.) The record source for the Associated Driver
Warnings Details Subform is DriversWarnings.)

Also I have set the query for the combo as follows:
Not In (SELECT WarningID FROM DriversWarnings WHERE DriverID =
Forms![Vehicles]!DriverID)
This isn't working either... it's just giving an empty combo. I'm throwing
lots of time at this but still can't get it working. What am I doing wrong?

Recap:
The main form in the database is called Vehicles, based on vehicles table.
The continuous subform on the Vehicles form is called Vehicles/ Drivers
Junction Table subform and has VehiclesDrivers as the source object. This is
a continuous form. For each record there is a button to open the record to
add or edit driver warnings. This opens up the Drivers form (to show only
the driver in that record.) It is on this (single) form that there is a
continuous subform called Associated Driver Warnings Details Subform, with
record source DriversWarnings, and the combo is in the detail section to show
up for each record.

In the criteria box, for the WarningID, type

NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)

That'll work fine.

I am not sure what happens to the value of a Combobox if the list from the
box no longer includes the current value. You could wind up having to have
a separate combobox that populates a plain jane text field. You might be
able to make this seamless by making the combobox invisible and creating a
graphic that looks like the down arrow on the end of a combobox next to the
text box that makes the combobox visible. It's probably a bit more
technical than that, but the basic idea should work.

It does show up blank for the other records in the continuous form.
One sneaky way to get the value displayed while still using the
conditional combo box is to carefully superimpose a textbox, based on
a DLookUp perhaps, over the text area of the combo box. Make the
textbox's properties Tab Stop = No, Enabled = No, Locked = Yes so the
user can't do anything to it; when the downarrow (left visible) of the
combo is clicked or the user tabs into the combo, it will "come in
front" and work as usual.

John W. Vinson[MVP]