Combo box conditional row source

[BruceM]

The background:
Jobs are performed according to Process Plans. A Process Plan may be for a
particular part number(s), or there may be no specific associated part
numbers. All part numbers are stored in a PartNumber table. A part may be
associated with several Process Plans and vice versa (many-to-many), so
there is a junction table (tjctPlanPart) between tblProcessPlan and
tblPartNumber.
When a job arrives, the user enters the job number, selects a ProcessPlan,
and selects a PartNumber (combo boxes for selection). This information is
stored in tblJob. If the selected ProcessPlan has associated part numbers,
I want the combo box to show just those part numbers. I have that part
worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job] is
the field in which the Process Plan ID is stored. Plan ID is the PK field
in tblProcessPlan; PlanID_Job is the corresponding FK field in tblJob. The
user first selects the ProcessPlan combo box on the Job form. The
PartNumber combo box on the Job table uses that field to filter the results
to just the part numbers associated with that ProcessPlan. There is a
one-to-many relationship from tblPart to tblJob, if that matters for this
situation.

The thing I cannot figure out is what to do if the Process Plan does not
have any associated part numbers (this could happen because the plan is for
a category of part rather than a specific part number). The SQL in that
situation would be to select all of the parts from the Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process Plan
has no associated part numbers?

 

[Michel Walsh]

probably something like:



SELECT Nz(tjctPlanPart.PartID_PlanPart, tblPart.PartID)
tblPart.PartNum,
tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])), true)
ORDER BY tblPart.PartNum;




NOTE: the first query has 4 selected 'fields', the second query has only 3,
so, I assumed that in the second case, the fourth field would be left null.
I don't understand, though, why you select

tjctPlanPart.PlanID_PlanPart

twice, in the first query.


I changed the INNER JOIN to a LEFT JOIN, and add two NZ( ).




Hoping it may help,
Vanderghast, Access MVP

The background:
Jobs are performed according to Process Plans. A Process Plan may be for
a particular part number(s), or there may be no specific associated part
numbers. All part numbers are stored in a PartNumber table. A part may
be associated with several Process Plans and vice versa (many-to-many), so
there is a junction table (tjctPlanPart) between tblProcessPlan and
tblPartNumber.
When a job arrives, the user enters the job number, selects a ProcessPlan,
and selects a PartNumber (combo boxes for selection). This information is
stored in tblJob. If the selected ProcessPlan has associated part
numbers, I want the combo box to show just those part numbers. I have
that part worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job] is
the field in which the Process Plan ID is stored. Plan ID is the PK field
in tblProcessPlan; PlanID_Job is the corresponding FK field in tblJob.
The user first selects the ProcessPlan combo box on the Job form. The
PartNumber combo box on the Job table uses that field to filter the
results to just the part numbers associated with that ProcessPlan. There
is a one-to-many relationship from tblPart to tblJob, if that matters for
this situation.

The thing I cannot figure out is what to do if the Process Plan does not
have any associated part numbers (this could happen because the plan is
for a category of part rather than a specific part number). The SQL in
that situation would be to select all of the parts from the Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process
Plan has no associated part numbers?

 

[BruceM]

I didn't use the same field twice. However, my choice of field names made
them a bit difficult to distinguish from each other. One is PartID_PlanPart
and the other is PlanID_PlanPart. I am experimenting with a slightly
modified naming convention in which the PK field is PartID from tblPart and
PlanID from tblPlan, while the FK fields append a shortened version of the
table name to the field names used for the PKs.

However, that is irrelevant, since the code stopped working when I added a
record or something.

As I said, a Plan may involve many Parts and a Part may be associated with
many Plans, so tjctPlanPart resolves the many-to-many. The Plan records are
set up ahead of time. There is a library of them, in effect.

A Plan is involved in several other many-to-many relationships (Processes,
etc.), but I don't think they are relevant to this question.

When starting a Job, the user enters a job number, then selects a Plan from
a combo box. If the selected Plan is for part numbers 123 and 456, I would
like the PartNumber combo box to show just those two parts. If the selected
Plan is generic and does not have any Parts associated with it, I would like
the PartNumber combo box to show all part numbers from the Parts table.

However, I absolutely cannot find a way to make this work. I cannot even
duplicate the apparent success I had when I made the original post. I will
probably need to just go with a list of all part numbers. It is awkward and
unwieldy, but it's probably the best I can do.

However, if I am missing something obvious I would like to learn what it is.
It is really a very, very poor situation that the users will have to go
through a list of hundreds of part numbers to select the one they need for a
job.

