parameter query

[jlute]

I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349


I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!

 

[Allen Browne]

No: you can't do that John. A parameter can only be a value; it cannot be a
mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[Michel Walsh]

There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"

(note there is no space after the comas)


And to use the query:

.... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")


ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter is
the pattern, at the right, of the operator LIKE).


Vanderghast, Access MVP

 

[Allen Browne]

Michel, that's just too clever.

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.


Thanks for posting: you've provided my fun for this evening.

 

[jlute]

Thanks, Michel! I was just starting to get a grip on Allen's
suggestion but now that he's enamored with yours I'm torn!

I don't know that I fully understand your suggestion. Do I still
create a form with a listbox to select the records...? I'm a bit
lost.

A possible one is to pass the parameter:  "12345,12348,12349"

Like passing a kidney stone?

And to use the query:

... WHERE ("," & parameter & ",")  LIKE ("*," & id  & ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE..
(Generally we do the reverse, the field is at the left and the parameter is
the pattern, at the right, of the operator LIKE).

So I create a field in my query...yikes...I'm really lost here!

 

[Michel Walsh]

You can use a list box with multiple selections possible, or just ask for
the 'string' (less friendly). If you use a list box, you will have to run
over its Selected items collection to get make the said string. That
'string' is then a standard parameter for a standard Jet query with a
parameter (or for a stored procedure with MS SQL Server). Assuming you use
Jet, then you can write your query with the syntax:


.... WHERE "," &
FORMS!FormName!ControlNameHavingTheConcatenatedStringUsedAsParameterHere &
"," LIKE "*," & yourFieldNameHere & ",*"



That assumes your query will be launched from the User Interface ( or from
DoCmd, or as RecordSource of another form/report) , with the implied form
already open, and its implied text control, filled (the form or the control
can be invisible, but must be open).



Vanderghast, Access MVP



Thanks, Michel! I was just starting to get a grip on Allen's
suggestion but now that he's enamored with yours I'm torn!

I don't know that I fully understand your suggestion. Do I still
create a form with a listbox to select the records...? I'm a bit
lost.

A possible one is to pass the parameter: "12345,12348,12349"

Like passing a kidney stone?

And to use the query:

... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is
the pattern, at the right, of the operator LIKE).

So I create a field in my query...yikes...I'm really lost here!

 

[jlute]

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Michel, that's just too clever.  

To handle the spaces a user might enter:
    PARAMETERS WotNum Text ( 255 );
    SELECT ClientNum
    FROM tblClient
    WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

There are some 'way around', but that can be slow.

A possible one is to pass the parameter:  "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",")  LIKE ("*," & id  & ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

- Show quoted text -

 

[Allen Browne]

Not really clear about that, John.

You have a form with:
- a combo
- a list box
- a subform

I'm not clear about how these elements are tied together. Is the list box
multi-select? How is it influenced by the combo? How does it affect the
subform? Is it the subform's filter that you want to apply to your report?
Are the criteria in the query, or are they in the filter of the form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Michel, that's just too clever.

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of
LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

- Show quoted text -

 

[jlute]

Thanks, Allen!

Not really clear about that, John.

You have a form with:
- a combo
- a list box
- a subform

Not exactly. No subform.
cbPKWTID:
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

lstFGID:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Report:
SELECT tblProfiles.txtProfileID FROM tblProfiles; (more than this but
I chopped for simplicity)
The subreport of this report has the query behind it that's filtered
by the listbox.

Therefore, the report needs to open where txtProfileID = cbPKWTID and
the subreport needs to return the filtered child records. Hope that
helps! Knowing this doesn't help me much!

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the
list box.
    'Author:   Allen J Browne, 2004.  http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'rptPKWeightCalculatorASSsFGs..

    'strDelim = """"            'Delimiter appropriate to field type.
See note 1.
    strDoc = "Associated Finished Goods"

    'Loop through the ItemsSelected in the list box.
    With Me.lstFGID
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
                'Build up the description from the text in the visible
column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
            End If
        Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets..
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
        End If
    End If

    'Report will not filter if open, so close it. For Access 97, see
note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If

    'Omit the last argument for Access 2000 and earlier. See note
4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

Michel, that's just too clever.

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Allen Browne]

You have a form named frmQueryPKWTCalcsFGs.

It has a combo named cbPKWTID, with RowSource:
SELECT tblProfiles.txtProfileID,
tblProfiles.Version,
tblProfiles.Description
FROM tblProfiles
WHERE tblProfiles.Type = "PKCALC")
ORDER BY tblProfiles.txtProfileID;

