Using form as criteria for report query

[Guest]

Hello,
I have been attempting to use a form to define criteria for a query which is
used as the source for a report. In the query I have the criteria for column
"company" set to "[Forms]![Select company]![Company]". In the form, I have a
list box which is bound to another table "tblCompany" which has a list of all
of the companies that are available in my DB.

When I run the report, the form comes up, I can select 1 or more companies,
and then I can click on the OK button. The report then displays but the
report is empty (no records). If I set the query criteria to prompt the user
for the company (i.e. "[Enter company]" the report works fine. This tells me
that the value I am selecting in the form isn't getting passed into the query.

Any help would be appreciated...

 

[Joseph Meehan]

Hello,
I have been attempting to use a form to define criteria for a query
which is used as the source for a report. In the query I have the
criteria for column "company" set to "[Forms]![Select
company]![Company]". In the form, I have a list box which is bound to
another table "tblCompany" which has a list of all of the companies
that are available in my DB.

When I run the report, the form comes up, I can select 1 or more
companies, and then I can click on the OK button. The report then
displays but the report is empty (no records). If I set the query
criteria to prompt the user for the company (i.e. "[Enter company]"
the report works fine. This tells me that the value I am selecting in
the form isn't getting passed into the query.

Any help would be appreciated...

I have not tried it your way, but I have used a form to select records
by setting a binary select field to yes and then printing a report from a
query with its filter set to selectfield = Yes (-1). That worked for me. I
also had a procedure to reset all the the records to the No state after I
indicated that the printing was complete.

 

[Guest]

Hi Stepney,

In order to use a list box in this manner, you will need to set the Multi
Select property, on the Other tab, to None. In order to allow multiple
selections, you'll need to write VBA code to iterate the .ItemsSelected
property of the listbox. Are you comfortable using VBA code, or, are you
willing to "muddle your way" through it with an example? If you send me a
private e-mail message, I'll be happy to send you a sample.

Tom
AOS168 AT comcast dot net
________________________________

:

Hello,
I have been attempting to use a form to define criteria for a query which is
used as the source for a report. In the query I have the criteria for column
"company" set to "[Forms]![Select company]![Company]". In the form, I have a
list box which is bound to another table "tblCompany" which has a list of all
of the companies that are available in my DB.

When I run the report, the form comes up, I can select 1 or more companies,
and then I can click on the OK button. The report then displays but the
report is empty (no records). If I set the query criteria to prompt the user
for the company (i.e. "[Enter company]" the report works fine. This tells me
that the value I am selecting in the form isn't getting passed into the query.

Any help would be appreciated...

 

[Guest]

Tom,

Before I saw your reply, and out of frustration, I set the list box to
accept only 1 selection and voila, it worked. So you are right, I have to
parse out the list somehow. I am weak in VBA and will accept your gracious
offer to help me out offline.

 

[Guest]

I, too, am frustrated with the use of multi-selection box as query criteria.
What good is being able to select several items if you have to write
extensive code to use them? OK, so much for venting.

I am still trying to muddle my way through how to use this code and get it
to work with the selection criteria. I don't think I have either correct.
To test my code, I tried to print the string, but I got an error that said
that the entity wasn't supported for that function. I now don't know whether
I don't have a string or whether I'm using the print statement wrong.

My ultimate goal is to transfer information from a multiselect list box on a
form to use as criteria in a query. Here's what I have put together so far:
(The form is 'D6ReportForm', the multilistbox is 'Categories' with 'Category'
the bound field, The control the code is embedded in is called 'Choose'.)

Private Sub Choose_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!D6ReportForm
Set ctl = frm!Categories
For Each varItem In ctl.ItemsSelected
strSQL = ctl.ItemData(varItem) & ", "
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 2)
Print [strSQL]
End Sub

The query criteria is currently set as:

[Forms]![D6ReportForm]![Choose].[strSQL]

I am not a programmer. Can somebody help me get this straight?

Thanks,

Joan

Hello,
I have been attempting to use a form to define criteria for a query
which is used as the source for a report. In the query I have the
criteria for column "company" set to "[Forms]![Select
company]![Company]". In the form, I have a list box which is bound to
another table "tblCompany" which has a list of all of the companies
that are available in my DB.

When I run the report, the form comes up, I can select 1 or more
companies, and then I can click on the OK button. The report then
displays but the report is empty (no records). If I set the query
criteria to prompt the user for the company (i.e. "[Enter company]"
the report works fine. This tells me that the value I am selecting in
the form isn't getting passed into the query.

Any help would be appreciated...

I have not tried it your way, but I have used a form to select records
by setting a binary select field to yes and then printing a report from a
query with its filter set to selectfield = Yes (-1). That worked for me. I
also had a procedure to reset all the the records to the No state after I
indicated that the printing was complete.