Limit combox to unselected item only

[Silvio]

I have a form Address and a subform (continuous form) Hold.

The relation is one-to-many (one address many holds). I am using a combo box
to enter hold. How can I prevent that a hold from the combo box is entered
more than one time? Address and Holds store data in 2 different tables
related to each other. One address (or AddressID - Primary key) can have many
holds but should be only one of each type. How can I accomplish this?

e.g.
Correct:
152 Broadway St
Red
Green
Blue


Wrong:
152 Broadway St
Red
Red
Green
Blue

 

[Silvio]

This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;

 

[Marshall Barton]

I think it's usually more efficient to use a frustrated
outer join than Not In(Select ...)

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
LEFT JOIN YourTable
ON tblHoldType.HoldID = YourTable.YourOtherHoldIDField
WHERE YourTable.YourOtherHoldIDField Is Null
ORDER BY tblHoldType.HoldDesc
--
Marsh
MVP [MS Access]

I'll just have to take a guess:

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
WHERE HoldID Not In (Select YourOtherHoldIDField FROM YourTable WHERE [ID] =
[ID])
ORDER BY tblHoldType.HoldDesc;

That is *real* crude code. The SubSelect should pull the proper field from
probably the RecordSource of your SubForm.

This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;

 

[Silvio]

Marsh, this code also makes the entries to appear blank meaning the record
line is there but the description is missing, also the combo box selection
carry the same limitation as I navigate from record to record meaning that
the number of "available" selections is the same no matter which record I am
looking. It appear to me that this code is creating a circular reference and
it does not limit the filter to tblHolds.addressID = tblHoldType.AddressID.

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType LEFT JOIN tblHolds ON tblHoldType.HoldID = tblHolds.HoldID
WHERE (((tblHolds.HoldID) Is Null))
ORDER BY tblHoldType.HoldDesc;

I think it's usually more efficient to use a frustrated
outer join than Not In(Select ...)

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
LEFT JOIN YourTable
ON tblHoldType.HoldID = YourTable.YourOtherHoldIDField
WHERE YourTable.YourOtherHoldIDField Is Null
ORDER BY tblHoldType.HoldDesc
--
Marsh
MVP [MS Access]

I'll just have to take a guess:

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
WHERE HoldID Not In (Select YourOtherHoldIDField FROM YourTable WHERE [ID] =
[ID])
ORDER BY tblHoldType.HoldDesc;

That is *real* crude code. The SubSelect should pull the proper field from
probably the RecordSource of your SubForm.

This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;

 

[Silvio]

Marshall, can I e-mail you what I have?

p.s. I am willing to compensate you for the help.

I think it's usually more efficient to use a frustrated
outer join than Not In(Select ...)

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
LEFT JOIN YourTable
ON tblHoldType.HoldID = YourTable.YourOtherHoldIDField
WHERE YourTable.YourOtherHoldIDField Is Null
ORDER BY tblHoldType.HoldDesc
--
Marsh
MVP [MS Access]

I'll just have to take a guess:

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
WHERE HoldID Not In (Select YourOtherHoldIDField FROM YourTable WHERE [ID] =
[ID])
ORDER BY tblHoldType.HoldDesc;

That is *real* crude code. The SubSelect should pull the proper field from
probably the RecordSource of your SubForm.

This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;

 

[Marshall Barton]

The problem of the other rows being blank is related to
using a dependent combo box on a continuous form and has
nothing to do with the queries Allen or I posted. The data
should be correct even if you can't see it. switch the
subform to single view for testing purposes so you are
distracted by display issues.

Getting the display you want on a continuous form is a very
messy issue that you should either live with or google for
previously posted discussions on the topic.

As for the list being the same for every record, I suspect
that you have not explained enough to get the query to be
related to the main form record. Shouldn't tblHolds have a
foreign key field to relate the holds to an address? If
there is, then the query should be modified to restrict the
combo box's row to the holds for the address. Think about
it. My guess is that maybe the query's Where clause should
be something like:

WHERE tblHolds.HoldID Is Null AND tblHolds.addressID =
Forms!mainform.addressID
--
Marsh
MVP [MS Access]

Marsh, this code also makes the entries to appear blank meaning the record
line is there but the description is missing, also the combo box selection
carry the same limitation as I navigate from record to record meaning that
the number of "available" selections is the same no matter which record I am
looking. It appear to me that this code is creating a circular reference and
it does not limit the filter to tblHolds.addressID = tblHoldType.AddressID.

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType LEFT JOIN tblHolds ON tblHoldType.HoldID = tblHolds.HoldID
WHERE (((tblHolds.HoldID) Is Null))
ORDER BY tblHoldType.HoldDesc;

I think it's usually more efficient to use a frustrated
outer join than Not In(Select ...)

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
LEFT JOIN YourTable
ON tblHoldType.HoldID = YourTable.YourOtherHoldIDField
WHERE YourTable.YourOtherHoldIDField Is Null
ORDER BY tblHoldType.HoldDesc

I'll just have to take a guess:

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
WHERE HoldID Not In (Select YourOtherHoldIDField FROM YourTable WHERE [ID] =
[ID])
ORDER BY tblHoldType.HoldDesc;

That is *real* crude code. The SubSelect should pull the proper field from
probably the RecordSource of your SubForm.


Silvio wrote:
This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;

 

[Silvio]

Mmmmhhhh... I think I will change approach to this issue, it is too
complicated for my level of expertice. Thank you for your time anyway.

The problem of the other rows being blank is related to
using a dependent combo box on a continuous form and has
nothing to do with the queries Allen or I posted. The data
should be correct even if you can't see it. switch the
subform to single view for testing purposes so you are
distracted by display issues.

Getting the display you want on a continuous form is a very
messy issue that you should either live with or google for
previously posted discussions on the topic.

As for the list being the same for every record, I suspect
that you have not explained enough to get the query to be
related to the main form record. Shouldn't tblHolds have a
foreign key field to relate the holds to an address? If
there is, then the query should be modified to restrict the
combo box's row to the holds for the address. Think about
it. My guess is that maybe the query's Where clause should
be something like:

WHERE tblHolds.HoldID Is Null AND tblHolds.addressID =
Forms!mainform.addressID
--
Marsh
MVP [MS Access]

Marsh, this code also makes the entries to appear blank meaning the record
line is there but the description is missing, also the combo box selection
carry the same limitation as I navigate from record to record meaning that
the number of "available" selections is the same no matter which record I am
looking. It appear to me that this code is creating a circular reference and
it does not limit the filter to tblHolds.addressID = tblHoldType.AddressID.

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType LEFT JOIN tblHolds ON tblHoldType.HoldID = tblHolds.HoldID
WHERE (((tblHolds.HoldID) Is Null))
ORDER BY tblHoldType.HoldDesc;

I think it's usually more efficient to use a frustrated
outer join than Not In(Select ...)

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
LEFT JOIN YourTable
ON tblHoldType.HoldID = YourTable.YourOtherHoldIDField
WHERE YourTable.YourOtherHoldIDField Is Null
ORDER BY tblHoldType.HoldDesc


ruralguy via AccessMonster.com wrote:

I'll just have to take a guess:

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
WHERE HoldID Not In (Select YourOtherHoldIDField FROM YourTable WHERE [ID] =
[ID])
ORDER BY tblHoldType.HoldDesc;

That is *real* crude code. The SubSelect should pull the proper field from
probably the RecordSource of your SubForm.


Silvio wrote:
This is the combobox RowSource, how do I change it?

SELECT tblHoldType.HoldID, tblHoldType.HoldDesc
FROM tblHoldType
ORDER BY tblHoldType.HoldDesc;