It also has a listbox named lstFGID, with RowSource:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description
FROM tblProfiles INNER JOIN tblPKProfilesAssociations
ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations
WHERE tblPKProfilesAssociations.txtProfileID =
[Forms]![frmQueryPKWTCalcsFGs].[Form]![cbPKWTID]
ORDER BY tblPKProfilesAssociations.ProfilesAssociations;

You open a report filtered by the combo.
The report has a subreport.
You want the subreport to show:
a) ALL records in the list box? or
b) just the SELECTED records in the mulit-select list box?

If (a), just include the combo's bound field (txtProfileID) in the
LinkMasterFields of the subreport control, with the matching field from the
subreport in LinkChildFields.

If (b), there are a few options:
1. The simplest would be to combine the subreport and main report into one,
so you can easily pass the WhereConditon string from the function you
already have.

2. It would be possible to write a VBA function that returns TRUE if the
current value is selected in the list box, else false. You call the function
in the WHERE clause of the subreport's query, passing in the ID to be
tested. This kind of thing:
Public Function IncludeProfile(ProfileID AS Variant) AS Boolean
and call it:
WHERE IncludeProfile([txtProfileID])

3. If those approaches don't work, you can build the SQL string for the
subreport's query, and assign it to the SQL property of the QueryDef:
CurrentDb.QueryDefs("Sub1Query").SQL = "SELECT ...

HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen!

Not really clear about that, John.

You have a form with:
- a combo
- a list box
- a subform

Not exactly. No subform.
cbPKWTID:
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

lstFGID:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Report:
SELECT tblProfiles.txtProfileID FROM tblProfiles; (more than this but
I chopped for simplicity)
The subreport of this report has the query behind it that's filtered
by the listbox.

Therefore, the report needs to open where txtProfileID = cbPKWTID and
the subreport needs to return the filtered child records. Hope that
helps! Knowing this doesn't help me much!

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Michel, that's just too clever.

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[jlute]

You have a form named frmQueryPKWTCalcsFGs.

It has a combo named cbPKWTID, with RowSource:
    SELECT tblProfiles.txtProfileID,
    tblProfiles.Version,
    tblProfiles.Description
    FROM tblProfiles
    WHERE tblProfiles.Type = "PKCALC")
    ORDER BY tblProfiles.txtProfileID;

It also has a listbox named lstFGID, with RowSource:
    SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
    tblProfiles.Description
    FROM tblProfiles INNER JOIN tblPKProfilesAssociations
      ON tblProfiles.txtProfileID =
      tblPKProfilesAssociations.ProfilesAssociations
    WHERE tblPKProfilesAssociations.txtProfileID =
      [Forms]![frmQueryPKWTCalcsFGs].[Form]![cbPKWTID]
    ORDER BY tblPKProfilesAssociations.ProfilesAssociations;

You open a report filtered by the combo.
The report has a subreport.
You want the subreport to show:
a) ALL records in the list box? or
b) just the SELECTED records in the mulit-select list box?

If (b), there are a few options:
1. The simplest would be to combine the subreport and main report into one,
so you can easily pass the WhereConditon string from the function you
already have.

Hi, Allen. Well, I've been pecking at this a little this morning. The
above is true (although I've made a couple design changes that really
don't affect anything and I don't want to post them so as to not add
any confusion). I followed your advice above regarding combining the
report and subreport.