FWIW, the SQL you suggested (it needed one more opening parentheses after
the second Nz to get the syntax working) produced a list of all part numbers
in all cases.

probably something like:



SELECT Nz(tjctPlanPart.PartID_PlanPart, tblPart.PartID)
tblPart.PartNum,
tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])), true)
ORDER BY tblPart.PartNum;




NOTE: the first query has 4 selected 'fields', the second query has only
3, so, I assumed that in the second case, the fourth field would be left
null. I don't understand, though, why you select

tjctPlanPart.PlanID_PlanPart

twice, in the first query.


I changed the INNER JOIN to a LEFT JOIN, and add two NZ( ).




Hoping it may help,
Vanderghast, Access MVP

The background:
Jobs are performed according to Process Plans. A Process Plan may be for
a particular part number(s), or there may be no specific associated part
numbers. All part numbers are stored in a PartNumber table. A part may
be associated with several Process Plans and vice versa (many-to-many),
so there is a junction table (tjctPlanPart) between tblProcessPlan and
tblPartNumber.
When a job arrives, the user enters the job number, selects a
ProcessPlan, and selects a PartNumber (combo boxes for selection). This
information is stored in tblJob. If the selected ProcessPlan has
associated part numbers, I want the combo box to show just those part
numbers. I have that part worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job] is
the field in which the Process Plan ID is stored. Plan ID is the PK
field in tblProcessPlan; PlanID_Job is the corresponding FK field in
tblJob. The user first selects the ProcessPlan combo box on the Job form.
The PartNumber combo box on the Job table uses that field to filter the
results to just the part numbers associated with that ProcessPlan. There
is a one-to-many relationship from tblPart to tblJob, if that matters for
this situation.

The thing I cannot figure out is what to do if the Process Plan does not
have any associated part numbers (this could happen because the plan is
for a category of part rather than a specific part number). The SQL in
that situation would be to select all of the parts from the Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process
Plan has no associated part numbers?

 

[BruceM]

Update:
I renamed a table, then changed the name back again, but not correctly,
which is why my original code didn't work. This Row Source SQL produces a
combo box list of part numbers that are associated with the Plan selected in
another combo box:

SELECT Nz(tjctPlanPart.PartID_PlanPart,tblPart.PartID), tblPart.PartNum,
tblPart.PartDescr
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID=tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Actually, the first line works the same way if it simply reads:
SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr

I omitted tjctPlanPart.PlanID_PlanPart from the SELECT (it was there in my
first incarnation of the code) because it isn't needed there. It is needed
only in the WHERE clause.

The Nz is the beginning of a way (I think) to make allowance for the
possibility that a Plan does not have any associated part numbers. The SQL
works the same whether it is there or not. If I eliminate the WHERE clause
the SQL returns all part numbers from tblParts.

In effect, I want the SQL to be as follows when there are no part numbers
for the selected Plan:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

probably something like:



SELECT Nz(tjctPlanPart.PartID_PlanPart, tblPart.PartID)
tblPart.PartNum,
tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])), true)
ORDER BY tblPart.PartNum;




NOTE: the first query has 4 selected 'fields', the second query has only
3, so, I assumed that in the second case, the fourth field would be left
null. I don't understand, though, why you select

tjctPlanPart.PlanID_PlanPart

twice, in the first query.


I changed the INNER JOIN to a LEFT JOIN, and add two NZ( ).




Hoping it may help,
Vanderghast, Access MVP

The background:
Jobs are performed according to Process Plans. A Process Plan may be for
a particular part number(s), or there may be no specific associated part
numbers. All part numbers are stored in a PartNumber table. A part may
be associated with several Process Plans and vice versa (many-to-many),
so there is a junction table (tjctPlanPart) between tblProcessPlan and
tblPartNumber.
When a job arrives, the user enters the job number, selects a
ProcessPlan, and selects a PartNumber (combo boxes for selection). This
information is stored in tblJob. If the selected ProcessPlan has
associated part numbers, I want the combo box to show just those part
numbers. I have that part worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job] is
the field in which the Process Plan ID is stored. Plan ID is the PK
field in tblProcessPlan; PlanID_Job is the corresponding FK field in
tblJob. The user first selects the ProcessPlan combo box on the Job form.
The PartNumber combo box on the Job table uses that field to filter the
results to just the part numbers associated with that ProcessPlan. There
is a one-to-many relationship from tblPart to tblJob, if that matters for
this situation.

