Combo to add records

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

Guest

Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
 
Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.
 
They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

Klatuu said:
Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

Rich1234 said:
Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
 
Here is an SQL statement that will return only Warnings the Driver has not
received:

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));

It should go in the After Update event of whatever control you use to select
a warning for the driver. You will also need to Requery the combo box each
time you assign a warning to a driver.
Now, as to how it will affect the other rows in your continuous for, I am
not sure, and don't have a way to test it.

I think the suggest solution to put a text box over the combo is ugly. If
the above solution does not work, then I would suggest you use a DLookup to
see if the Driver/Warning combination already exists, present an message box
with the error, and cancel the update on the control. This is certainly not
as elegant as what you want to do, but it may be necessary.

Let me know how it works out.

Rich1234 said:
They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

Klatuu said:
Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

Rich1234 said:
Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your post Klatuu

Where should I put this SQL? You mention it should go in the After Update
event of the control I am using to select a warning. I pasted it into the
after update event procedure and the SQL was coloured red (this must be the
wrong place to put it... this is just for VB, right?)
I am using the combo to select warnings (in the detail section of a
continuous subform) and so I want only warnings that aren't already assigned
to appear in the combo (so does this query need to go in the rowsource for
the combo?) Presumably I would then put the requery in the after update
event of the combo?

The message box idea is fine if it works. But I don't see why it would be
needed. If the combo on each record in the subform only included warnings
that weren't already selected, it would be impossible to select a duplicate.
That is the thinking behind all of this. If there is another way of doing
this, fine.

Do I need to create a query outside of the combo box, give it a name and
refer to this query name as the row source for the combo, or can I put it
directly into the combo itself? (Does this make any difference? What's
confusing me is the fact that the combo has to "requery" after each
selection.)

Throughout all of this I have been unclear on where to put the SQL. Surely
excluding the selected warnings from rowsource for the combo is the key to
sorting this....? What am I doing wrong? The textbox idea was to put a
textbox carefully positioned directly over the combo so that it would still
display the previously selected warning (using DLookup) for that driver
(which would now not show up as it would be excluded from the row source.)

Have I explained all this OK?
Looking forward to your thoughts on this one
Rich
Klatuu said:
Here is an SQL statement that will return only Warnings the Driver has not
received:

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));

It should go in the After Update event of whatever control you use to select
a warning for the driver. You will also need to Requery the combo box each
time you assign a warning to a driver.
Now, as to how it will affect the other rows in your continuous for, I am
not sure, and don't have a way to test it.

I think the suggest solution to put a text box over the combo is ugly. If
the above solution does not work, then I would suggest you use a DLookup to
see if the Driver/Warning combination already exists, present an message box
with the error, and cancel the update on the control. This is certainly not
as elegant as what you want to do, but it may be necessary.

Let me know how it works out.

Rich1234 said:
They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

Klatuu said:
Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

:

Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
Click to expand...
 
The SQL statement should be the row source for the combo box. Do your
requery in the After Update event.

Rich1234 said:
Thanks for your post Klatuu

Where should I put this SQL? You mention it should go in the After Update
event of the control I am using to select a warning. I pasted it into the
after update event procedure and the SQL was coloured red (this must be the
wrong place to put it... this is just for VB, right?)
I am using the combo to select warnings (in the detail section of a
continuous subform) and so I want only warnings that aren't already assigned
to appear in the combo (so does this query need to go in the rowsource for
the combo?) Presumably I would then put the requery in the after update
event of the combo?

The message box idea is fine if it works. But I don't see why it would be
needed. If the combo on each record in the subform only included warnings
that weren't already selected, it would be impossible to select a duplicate.
That is the thinking behind all of this. If there is another way of doing
this, fine.

Do I need to create a query outside of the combo box, give it a name and
refer to this query name as the row source for the combo, or can I put it
directly into the combo itself? (Does this make any difference? What's
confusing me is the fact that the combo has to "requery" after each
selection.)

