T
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
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