Union to populate cbo2 based on cbo1

[Guest]

Hi -

I have three combo boxes -
1. Company
2. Division
3. Contact
- on my form.
When a company is selected, all the divisions of that company populate the
"Division" combobox, and when a division is selected, the same occurs with
the "Contact" combobox, to show all the contacts that are assigned to that
specific division.
I have this process working correctly.

I used a Union qry to add "(Company Not Listed)" to the top of the Company
cbo for the user to select if the company they are looking for isn't listed.
If the user selects "(Company Not Listed)", a form opens to allow them to add
a new company.
The code for this is:
If Me.cboBusDevCompany.Column(1) = "(Company Not Listed)" Then
DoCmd.OpenForm "frmCompanyAddNew", acNormal, , , acFormAdd
End If
(The form opens as it should at this point.)

My problem is this:
The "(Not Listed)" option should appear in all three cbos
(Company/Division/Contact). It works correctly in the company cbo. But, when
I attempted to add it to the division cbo, it doesn't show anything.
(Meaning: no data is shown at all, no text, just a blank box)

Source code for Company cbo (which works):
SELECT tblCompany.CompID, tblCompany.CompName FROM tblCompany UNION Select 0
as NotListedChoice, "(Company Not Listed)" as Bogus from tblCompany;

Source code for Division cbo (which doesn't work):
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION Select 0 as NotListedChoice, "(Division Not Listed)" as
Bogus from tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

Current source code for Contact cbo (which I haven't modified yet):
SELECT tblContact.ContactID, tblContact.ContactDivision, [ContactLastName] &
", " & [ContactFirstName] AS ContactName FROM tblContact WHERE
(((tblContact.ContactDivision)=Forms!frmBusDev!cboBusDevDivision)) ORDER BY
tblContact.ContactLastName;

I'm assuming this problem has to do with the way I've formed the Union qry
or the fact that two of the combo boxes are based on the one before.

Additionally, after the user clicks the "Not Listed" option, and then adds a
new company/division or contact, I want the new/updated info to appear in the
cbo. Where should I include requery?

I would really appreciate help in figuring out how to get this to work. If
you need more info, please let me know.
Thanks!

 

[Guest]

Hi Jenna B,

To get the not listed option in your subsequent text boxes, you need a union
query. The first part of your query looks alright, just union that with an
item for (Division Not Listed).

Same for the Contact Not Listed.

As far as requerying goes... since you are opening a new form to populate
data, you could have the query on the close event of that form, or when the
user clicks the save or close button. If the form is used elsewhere, you
might want to pass a parameter to the form that indicates that you require a
requery.

Hope this helps.

Damian.

Hi -

I have three combo boxes -
1. Company
2. Division
3. Contact
- on my form.
When a company is selected, all the divisions of that company populate the
"Division" combobox, and when a division is selected, the same occurs with
the "Contact" combobox, to show all the contacts that are assigned to that
specific division.
I have this process working correctly.

I used a Union qry to add "(Company Not Listed)" to the top of the Company
cbo for the user to select if the company they are looking for isn't listed.
If the user selects "(Company Not Listed)", a form opens to allow them to add
a new company.
The code for this is:
If Me.cboBusDevCompany.Column(1) = "(Company Not Listed)" Then
DoCmd.OpenForm "frmCompanyAddNew", acNormal, , , acFormAdd
End If
(The form opens as it should at this point.)

My problem is this:
The "(Not Listed)" option should appear in all three cbos
(Company/Division/Contact). It works correctly in the company cbo. But, when
I attempted to add it to the division cbo, it doesn't show anything.
(Meaning: no data is shown at all, no text, just a blank box)

Source code for Company cbo (which works):
SELECT tblCompany.CompID, tblCompany.CompName FROM tblCompany UNION Select 0
as NotListedChoice, "(Company Not Listed)" as Bogus from tblCompany;

Source code for Division cbo (which doesn't work):
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION Select 0 as NotListedChoice, "(Division Not Listed)" as
Bogus from tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

Current source code for Contact cbo (which I haven't modified yet):
SELECT tblContact.ContactID, tblContact.ContactDivision, [ContactLastName] &
", " & [ContactFirstName] AS ContactName FROM tblContact WHERE
(((tblContact.ContactDivision)=Forms!frmBusDev!cboBusDevDivision)) ORDER BY
tblContact.ContactLastName;

I'm assuming this problem has to do with the way I've formed the Union qry
or the fact that two of the combo boxes are based on the one before.

Additionally, after the user clicks the "Not Listed" option, and then adds a
new company/division or contact, I want the new/updated info to appear in the
cbo. Where should I include requery?

I would really appreciate help in figuring out how to get this to work. If
you need more info, please let me know.
Thanks!

 

[Guest]

"To get the not listed option in your subsequent text boxes, you need a
union query. The first part of your query looks alright, just union that
with an item for (Division Not Listed)."

Damian,

I did use a union qry. The Company one works.
It's the Division one that I'm having trouble with.
Could you be a little more specific in your help?

This is the one that I need help with at the moment:
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION Select 0 as NotListedChoice, "(Division Not Listed)" as
Bogus from tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

I'm not sure if the placement of the union is correct, or if the order even
matters in this SQL statement. I changed it to:
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) UNION Select
0 as NotListedChoice, "(Division Not Listed)" as Bogus from tblDivision ORDER
BY tblDivision.DivName;

It still doesn't work. The 2nd combo box (based on the first - Company)
still shows no data at all. When I remove the union part of the statement, it
works fine, but obviously doesn't fit my need for the (Division Not Listed)
text.

Thanks again for your help.

- Jenna

Hi Jenna B,

To get the not listed option in your subsequent text boxes, you need a union
query. The first part of your query looks alright, just union that with an
item for (Division Not Listed).

Same for the Contact Not Listed.

As far as requerying goes... since you are opening a new form to populate
data, you could have the query on the close event of that form, or when the
user clicks the save or close button. If the form is used elsewhere, you
might want to pass a parameter to the form that indicates that you require a
requery.

Hope this helps.

Damian.

Hi -

I have three combo boxes -
1. Company
2. Division
3. Contact
- on my form.
When a company is selected, all the divisions of that company populate the
"Division" combobox, and when a division is selected, the same occurs with
the "Contact" combobox, to show all the contacts that are assigned to that
specific division.
I have this process working correctly.

I used a Union qry to add "(Company Not Listed)" to the top of the Company
cbo for the user to select if the company they are looking for isn't listed.
If the user selects "(Company Not Listed)", a form opens to allow them to add
a new company.
The code for this is:
If Me.cboBusDevCompany.Column(1) = "(Company Not Listed)" Then
DoCmd.OpenForm "frmCompanyAddNew", acNormal, , , acFormAdd
End If
(The form opens as it should at this point.)

My problem is this:
The "(Not Listed)" option should appear in all three cbos
(Company/Division/Contact). It works correctly in the company cbo. But, when
I attempted to add it to the division cbo, it doesn't show anything.
(Meaning: no data is shown at all, no text, just a blank box)

Source code for Company cbo (which works):
SELECT tblCompany.CompID, tblCompany.CompName FROM tblCompany UNION Select 0
as NotListedChoice, "(Company Not Listed)" as Bogus from tblCompany;

Source code for Division cbo (which doesn't work):
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION Select 0 as NotListedChoice, "(Division Not Listed)" as
Bogus from tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

Current source code for Contact cbo (which I haven't modified yet):
SELECT tblContact.ContactID, tblContact.ContactDivision, [ContactLastName] &
", " & [ContactFirstName] AS ContactName FROM tblContact WHERE
(((tblContact.ContactDivision)=Forms!frmBusDev!cboBusDevDivision)) ORDER BY
tblContact.ContactLastName;

I'm assuming this problem has to do with the way I've formed the Union qry
or the fact that two of the combo boxes are based on the one before.

Additionally, after the user clicks the "Not Listed" option, and then adds a
new company/division or contact, I want the new/updated info to appear in the
cbo. Where should I include requery?

I would really appreciate help in figuring out how to get this to work. If
you need more info, please let me know.
Thanks!

 

[Guest]

Note: When I add the following SQL statement to the row source of the
Division cbo:
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) UNION Select
0 as NotListedChoice, "(Division Not Listed)" as Bogus from tblDivision ORDER
BY tblDivision.DivName;

- the error that I get is this:
"The number of columns in the two selected tables or queries of a union
query do not match. (Error 3307)
The two tables or queries joined by the UNION operation must generate the
same number of columns. Remove columns from the SELECT statement that has too
many columns or include more columns in the SELECT statement that has too
few."

 

[pietlinden]

hmm... error message says it all. you can't union two
union-incompatible sets, e.g, two sets with different numbers of
columns returned. You need to alias those

Try something like:

SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID
FROM tblDivision
UNION
SELECT 0 as DivID, "(Division Not Listed)" as DivName, "" as
DivCompanyID
FROM tblDivision
WHERE (((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany))
ORDER BY tblDivision.DivName;

 

[Guest]

That works as far as showing the "(Division Not Listed)" text.
But, now my second combo box shows ALL the divisions, and not just the ones
listed for the specified company chosen in cbo1 (Company).

Here's the statement:
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION SELECT 0 as DivID, "(Division Not Listed)" as DivName, ""
as DivCompanyID FROM tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

Must be a way to fix this. I appreciate your help.

 

[John Vinson]

That works as far as showing the "(Division Not Listed)" text.
But, now my second combo box shows ALL the divisions, and not just the ones
listed for the specified company chosen in cbo1 (Company).

Here's the statement:
SELECT tblDivision.DivID, tblDivision.DivName, tblDivision.DivCompanyID FROM
tblDivision UNION SELECT 0 as DivID, "(Division Not Listed)" as DivName, ""
as DivCompanyID FROM tblDivision WHERE
(((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany)) ORDER BY
tblDivision.DivName;

That's because you're applying the criterion to the SECOND SELECT
clause in the Union - the one for (Division Not Listed). Each SELECT
in a UNION query is an independent query, with its own criteria.

Try

SELECT tblDivision.DivID, tblDivision.DivName,
tblDivision.DivCompanyID FROM
tblDivision
WHERE (((tblDivision.DivCompanyID)=Forms!frmBusDev!cboBusDevCompany))
UNION
SELECT 0 as DivID, "(Division Not Listed)" as DivName, ""
as DivCompanyID FROM tblDivision
ORDER BY DivName;

This assumes that DivCompanyID is a Text field.

John W. Vinson[MVP]

 

[Guest]

John - Thanks so much for your help. That worked perfectly!

I also modified it to use on the third cbo:
SELECT tblContact.ContactID, tblContact.ContactDivision, [ContactLastName] &
", " & [ContactFirstName] AS ContactName FROM tblContact WHERE
(((tblContact.ContactDivision)=Forms!frmBusDev!cboBusDevDivision)) UNION
SELECT 0 as ContactID, "" AS ContactDivision, "(Contact Not Listed)" AS
ContactName FROM tblContact ORDER BY ContactName;

With appreciation and thanks,

Jenna