Throughout all of this I have been unclear on where to put the SQL. Surely
excluding the selected warnings from rowsource for the combo is the key to
sorting this....? What am I doing wrong? The textbox idea was to put a
textbox carefully positioned directly over the combo so that it would still
display the previously selected warning (using DLookup) for that driver
(which would now not show up as it would be excluded from the row source.)

Have I explained all this OK?
Looking forward to your thoughts on this one
Rich
Klatuu said:
Here is an SQL statement that will return only Warnings the Driver has not
received:

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));

It should go in the After Update event of whatever control you use to select
a warning for the driver. You will also need to Requery the combo box each
time you assign a warning to a driver.
Now, as to how it will affect the other rows in your continuous for, I am
not sure, and don't have a way to test it.

I think the suggest solution to put a text box over the combo is ugly. If
the above solution does not work, then I would suggest you use a DLookup to
see if the Driver/Warning combination already exists, present an message box
with the error, and cancel the update on the control. This is certainly not
as elegant as what you want to do, but it may be necessary.

Let me know how it works out.

Rich1234 said:
They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

:

Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

:

Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
Click to expand...
 
I put this SQL in as the rowsource, togther with the requery in the After
Update event but it isn't working. When you click on the down arrow at the
end of the combo to view the list, there are no items showing in the list at
all. The "dropdown" doesn't appear.
Any more ideas? How is this possible?
Thanks Klatuu for your advice so far
rich

Klatuu said:
The SQL statement should be the row source for the combo box. Do your
requery in the After Update event.

Rich1234 said:
Thanks for your post Klatuu

Where should I put this SQL? You mention it should go in the After Update
event of the control I am using to select a warning. I pasted it into the
after update event procedure and the SQL was coloured red (this must be the
wrong place to put it... this is just for VB, right?)
I am using the combo to select warnings (in the detail section of a
continuous subform) and so I want only warnings that aren't already assigned
to appear in the combo (so does this query need to go in the rowsource for
the combo?) Presumably I would then put the requery in the after update
event of the combo?

The message box idea is fine if it works. But I don't see why it would be
needed. If the combo on each record in the subform only included warnings
that weren't already selected, it would be impossible to select a duplicate.
That is the thinking behind all of this. If there is another way of doing
this, fine.

Do I need to create a query outside of the combo box, give it a name and
refer to this query name as the row source for the combo, or can I put it
directly into the combo itself? (Does this make any difference? What's
confusing me is the fact that the combo has to "requery" after each
selection.)

Throughout all of this I have been unclear on where to put the SQL. Surely
excluding the selected warnings from rowsource for the combo is the key to
sorting this....? What am I doing wrong? The textbox idea was to put a
textbox carefully positioned directly over the combo so that it would still
display the previously selected warning (using DLookup) for that driver
(which would now not show up as it would be excluded from the row source.)

Have I explained all this OK?
Looking forward to your thoughts on this one
Rich
Klatuu said:
Here is an SQL statement that will return only Warnings the Driver has not
received:

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));

It should go in the After Update event of whatever control you use to select
a warning for the driver. You will also need to Requery the combo box each
time you assign a warning to a driver.
Now, as to how it will affect the other rows in your continuous for, I am
not sure, and don't have a way to test it.

I think the suggest solution to put a text box over the combo is ugly. If
the above solution does not work, then I would suggest you use a DLookup to
see if the Driver/Warning combination already exists, present an message box
with the error, and cancel the update on the control. This is certainly not
as elegant as what you want to do, but it may be necessary.

Let me know how it works out.

:

They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

:

Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

:

Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
Click to expand...
 
Since I don't have everything you have in front of me, I can't be sure.
There may be an error in the way the SQL statement is structured. It (sorta)
works, because it does not return an error, but there is something in the way
it is looking at the table.

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));


Rich1234 said:
I put this SQL in as the rowsource, togther with the requery in the After
Update event but it isn't working. When you click on the down arrow at the
end of the combo to view the list, there are no items showing in the list at
all. The "dropdown" doesn't appear.
Any more ideas? How is this possible?
Thanks Klatuu for your advice so far
rich

