parameter query

On Jun 27, 11:52*am, "Allen Browne"
wrote:
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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...

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

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!

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!