Here's my button event:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "[PKWTID] = """ & _
Forms![frmQueryPKWTCalcsFGs]![PKWTID] & """"
DoCmd.OpenReport "rptPKWeightCalculatorASSsFGs", acPreview _
, , strWhere

Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub

This properly returns ALL records in the list box. Now comes the
tricky part that is still way over my head. So I need to plug in the
following code into the button? If I'm on the right track could you
possibly put it into Access for Dummies terms? Thanks!!!

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Allen Browne]

In the first code, strWhere is set to just one field.

In the 2nd code, you need to generate something like:
strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")"

You will do that by looping through the ItemsSelected in the list box, as in
the example.

I'm going to have to let you put it together and test it, John.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Here's my button event:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "[PKWTID] = """ & _
Forms![frmQueryPKWTCalcsFGs]![PKWTID] & """"
DoCmd.OpenReport "rptPKWeightCalculatorASSsFGs", acPreview _
, , strWhere

Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub

This properly returns ALL records in the list box. Now comes the
tricky part that is still way over my head. So I need to plug in the
following code into the button? If I'm on the right track could you
possibly put it into Access for Dummies terms? Thanks!!!

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[jlute]

Thanks for the support, Allen!

In the first code, strWhere is set to just one field.

I'm not sure what you mean by this. Are you referring to:
'Build up the filter from the bound column.
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","

In the 2nd code, you need to generate something like:
strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")"

I'm completely thrown by this, too. Sorry, I'm just not follwing all
of this as my skills are obviously lacking!

Allow me to run through the current design, etc.

I'm using Access 2003.

Form:
frmQueryPKWTCalcsFGs_Select
SELECT tblProfiles.txtProfileID AS PKWTID, tblProfiles.Description,
tblProfiles.Type FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC"));

Combobox:
cbPKWTID
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

Listbox:
lstFGID
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs_Select].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Preview button:
Private Sub cmdPreview_Click()

Dim i As Integer
Dim strForm As String

For i = 1 To CurrentProject.AllForms.Count
If CurrentProject.AllForms(i - 1).IsLoaded Then
strForm = CurrentProject.AllForms(i - 1).Name
If strForm <> "frmQueryPKWTCalcsFGs_Select" And strForm <>
"Marzetti Main Menu" Then
DoCmd.Close acForm, strForm, acSaveNo
End If
End If
Next i

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strFGID As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
strDelim = """"
strDoc = "rptPKWeightCalculatorASSsFGs_Select"
'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column.
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the FGIDs from the text in the FGIDs column.
strFGID = strFGID & """" & .Column(0, varItem) & """,
"
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[PKWTID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strFGID = "FGIDs: " & Left$(strFGID, lngLen)
End If
End If
DoCmd.OpenReport strDoc, acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strFGID
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

I'm sure I've butchered the button code and it's not clear to me at
all what I've done wrong however two things happen:
- If I make a selection in the listbox the report opens BUT every
field is either blank or contains #Error. I placed a textbox called
OpenArgs (and if you're familiar with JOURNEY's 1980's hit Open Arms
you MUST chuckle) in the report and it properly displays the selected
IDs.
- If I leave the listbox blank the report opens and returns every
PKWTID and all of their associated FGIDs.

Do you see at all where I've goofed?

Thanks again!

 

[jlute]

Thanks for the support, Allen!

In the first code, strWhere is set to just one field.

I'm not sure what you mean by this. Are you referring to:
'Build up the filter from the bound column.
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","

In the 2nd code, you need to generate something like:
strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")"

I'm completely thrown by this, too. Sorry, I'm just not follwing all
of this as my skills are obviously lacking!

Allow me to run through the current design, etc.

I'm using Access 2003.

Form:
frmQueryPKWTCalcsFGs_Select
SELECT tblProfiles.txtProfileID AS PKWTID, tblProfiles.Description,
tblProfiles.Type FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC"));

Combobox:
cbPKWTID
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

Listbox:
lstFGID
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs_Select].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Preview button:
Private Sub cmdPreview_Click()

Dim i As Integer
Dim strForm As String

For i = 1 To CurrentProject.AllForms.Count
If CurrentProject.AllForms(i - 1).IsLoaded Then
strForm = CurrentProject.AllForms(i - 1).Name
If strForm <> "frmQueryPKWTCalcsFGs_Select" And strForm <>
"Marzetti Main Menu" Then
DoCmd.Close acForm, strForm, acSaveNo
End If
End If
Next i

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strFGID As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
strDelim = """"
strDoc = "rptPKWeightCalculatorASSsFGs_Select"
'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column.
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the FGIDs from the text in the FGIDs column.
strFGID = strFGID & """" & .Column(0, varItem) & """,
"
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[PKWTID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strFGID = "FGIDs: " & Left$(strFGID, lngLen)
End If
End If
DoCmd.OpenReport strDoc, acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strFGID
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

I'm sure I've butchered the button code and it's not clear to me at
all what I've done wrong however two things happen:
- If I make a selection in the listbox the report opens BUT every
field is either blank or contains #Error. I placed a textbox called
OpenArgs (and if you're familiar with JOURNEY's 1980's hit Open Arms
you MUST chuckle) in the report and it properly displays the selected
IDs.
- If I do NOT make a selection in the listbox the report opens and
returns every PKWTID and all of their associated FGIDs.

Do you see at all where I've goofed?

Thanks again!