Klatuu said:
The SQL statement should be the row source for the combo box. Do your
requery in the After Update event.

Rich1234 said:
Thanks for your post Klatuu

Where should I put this SQL? You mention it should go in the After Update
event of the control I am using to select a warning. I pasted it into the
after update event procedure and the SQL was coloured red (this must be the
wrong place to put it... this is just for VB, right?)
I am using the combo to select warnings (in the detail section of a
continuous subform) and so I want only warnings that aren't already assigned
to appear in the combo (so does this query need to go in the rowsource for
the combo?) Presumably I would then put the requery in the after update
event of the combo?

The message box idea is fine if it works. But I don't see why it would be
needed. If the combo on each record in the subform only included warnings
that weren't already selected, it would be impossible to select a duplicate.
That is the thinking behind all of this. If there is another way of doing
this, fine.

Do I need to create a query outside of the combo box, give it a name and
refer to this query name as the row source for the combo, or can I put it
directly into the combo itself? (Does this make any difference? What's
confusing me is the fact that the combo has to "requery" after each
selection.)

Throughout all of this I have been unclear on where to put the SQL. Surely
excluding the selected warnings from rowsource for the combo is the key to
sorting this....? What am I doing wrong? The textbox idea was to put a
textbox carefully positioned directly over the combo so that it would still
display the previously selected warning (using DLookup) for that driver
(which would now not show up as it would be excluded from the row source.)

Have I explained all this OK?
Looking forward to your thoughts on this one
Rich
:

Here is an SQL statement that will return only Warnings the Driver has not
received:

SELECT Warnings.WarningsID, DriversWarnings.WarningsID
FROM Warnings LEFT JOIN DriversWarnings ON Warnings.WarningsID =
DriversWarnings.WarningsID
GROUP BY Warnings.WarningsID, DriversWarnings.WarningsID
HAVING (((DriversWarnings.WarningsID) Is Null));

It should go in the After Update event of whatever control you use to select
a warning for the driver. You will also need to Requery the combo box each
time you assign a warning to a driver.
Now, as to how it will affect the other rows in your continuous for, I am
not sure, and don't have a way to test it.

I think the suggest solution to put a text box over the combo is ugly. If
the above solution does not work, then I would suggest you use a DLookup to
see if the Driver/Warning combination already exists, present an message box
with the error, and cancel the update on the control. This is certainly not
as elegant as what you want to do, but it may be necessary.

Let me know how it works out.

:

They are in a table, Warnings, PK WarningID. The other field just contains
the 19 warnings

:

Are the 19 possible warnings that show in the combo box in a table or just in
a value list?
The technique you use will depend on which it is. Post back, and I think I
can show you how to accomplish this.

:

Hi
I have tables
Drivers (DriverID PK)
Warnings (WarningID PK)
DriversWarnings (DriverID and WarningID PK)

(Warnings are assigned to drivers in a many to many.)
I have a main form, showing info from the Drivers table and a continuous
subform, warnings, to show respective warnings for the driver. In the detail
section (ie for each record in the subform) there is a combo box where the
user can select a new warning to be assigned to a driver. There is a finite
list of 19 warnings possible for each driver. How can I prevent warnings
that are already assigned to the driver selected in the main form from
appearing in the combo in the subform so that the driver won't get duplicate
warnings assigned?

In a previous post the following query was proposed for the combo:
In the criteria box, for the WarningID, type
NOT IN (SELECT WarningID FROM WarningDrivers WHERE DriverID=
Forms![MainForm]!DriverID)
but I still haven't managed to get this to work. Maybe I'm doing something
wrong. I'm told that this will also prevent the previously selected warning
from appearing in records above on the continuos subform, and that I need to
put a textbox using a DLookup over the top of the combo to reflect this. I
haven't been able to work DLookup expression out either.

PLease help if you can. After much time spent on this, I will be very
grateful.
TIA
Rich
Click to expand...
Click to expand...
Click to expand...
 
Back
Top