select child records from list box to filter report

J

jlute

I'm using a form to filter and run a report. The form has a combobox
and a
listbox. The combobox displays parent records and the list box
displays child
records. I'm trying to use the listbox to select particular child
records to
display in the report. This is very challenging for me and I don't
understand
all of Allen Browne's code nor the overall design.

Here's what I currently have...

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, tblProfiles.Type 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_Se­lect].[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

Clicking the Preview button opens the report however every control is
either
blank or contains #Error. I placed a textbox called OpenArgs in the
report
and it properly displays the selected IDs.

I'm completely confused by all of the Dim lines.

Can anyone help? I've reached the end of my brain's capability!

Thanks!
 
B

Beetle

Based on what you said you are trying to accomplish, I don't see why
you even need some of that code. Something like the following should
work;

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "rptPKWeightCalculatorASSsFGs_Select"
strWhere = "" 'start with a zero length string

'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.
If strWhere = "" Then
strWhere = strDelim & .ItemData(varItem) _
& strDelim
Else
strWhere = strWhere & "," & strDelim _
& .ItemData(varItem) & strDelim
End If
End If
Next
End With

If strWhere <> "" Then
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:="[PKWTID] IN (" & strWhere & ")"
Else
MsgBox "No items selected"
End If

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

--
_________

Sean Bailey


[email protected] said:
I'm using a form to filter and run a report. The form has a combobox
and a
listbox. The combobox displays parent records and the list box
displays child
records. I'm trying to use the listbox to select particular child
records to
display in the report. This is very challenging for me and I don't
understand
all of Allen Browne's code nor the overall design.

Here's what I currently have...

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, tblProfiles.Type 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_Se­lect].[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

Clicking the Preview button opens the report however every control is
either
blank or contains #Error. I placed a textbox called OpenArgs in the
report
and it properly displays the selected IDs.

I'm completely confused by all of the Dim lines.

Can anyone help? I've reached the end of my brain's capability!

Thanks!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WHERE help with preview button event 1
WHERE help for button code 5
Two Multi-Select List Boxes as Filters 11
Two multi-select list boxes 3
Multi-Select in List Box 4
Create Report On the Fly 1
Error 13 - Type Mismatch 5
Open a report based on values from Two List boxes value 2

Top