The thing I cannot figure out is what to do if the Process Plan does not
have any associated part numbers (this could happen because the plan is
for a category of part rather than a specific part number). The SQL in
that situation would be to select all of the parts from the Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process
Plan has no associated part numbers?

 

[Michel Walsh]

Hi,


You have to care for the possible null coming from tjctPlanPart, even in the
WHERE clause. I would try:

WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])) OR
(tjctPlanPart.PlanID_PlanPart IS NULL)


because if you don't, the WHERE clause destroys the useful work done by the
LEFT JOIN, and you are back to an INNER JOIN.

Instead of the OR, you can also use:


WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]), true)



Hoping it may help,
Vanderghast, Access MVP

Update:
I renamed a table, then changed the name back again, but not correctly,
which is why my original code didn't work. This Row Source SQL produces a
combo box list of part numbers that are associated with the Plan selected
in another combo box:

SELECT Nz(tjctPlanPart.PartID_PlanPart,tblPart.PartID), tblPart.PartNum,
tblPart.PartDescr
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID=tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Actually, the first line works the same way if it simply reads:
SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr

I omitted tjctPlanPart.PlanID_PlanPart from the SELECT (it was there in my
first incarnation of the code) because it isn't needed there. It is
needed only in the WHERE clause.

The Nz is the beginning of a way (I think) to make allowance for the
possibility that a Plan does not have any associated part numbers. The
SQL works the same whether it is there or not. If I eliminate the WHERE
clause the SQL returns all part numbers from tblParts.

In effect, I want the SQL to be as follows when there are no part numbers
for the selected Plan:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

probably something like:



SELECT Nz(tjctPlanPart.PartID_PlanPart, tblPart.PartID)
tblPart.PartNum,
tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])), true)
ORDER BY tblPart.PartNum;




NOTE: the first query has 4 selected 'fields', the second query has only
3, so, I assumed that in the second case, the fourth field would be left
null. I don't understand, though, why you select

tjctPlanPart.PlanID_PlanPart

twice, in the first query.


I changed the INNER JOIN to a LEFT JOIN, and add two NZ( ).




Hoping it may help,
Vanderghast, Access MVP

The background:
Jobs are performed according to Process Plans. A Process Plan may be
for a particular part number(s), or there may be no specific associated
part numbers. All part numbers are stored in a PartNumber table. A
part may be associated with several Process Plans and vice versa
(many-to-many), so there is a junction table (tjctPlanPart) between
tblProcessPlan and tblPartNumber.
When a job arrives, the user enters the job number, selects a
ProcessPlan, and selects a PartNumber (combo boxes for selection). This
information is stored in tblJob. If the selected ProcessPlan has
associated part numbers, I want the combo box to show just those part
numbers. I have that part worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job]
is the field in which the Process Plan ID is stored. Plan ID is the PK
field in tblProcessPlan; PlanID_Job is the corresponding FK field in
tblJob. The user first selects the ProcessPlan combo box on the Job
form. The PartNumber combo box on the Job table uses that field to
filter the results to just the part numbers associated with that
ProcessPlan. There is a one-to-many relationship from tblPart to
tblJob, if that matters for this situation.

The thing I cannot figure out is what to do if the Process Plan does not
have any associated part numbers (this could happen because the plan is
for a category of part rather than a specific part number). The SQL in
that situation would be to select all of the parts from the Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process
Plan has no associated part numbers?

 

[BruceM]

Thanks for taking a crack at this, but I have to tell you that both
approaches had the same result: All records in all cases. Quite possibly I
provided incomplete information.

What I ended up doing was to use the form's Current event to set the row
source.

Dim lngCount As Long
Dim strSQL As String, strSQL_All As String

lngCount = DCount("PlanID_PlanPart", "tjctPlanPart", _
"PlanID_PlanPart = " & PlanID_Job)

' PlanID_Job is a field to which a combo box on the form is
bound

strSQL = "SELECT tjctPlanPart.PartID_PlanPart, PartNum, PartDescr " & _
"FROM tblPart " & _
"LEFT JOIN tjctPlanPart " & _
"ON tblPart.PartID=tjctPlanPart.PartID_PlanPart " & _
"WHERE (((tjctPlanPart.PlanID_PlanPart) = [PlanID_Job])) " & _
"ORDER BY PartNum"

strSQL_All = "SELECT PartID, PartNum, PartDescr " & _
"FROM tblPart " & _
"ORDER BY PartNum"

