Query with parameter from form control returns no records - HELPPLEASE!

[teddysnips]

This is driving me nuts!

Access 2003 FE/SQL Server 2000 BE

My client has asked me to provide a simple form to allow a run of
address labels to be printed off filtered by Customer Type. So I have
an unbound form with a "Print" button that will open a report based on
a query based on the Customer table.

The form has a single Combo Box to filter the correct companies
populated by the following SQL:

SELECT DISTINCT dbo_tblCustomerType.fldCustomerTypeID,
dbo_tblCustomerType.fldCustomerType & ' - ' &
dbo_tblCustomerType.fldTypeDescription AS CustomerType
FROM dbo_tblCompany INNER JOIN dbo_tblCustomerType ON
dbo_tblCompany.fldCustomerTypeID =
dbo_tblCustomerType.fldCustomerTypeID;

(It doesn't just use the CustomerType table in case there are any
Customer Types with no matching Customers)

The combo box has two columns, with widths 0cm and 5cm, bound to
Column 1 (fldCustomerTypeID).

To retrieve the correct set of addresses I have another query as
follows:

SELECT dbo_tblCompany.fldCustCode, dbo_tblCompany.fldCompanyName,
dbo_tblCompany.fldAddress1, dbo_tblCompany.fldAddress2,
dbo_tblCompany.fldAddress3, dbo_tblCompany.fldAddress4,
dbo_tblCompany.fldAddress5, dbo_tblCountry.fldCountry
FROM dbo_tblCountry INNER JOIN dbo_tblCompany ON
dbo_tblCountry.fldCountryID = dbo_tblCompany.fldCountryID
WHERE (((dbo_tblCompany.fldCustomerTypeID)=[Forms]![frmLabelPrinting]!
[cboCustomerType]))
ORDER BY dbo_tblCompany.fldCompanyName;

When I load the form and press the "Print" button, however, the report
is blank. This is because there's nothing in the query! In other
words, if I run the query as it is, it returns no records. If I put a
breakpoint in the Print button_click event and type in the Immediate
pane:

?[Forms]![frmLabelPrinting]![cboCustomerType]

It returns 1 (for the first row selected in the combo box)

If I now rerun the query above with

WHERE (((dbo_tblCompany.fldCustomerTypeID)=1))

instead of

WHERE (((dbo_tblCompany.fldCustomerTypeID)=[Forms]![frmLabelPrinting]!
[cboCustomerType]))

it returns all the rows I would expect.

In other words, the VBA can "see" the value of my combo box at
runtime, but the query cannot, even though I used the expression
builder in the Query Designer window to reference the control in the
correct way.

What am I doing wrong?

Thanks

Edward

 

[Dale Fye]

Teddy,

Try defining the parameter. You can do this one of two ways (through the
SQL view, or through the query design view). In SQL view, paste the
following line at the beginning of the SQL statement for the reports query:

Parameters [Forms]![frmLabelPrinting]![cboCustomerType] Long;

If you do this via the query design view, right click in the gray area where
the tables sit in the query. Select the Parameters option, then paste the
control reference in first row on the left, and select the Long datatype for
the column on the right.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

This is driving me nuts!

Access 2003 FE/SQL Server 2000 BE

My client has asked me to provide a simple form to allow a run of
address labels to be printed off filtered by Customer Type. So I have
an unbound form with a "Print" button that will open a report based on
a query based on the Customer table.

The form has a single Combo Box to filter the correct companies
populated by the following SQL:

SELECT DISTINCT dbo_tblCustomerType.fldCustomerTypeID,
dbo_tblCustomerType.fldCustomerType & ' - ' &
dbo_tblCustomerType.fldTypeDescription AS CustomerType
FROM dbo_tblCompany INNER JOIN dbo_tblCustomerType ON
dbo_tblCompany.fldCustomerTypeID =
dbo_tblCustomerType.fldCustomerTypeID;

(It doesn't just use the CustomerType table in case there are any
Customer Types with no matching Customers)

The combo box has two columns, with widths 0cm and 5cm, bound to
Column 1 (fldCustomerTypeID).

To retrieve the correct set of addresses I have another query as
follows:

SELECT dbo_tblCompany.fldCustCode, dbo_tblCompany.fldCompanyName,
dbo_tblCompany.fldAddress1, dbo_tblCompany.fldAddress2,
dbo_tblCompany.fldAddress3, dbo_tblCompany.fldAddress4,
dbo_tblCompany.fldAddress5, dbo_tblCountry.fldCountry
FROM dbo_tblCountry INNER JOIN dbo_tblCompany ON
dbo_tblCountry.fldCountryID = dbo_tblCompany.fldCountryID
WHERE (((dbo_tblCompany.fldCustomerTypeID)=[Forms]![frmLabelPrinting]!
[cboCustomerType]))
ORDER BY dbo_tblCompany.fldCompanyName;

When I load the form and press the "Print" button, however, the report
is blank. This is because there's nothing in the query! In other
words, if I run the query as it is, it returns no records. If I put a
breakpoint in the Print button_click event and type in the Immediate
pane:

?[Forms]![frmLabelPrinting]![cboCustomerType]

It returns 1 (for the first row selected in the combo box)

If I now rerun the query above with

WHERE (((dbo_tblCompany.fldCustomerTypeID)=1))

instead of

WHERE (((dbo_tblCompany.fldCustomerTypeID)=[Forms]![frmLabelPrinting]!
[cboCustomerType]))

it returns all the rows I would expect.

In other words, the VBA can "see" the value of my combo box at
runtime, but the query cannot, even though I used the expression
builder in the Query Designer window to reference the control in the
correct way.

What am I doing wrong?

Thanks

Edward

 

[teddysnips]

It's sometimes hard to see why there's an error. Something may look
good but...

Let's say I have
strSQL = "Select * From TableA " & _
"Where TableA.fldCustomerTypeID=[Forms]![frmLabelPrinting]!
[cboCustomerType]));"

I might then, underneath that code line, enter
Debug.print strSQL
and run the process.

Now I can go to the Immediate window, select the output from the Debug
statement, copy to clipboard, and then do a Query/New/Design...get to
the QBE, and select View/SQL and paste the SQL into it. Then attempt to
run that query. Most likely you'll find the error at this point.

I tried that - didn't work. I kludged it by putting a non-visible
text box on the form which was populated by the combo box's value in
the combo box's AfterUpdate event. The query then referenced

[Forms]![frmLabelPrinting]![txtHidden]

and it all worked fine. Don't know why it failed before, but never
mind.

Edward