Cascading Combo and Listbox errors

M

Maureen227

I am setting up cascading combo box to listbox. The combo box is based
on a query with this sql
SELECT [Query for Cust List].[CustNum], [Query for Cust
List].[CustName] FROM [Query for Cust List];

The list box should display all the invoices for the customer number
chosen in the combo box.
The sql for the list box is
SELECT distinct JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION select distinct null, null
ORDER BY JobHeader.JobInvoice;

I get two difference error messages
When creating the list box based on a query with the above sql I get
the error.
"No value given for one or more required parameters"

If I paste the sql into the list box rowsoursce I get this message,
when I open the form.
"Query input must contain at least one table or query"

I have working cascading combo box on other forms using different value
the sql structure looks the same.

Any help would be appreciated
Maureen
 
D

Douglas J. Steele

Access insists that you list a table in the FROM clause, even when you're
only selecting constants. As well, your ORDER BY is inappropriate, since it
applies to the entire SELECT, not simply the one that includes JobHeader.
Finally, your first subselect has 4 fields, while your second subselect only
has 2.

See whether this works any better:

SELECT JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION
SELECT Null, Null, Null, Null FROM JobHeader
ORDER BY JobInvoice;

Note that the SELECT DISTINCT isn't required, since UNION eliminates
duplicates (use UNION ALL if you don't want to eliminate duplicates)
 
M

Maureen227

Doug
You SQL got rid of the error messages but the list box still doesn't
populate. Any suggestions?
Thanks ahead.
Maureen

Access insists that you list a table in the FROM clause, even when you're
only selecting constants. As well, your ORDER BY is inappropriate, since it
applies to the entire SELECT, not simply the one that includes JobHeader.
Finally, your first subselect has 4 fields, while your second subselect only
has 2.

See whether this works any better:

SELECT JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION
SELECT Null, Null, Null, Null FROM JobHeader
ORDER BY JobInvoice;

Note that the SELECT DISTINCT isn't required, since UNION eliminates
duplicates (use UNION ALL if you don't want to eliminate duplicates)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maureen227 said:
I am setting up cascading combo box to listbox. The combo box is based
on a query with this sql
SELECT [Query for Cust List].[CustNum], [Query for Cust
List].[CustName] FROM [Query for Cust List];

The list box should display all the invoices for the customer number
chosen in the combo box.
The sql for the list box is
SELECT distinct JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION select distinct null, null
ORDER BY JobHeader.JobInvoice;

I get two difference error messages
When creating the list box based on a query with the above sql I get
the error.
"No value given for one or more required parameters"

If I paste the sql into the list box rowsoursce I get this message,
when I open the form.
"Query input must contain at least one table or query"

I have working cascading combo box on other forms using different value
the sql structure looks the same.

Any help would be appreciated
Maureen
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Nothing at all, or one empty row? (I would expect one row from the SELECT
Null, Null, Null, Null FROM JobHeader row, even if the combo box isn't
returning what you think it is)

Assuming the latter, try putting in a statement for debugging purposes:

MsgBox [Forms]![Existing Customer List].[form]![cboCustList]

Is that the Customer Number you expect?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maureen227 said:
Doug
You SQL got rid of the error messages but the list box still doesn't
populate. Any suggestions?
Thanks ahead.
Maureen

Access insists that you list a table in the FROM clause, even when you're
only selecting constants. As well, your ORDER BY is inappropriate, since
it
applies to the entire SELECT, not simply the one that includes JobHeader.
Finally, your first subselect has 4 fields, while your second subselect
only
has 2.

See whether this works any better:

SELECT JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION
SELECT Null, Null, Null, Null FROM JobHeader
ORDER BY JobInvoice;

Note that the SELECT DISTINCT isn't required, since UNION eliminates
duplicates (use UNION ALL if you don't want to eliminate duplicates)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maureen227 said:
I am setting up cascading combo box to listbox. The combo box is based
on a query with this sql
SELECT [Query for Cust List].[CustNum], [Query for Cust
List].[CustName] FROM [Query for Cust List];

The list box should display all the invoices for the customer number
chosen in the combo box.
The sql for the list box is
SELECT distinct JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION select distinct null, null
ORDER BY JobHeader.JobInvoice;

I get two difference error messages
When creating the list box based on a query with the above sql I get
the error.
"No value given for one or more required parameters"

If I paste the sql into the list box rowsoursce I get this message,
when I open the form.
"Query input must contain at least one table or query"

I have working cascading combo box on other forms using different value
the sql structure looks the same.

Any help would be appreciated
Maureen
Click to expand...
Click to expand...
Click to expand...
 
M

Maureen227

Doug
FYI just so others can be reminded. When I rechecked my requery code
for the cascading of the list box the requery for the box was not being
done properly. Fixed it fixed problem

Maureen

Nothing at all, or one empty row? (I would expect one row from the SELECT
Null, Null, Null, Null FROM JobHeader row, even if the combo box isn't
returning what you think it is)

Assuming the latter, try putting in a statement for debugging purposes:

MsgBox [Forms]![Existing Customer List].[form]![cboCustList]

Is that the Customer Number you expect?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maureen227 said:
Doug
You SQL got rid of the error messages but the list box still doesn't
populate. Any suggestions?
Thanks ahead.
Maureen

Access insists that you list a table in the FROM clause, even when you're
only selecting constants. As well, your ORDER BY is inappropriate, since
it
applies to the entire SELECT, not simply the one that includes JobHeader.
Finally, your first subselect has 4 fields, while your second subselect
only
has 2.

See whether this works any better:

SELECT JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION
SELECT Null, Null, Null, Null FROM JobHeader
ORDER BY JobInvoice;

Note that the SELECT DISTINCT isn't required, since UNION eliminates
duplicates (use UNION ALL if you don't want to eliminate duplicates)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am setting up cascading combo box to listbox. The combo box is based
on a query with this sql
SELECT [Query for Cust List].[CustNum], [Query for Cust
List].[CustName] FROM [Query for Cust List];

The list box should display all the invoices for the customer number
chosen in the combo box.
The sql for the list box is
SELECT distinct JobHeader.JobInvoice, JobHeader.CustNum,
JobHeader.JobDescript, JobHeader.JobDate
FROM JobHeader
WHERE ((JobHeader.CustNum= [Forms]![Existing Customer
List].[form]![cboCustList]))
UNION select distinct null, null
ORDER BY JobHeader.JobInvoice;

I get two difference error messages
When creating the list box based on a query with the above sql I get
the error.
"No value given for one or more required parameters"

If I paste the sql into the list box rowsoursce I get this message,
when I open the form.
"Query input must contain at least one table or query"

I have working cascading combo box on other forms using different value
the sql structure looks the same.

Any help would be appreciated
Maureen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reverse Cascading Combo Box's ?? 8
Cascading Dependant combo boxes 0
Cascading Combo Boxes w/ SQL in a PRoject 3
Access Cannot select items in listbox 1
Form Problems with Combo Boxes 3
Cascading ComboBox on SubForm 6
cascading combo display is blank sometimes (Access 2007) 2
multiple cascading combo 3

Top