Selecting certain items for a report

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck
 
Hi ChuckW,

As always there are a number of options! You could use a list box and some
code but that may get complicated.

The easiest method would be to add a new field to the table (perhaps called
ReportRecord) that is a Yes/No data type.

Create a tabular form that includes the 'ReportRecord' field check box (or
a datasheet as a subform if you are familiar with such things and if you are
not too worried about colours - it's easier to use the keyboard to navigate
between records).
Create a query that only shows records from the table which have the
'ReportRecord' field set to Yes.
Create a report based on the query.
Create a button on your tabular form in the form header section
that opens the report (use the wizard if you're not sure about doing this).
The command button should be placed in the main form if you chose to create a
datasheet embedded in a subform.
Create an update query that sets the 'ReportRecord' field to No for all
records.
Make the query run at the appropriate time (perhaps attached to a command
button that has the Caption 'Reset'). You will need to turn warnings off to
prevent the default update message from appearing. Make sure, if this is done
in code, that you set warnings back on again (DoCmd.SetWarnings = True)
afterwards otherwise other error messages will be hidden. Resetting warnings
is not necessary in macros.

This is an approach rather than the specific code etc as the best method
depends on your current knowledge.

I hope this helps

accessjunky
 
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck
-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look at the
following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck
Click to expand...

.
Click to expand...
 
Hi Chuck,

Sorry about that - I didn't really give you enough information. Here's how I
do it - a little different than described in the articles but it works well
especially when the recordsource for the report is a complex query. Create a
new query that only pulls data from the product form - you only need to
include the primary key column from the table. Save it as
"tmpSelectProducts". For now it doesn't have any criteria - your code will
actually modify the criteria later. Join this new table to your recordsource
query for the report - this will allow the query to serve as a filter for
the report.

Now your code for opening the report will look something like this:





--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck said:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck
-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look
at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck
Click to expand...

.
Click to expand...
Click to expand...
 
Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The article
on MVPS.org implies that you need to use the resulting SQL as the
recordsource for your report.

Here's how I do it - a little different than described in the articles but
it works well especially when the recordsource for the report is a complex
query. Create a new query that only pulls data from the product form - you
only need to include the primary key column from the table. Save it as
"tmpSelectProducts". For now it doesn't have any criteria - your code will
actually modify the criteria later. Join this new table to your recordsource
query for the report - this will allow the query to serve as a filter for
the report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me debug and
doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary key. If you
use a text value for selecting records you need to wrap the value in quotes
when you build the Where clause. This is easily done by putting an extra
pair of double quote characters (") inside the strings on either side of the
&. Each embedded pair of quote characters will be replaced with a single
character which will remain in the final string. The outside pair are string
delimiters and aren't part of the actual literal string.

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR "


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck said:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck
-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look
at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck
Click to expand...

.
Click to expand...
Click to expand...
 
Sandra,

Thanks again for helping me. I still have some problems
though. I created the query called tmpSelectProducts
based on a table called Products. Here is the sql for it:

SELECT Products.ProductName, Products.Ingredients,
Products.Dosage, Products.Notes
FROM Products;

The form name that I created is called
SelectIngredients. I created the multiSelect list box
and called it List0 and also have a command button below
called Command2 based on the instructions from the
article. I then created a report called rptSelectProducts
and added the VBA code in which is listed below. Whenever
I try to run the report I get an error that brings up the
VBA code which says "User defined type not defined" and
highlights the Dim qdf as DAO.QueryDef line. Also the
code line StrWhere = StrWhere & "ProductName=""" &
appears in red. When I typed this out it gave me the
error "Compile Error: Expected Expression".

Any ideas on the VBA errors"

Thanks,

Chuck

----------------------------------------------------

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData (varItem) & """ OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) -
4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list
box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ProductName]='" _
& Me![List0].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True


End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "ProductReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
--------------------------------------------------------





-----Original Message-----
Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The article
on MVPS.org implies that you need to use the resulting SQL as the
recordsource for your report.

Here's how I do it - a little different than described in the articles but
it works well especially when the recordsource for the report is a complex
query. Create a new query that only pulls data from the product form - you
only need to include the primary key column from the table. Save it as
"tmpSelectProducts". For now it doesn't have any criteria - your code will
actually modify the criteria later. Join this new table to your recordsource
query for the report - this will allow the query to serve as a filter for
the report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me debug and
doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary key. If you
use a text value for selecting records you need to wrap the value in quotes
when you build the Where clause. This is easily done by putting an extra
pair of double quote characters (") inside the strings on either side of the
&. Each embedded pair of quote characters will be replaced with a single
character which will remain in the final string. The outside pair are string
delimiters and aren't part of the actual literal string.

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR "


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck said:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck
-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look
at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

ChuckW wrote:
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck

.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Chuck

The "User defined type not defined" is most likely due to a problem with
references. You need to have a reference to the "Microsoft DAO 3.6 object
library". From the VB Editor, click Tools -> References and make sure that
this library is checked.

The ""Compile Error: Expected Expression" error is because the following
should all be on one line:

StrWhere = StrWhere & "ProductName=""" & Me.lstSelectProducts.ItemData
(varItem) & """ OR "

The newsreader causes some confusing linebreaks so here it is with line
continuation characters (these lines can remain separate)

StrWhere = StrWhere & "ProductName=""" & _
Me.lstSelectProducts.ItemData (varItem) & """ OR "

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,

Thanks again for helping me. I still have some problems
though. I created the query called tmpSelectProducts
based on a table called Products. Here is the sql for it:

SELECT Products.ProductName, Products.Ingredients,
Products.Dosage, Products.Notes
FROM Products;

The form name that I created is called
SelectIngredients. I created the multiSelect list box
and called it List0 and also have a command button below
called Command2 based on the instructions from the
article. I then created a report called rptSelectProducts
and added the VBA code in which is listed below. Whenever
I try to run the report I get an error that brings up the
VBA code which says "User defined type not defined" and
highlights the Dim qdf as DAO.QueryDef line. Also the
code line StrWhere = StrWhere & "ProductName=""" &
appears in red. When I typed this out it gave me the
error "Compile Error: Expected Expression".

Any ideas on the VBA errors"

Thanks,

Chuck

----------------------------------------------------

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData (varItem) & """ OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) -
4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list
box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ProductName]='" _
& Me![List0].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True


End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "ProductReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
--------------------------------------------------------





-----Original Message-----
Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The
article on MVPS.org implies that you need to use the resulting SQL
as the recordsource for your report.

Here's how I do it - a little different than described in the
articles but it works well especially when the recordsource for the
report is a complex query. Create a new query that only pulls data
from the product form - you only need to include the primary key
column from the table. Save it as "tmpSelectProducts". For now it
doesn't have any criteria - your code will actually modify the
criteria later. Join this new table to your recordsource query for
the report - this will allow the query to serve as a filter for the
report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me debug
and doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary
key. If you use a text value for selecting records you need to wrap
the value in quotes when you build the Where clause. This is easily
done by putting an extra pair of double quote characters (") inside
the strings on either side of the &. Each embedded pair of quote
characters will be replaced with a single character which will
remain in the final string. The outside pair are string delimiters
and aren't part of the actual literal string.

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR "


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck said:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck

-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look
at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

ChuckW wrote:
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck

.
Click to expand...


.
Click to expand...
Click to expand...
 
Sandra,

Thanks. Your suggestions work and the form is
functioning very well.

Chuck
-----Original Message-----
Hi Chuck

The "User defined type not defined" is most likely due to a problem with
references. You need to have a reference to the "Microsoft DAO 3.6 object
library". From the VB Editor, click Tools -> References and make sure that
this library is checked.

The ""Compile Error: Expected Expression" error is because the following
should all be on one line:

StrWhere = StrWhere & "ProductName=""" & Me.lstSelectProducts.ItemData
(varItem) & """ OR "

The newsreader causes some confusing linebreaks so here it is with line
continuation characters (these lines can remain separate)

StrWhere = StrWhere & "ProductName=""" & _
Me.lstSelectProducts.ItemData (varItem) & """ OR "

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,

Thanks again for helping me. I still have some problems
though. I created the query called tmpSelectProducts
based on a table called Products. Here is the sql for it:

SELECT Products.ProductName, Products.Ingredients,
Products.Dosage, Products.Notes
FROM Products;

The form name that I created is called
SelectIngredients. I created the multiSelect list box
and called it List0 and also have a command button below
called Command2 based on the instructions from the
article. I then created a report called rptSelectProducts
and added the VBA code in which is listed below. Whenever
I try to run the report I get an error that brings up the
VBA code which says "User defined type not defined" and
highlights the Dim qdf as DAO.QueryDef line. Also the
code line StrWhere = StrWhere & "ProductName=""" &
appears in red. When I typed this out it gave me the
error "Compile Error: Expected Expression".

Any ideas on the VBA errors"

Thanks,

Chuck

----------------------------------------------------

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData (varItem) & """ OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) -
4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list
box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ProductName]='" _
& Me![List0].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True


End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "ProductReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
------------------------------------------------------- -





-----Original Message-----
Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The
article on MVPS.org implies that you need to use the resulting SQL
as the recordsource for your report.

Here's how I do it - a little different than described in the
articles but it works well especially when the recordsource for the
report is a complex query. Create a new query that only pulls data
from the product form - you only need to include the primary key
column from the table. Save it as "tmpSelectProducts". For now it
doesn't have any criteria - your code will actually modify the
criteria later. Join this new table to your recordsource query for
the report - this will allow the query to serve as a filter for the
report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me debug
and doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len (StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary
key. If you use a text value for selecting records you need to wrap
the value in quotes when you build the Where clause. This is easily
done by putting an extra pair of double quote characters (") inside
the strings on either side of the &. Each embedded pair of quote
characters will be replaced with a single character which will
remain in the final string. The outside pair are string delimiters
and aren't part of the actual literal string.

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR "


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck W wrote:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck

-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a look
at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

ChuckW wrote:
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck

.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Glad to hear it!

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck said:
Sandra,

Thanks. Your suggestions work and the form is
functioning very well.

Chuck
-----Original Message-----
Hi Chuck

The "User defined type not defined" is most likely due to a problem
with references. You need to have a reference to the "Microsoft DAO 3.6 object
library". From the VB Editor, click Tools -> References and make
sure that this library is checked.

The ""Compile Error: Expected Expression" error is because the
following should all be on one line:

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData (varItem) & """ OR "

The newsreader causes some confusing linebreaks so here it is with
line continuation characters (these lines can remain separate)

StrWhere = StrWhere & "ProductName=""" & _
Me.lstSelectProducts.ItemData (varItem) & """ OR "

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,

Thanks again for helping me. I still have some problems
though. I created the query called tmpSelectProducts
based on a table called Products. Here is the sql for it:

SELECT Products.ProductName, Products.Ingredients,
Products.Dosage, Products.Notes
FROM Products;

The form name that I created is called
SelectIngredients. I created the multiSelect list box
and called it List0 and also have a command button below
called Command2 based on the instructions from the
article. I then created a report called rptSelectProducts
and added the VBA code in which is listed below. Whenever
I try to run the report I get an error that brings up the
VBA code which says "User defined type not defined" and
highlights the Dim qdf as DAO.QueryDef line. Also the
code line StrWhere = StrWhere & "ProductName=""" &
appears in red. When I typed this out it gave me the
error "Compile Error: Expected Expression".

Any ideas on the VBA errors"

Thanks,

Chuck

----------------------------------------------------

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData (varItem) & """ OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len(StrWhere) -
4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list
box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ProductName]='" _
& Me![List0].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True


End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "ProductReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
------------------------------------------------------- -






-----Original Message-----
Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The
article on MVPS.org implies that you need to use the resulting SQL
as the recordsource for your report.

Here's how I do it - a little different than described in the
articles but it works well especially when the recordsource for the
report is a complex query. Create a new query that only pulls data
from the product form - you only need to include the primary key
column from the table. Save it as "tmpSelectProducts". For now it
doesn't have any criteria - your code will actually modify the
criteria later. Join this new table to your recordsource query for
the report - this will allow the query to serve as a filter for the
report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me
debug and doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
StrWhere = "Where " & Left(StrWhere, Len (StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary
key. If you use a text value for selecting records you need to wrap
the value in quotes when you build the Where clause. This is easily
done by putting an extra pair of double quote characters (") inside
the strings on either side of the &. Each embedded pair of quote
characters will be replaced with a single character which will
remain in the final string. The outside pair are string delimiters
and aren't part of the actual literal string.

StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR "


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Chuck W wrote:
Sandra,

Thanks for your help. I went through the directions and
created a form on the northwind database. It stops where
you create a form with the multiselect list box and a
command button. When you select some items from the list
box and click the command button, It shows up at the
bottom as saying "1 of 4 (which is how many records I
selected) and then "Filtered". What I really want to do
is to create a report of information contained in the
four records I just selected. How would I add this next
step?

Thanks,

Chuck

-----Original Message-----
Hi Chuck,

A Multiselect listbox is ideal for this type of thing. Take a
look at the following articles -

http://www.mvps.org/access/forms/frm0007.htm

How to Use a Multi-Select List Box to Filter a Form
http://support.microsoft.com/default.aspx?scid=kb; [LN];135546


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

ChuckW wrote:
Hi,

I have a table called products that has four fields:
ProductName, Ingredients, Dosage, and Notes. There are
about 250 products. I want to create an application that
allows a person to select anywhere from 3 to about 10
products out of these 250 and have these selected
products appear on a report. I can create either radio
boxes or drop downs but am not sure which to use. Can
anyone help me get started with doing this?

Thanks,

Chuck

.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top