WHERE help with preview button event

[jlute]

Thanks in advance for your help. I posted this in the queries forum
but thought it more appropriate here.

Two things happen when I click my preview button:
- 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.


I'm fairly sure that that this is a WHERE problem but I'm lost as to
how to correct it. The button code follows but first a little design
info.

I'm using Access 2003. The button is on the following form that has
the following controls.

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

 

[strive4peace]

please do not post the same question in multiple newsgroups; this is
discussed in microsoft.public.access.queries

thank you

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks in advance for your help. I posted this in the queries forum
but thought it more appropriate here.

Two things happen when I click my preview button:
- 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.


I'm fairly sure that that this is a WHERE problem but I'm lost as to
how to correct it. The button code follows but first a little design
info.

I'm using Access 2003. The button is on the following form that has
the following controls.

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