If lngCount = 0 Then
Me.cboPartID_Job.RowSource = strSQL_All
Else
Me.cboPartID_Job.RowSource = strSQL
End If

I'm not that pleased with having to use the domain aggregate function, but
it works, and will have to do for now. Maybe I could use DCount in similar
manner in the SQL for the Row Source Property. As I said, it works, so I
should move on to other things, but I am curious about how this could be
improved.

Hi,


You have to care for the possible null coming from tjctPlanPart, even in
the WHERE clause. I would try:

WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])) OR
(tjctPlanPart.PlanID_PlanPart IS NULL)


because if you don't, the WHERE clause destroys the useful work done by
the LEFT JOIN, and you are back to an INNER JOIN.

Instead of the OR, you can also use:


WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]), true)



Hoping it may help,
Vanderghast, Access MVP

Update:
I renamed a table, then changed the name back again, but not correctly,
which is why my original code didn't work. This Row Source SQL produces
a combo box list of part numbers that are associated with the Plan
selected in another combo box:

SELECT Nz(tjctPlanPart.PartID_PlanPart,tblPart.PartID), tblPart.PartNum,
tblPart.PartDescr
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID=tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Actually, the first line works the same way if it simply reads:
SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum, tblPart.PartDescr

I omitted tjctPlanPart.PlanID_PlanPart from the SELECT (it was there in
my first incarnation of the code) because it isn't needed there. It is
needed only in the WHERE clause.

The Nz is the beginning of a way (I think) to make allowance for the
possibility that a Plan does not have any associated part numbers. The
SQL works the same whether it is there or not. If I eliminate the WHERE
clause the SQL returns all part numbers from tblParts.

In effect, I want the SQL to be as follows when there are no part numbers
for the selected Plan:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

probably something like:



SELECT Nz(tjctPlanPart.PartID_PlanPart, tblPart.PartID)
tblPart.PartNum,
tblPart.PartDescr,
tjctPlanPart.PlanID_PlanPart
FROM tblPart
LEFT JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE Nz(((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job])), true)
ORDER BY tblPart.PartNum;




NOTE: the first query has 4 selected 'fields', the second query has
only 3, so, I assumed that in the second case, the fourth field would be
left null. I don't understand, though, why you select

tjctPlanPart.PlanID_PlanPart

twice, in the first query.


I changed the INNER JOIN to a LEFT JOIN, and add two NZ( ).




Hoping it may help,
Vanderghast, Access MVP

The background:
Jobs are performed according to Process Plans. A Process Plan may be
for a particular part number(s), or there may be no specific associated
part numbers. All part numbers are stored in a PartNumber table. A
part may be associated with several Process Plans and vice versa
(many-to-many), so there is a junction table (tjctPlanPart) between
tblProcessPlan and tblPartNumber.
When a job arrives, the user enters the job number, selects a
ProcessPlan, and selects a PartNumber (combo boxes for selection).
This information is stored in tblJob. If the selected ProcessPlan has
associated part numbers, I want the combo box to show just those part
numbers. I have that part worked out:

SELECT tjctPlanPart.PartID_PlanPart, tblPart.PartNum,
tblPart.PartDescr, tjctPlanPart.PlanID_PlanPart
FROM tblPart
INNER JOIN tjctPlanPart
ON tblPart.PartID = tjctPlanPart.PartID_PlanPart
WHERE (((tjctPlanPart.PlanID_PlanPart)=[PlanID_Job]))
ORDER BY tblPart.PartNum;

Again, the combo box using this code is on the Job form. [PlanID_Job]
is the field in which the Process Plan ID is stored. Plan ID is the PK
field in tblProcessPlan; PlanID_Job is the corresponding FK field in
tblJob. The user first selects the ProcessPlan combo box on the Job
form. The PartNumber combo box on the Job table uses that field to
filter the results to just the part numbers associated with that
ProcessPlan. There is a one-to-many relationship from tblPart to
tblJob, if that matters for this situation.

The thing I cannot figure out is what to do if the Process Plan does
not have any associated part numbers (this could happen because the
plan is for a category of part rather than a specific part number).
The SQL in that situation would be to select all of the parts from the
Parts table:
SELECT tblPart.PartID, tblPart.PartNum, tblPart.PartDescr
FROM tblPart
ORDER BY tblPart.PartNum;

How do I make it so that the row source is the first code above if the
Process Plan has associated part numbers, and the second if the Process
Plan has no associated part numbers?