WHERE help for button code

J

jlute

Thanks in advance for your help. I posted this in a previous thread
but I'm afraid that it's faded into oblivion.

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
 
S

strive4peace

Hi jlute (what is your name?)

"Type" is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

if you choose not to rename your field and use [Type] in SQL, it needs
to be enclosed in brackets

~~~

it is difficult to follow the code without your database to try it on...
put this statement before your OpenReport statement

debug.print "strWhere: " & strWhere
debug.print "strFGID: " & strFGID

then, press CTRL-G after you run it and see what was written to the
Debug (Immediate) window

chances are, that manual inspection of what you are constructing will
solve the issue -- if it does not, please post what it constructed back here



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 a previous thread
but I'm afraid that it's faded into oblivion.

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
Click to expand...
 
J

jlute

Hi, Crystal!

Thanks for the response.
Hi jlute (what is your name?)
Click to expand...

My name is John and I also post under JohnLute at the MS Discussion
Forum however I regularly experience connectivity issues with that
site and therefore post at Google under this name.
"Type" is a reserved word

Problem names and reserved words in Access, by Allen Brownehttp://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...

Yeah. I named it that several years ago when I didn't know better.
It's never posed a problem however.
it is difficult to follow the code without your database to try it on...
put this statement before your OpenReport statement

debug.print "strWhere: " & strWhere
debug.print "strFGID: " & strFGID

then, press CTRL-G after you run it and see what was written to the
Debug (Immediate) window

chances are, that manual inspection of what you are constructing will
solve the issue -- if it does not, please post what it constructed back here
Click to expand...

That's a nifty way to go about things. I gave it a whirl and this is
what was in the Immediate window:
strWhere:
strFGID:

This doesn't appear to be of any help or is it saying that there's no
problem?
 
S

strive4peace

Hi John,

what it is saying is that neither variable has any value
-- that means you are not passing anything to OpenReport

~~~

would you like to go through each statement of the code so that you can
understand what is happening and, therefore, fix the problem? If so,
read this first:

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


Warm Regards,
Crystal

remote programming and training

*
:) have an awesome day :)
*
 
J

jlute

Hi John,

what it is saying is that neither variable has any value
  -- that means you are not passing anything to OpenReport

~~~

would you like to go through each statement of the code so that you can
understand what is happening and, therefore, fix the problem?  If so,
read this first:

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace
Click to expand...

Thanks, Crystal.

I was actually looking through your 8-part tutorial earlier today and
found it very informative. I'm actually very comfortable with Access
except writing/comprehending SQL and Modules and Macros. Everything
else I'm fairly skilled at. My database has 450+ tables; 400+ forms
and 550+ reports that are entirely my design so I've seen quite a bit!

This particular challenge is difficult for me due to my poor SQL
skills. I don't always understand what a long line of code such as
this is doing - despite my "hack" ability to actually write some
primitive code.

I'll review your SQL section and get back to you!

Thanks!
 

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

select child records from list box to filter report 1
WHERE help with preview button event 1
Building WHERE string; Passing Combo Box Value to a Query 1
Error 13 - Type Mismatch 5
Crosstab Query Building Report 2
Create Report On the Fly 1
Two Multi-Select List Boxes as Filters 11
Simplify Code-Delete Query 3

Top