J
Jon22
I'm trying to make the Row Source of a combo box named "Contact" be a list of
names based on the value in another combo box named "Customer" in the same
form named "Quotes New".
The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".
I have successfully created an SQL statement using the UNION thingy which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box Row
Source (which updates as the "Customer" Combo Box value changes) - that's all
fine. This Statement reads:
SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
I've managed to weed out the Contact fields that are null too (except for
one null line that remains in the drop down list which stumps me) as many of
the records from "Customers" only contain 1 or 2 Contacts.
What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with the
following statement:
SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];
But, the problem with this is, because of the [Number] field, I get a blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the [Contact1]
field, the drop down list displays this name at the top then 5 blank lines
then "Add New" at the bottom.
I can't work out how to display just the contacts that are not null then a
blank line then the text "Add New" in the order I want them.
names based on the value in another combo box named "Customer" in the same
form named "Quotes New".
The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".
I have successfully created an SQL statement using the UNION thingy which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box Row
Source (which updates as the "Customer" Combo Box value changes) - that's all
fine. This Statement reads:
SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
I've managed to weed out the Contact fields that are null too (except for
one null line that remains in the drop down list which stumps me) as many of
the records from "Customers" only contain 1 or 2 Contacts.
What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with the
following statement:
SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];
But, the problem with this is, because of the [Number] field, I get a blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the [Contact1]
field, the drop down list displays this name at the top then 5 blank lines
then "Add New" at the bottom.
I can't work out how to display just the contacts that are not null then a
blank line then the text "Add New" in the order I want them.