Preview Report based on Option Group Selection

[GodofSmallThings]

Hello,

I would like to preview a report based on an Option group
selection. The selection is based on [CATEGORY] from my
report. The report is generated from a query...

The following is code from the Command button on my Preview
Menu Form:

Private Sub btnPreviewReport_Click()

On Error GoTo Err_btnPreviewReport_Click

Dim stDocName As String
Dim stFilter As String

stDocName = "rptPaper"
stFilter = ""

Select Case Preview
Case 1

stFilter = "[CATEGORY] = 'PREMIUM' "

Case 2

stFilter = "[CATEGORY] = 'SURVEY' "

Case 3

stFilter = "[CATEGORY] = 'DAVIDS' "

End Select

DoCmd.OpenReport stDocName, acViewPreview, , stFilter

Exit_btnPreviewReport_Click:

Exit Sub

Err_btnPreviewReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnPreviewReport_Click


End Sub


When I click on the command button. The entire report
displays and not the filtered selection. Your assistance
would be appreciated. Thanks in advance...

 

[FancyPrairie]

I don't see a problem. Why don't you do the following.
Prior to the statement "docmd.openreport...", insert the statement: msgbox stFilter just to see what it contains. I would also put a statement above my "Select Case Preview" statement and examine Preview (i.e. msgbox Preview).

If you know how to use debug, step through your code and see if it does what you expect.

Also, don't think Preview is a reserved word, but if everything else looks ok, change the name to something like grpPreview.

FYI,
I discovered the stFilter="" actually has to create another string to set stFilter to an empty string. The book I read suggested using this instead: stFilter=vbNullString. It's no big deal, just thought I'd pass it on.

 

[GodofSmallThings]

Hello,

I'm on the trial by error training program. I am not
familiar with the debug tools. However, the old school
method of printing varibles before execution gave me
some glaring clues to this problem.

...I was referencing the wrong Select Group.

Once I corrected; another problem surfaced. When I make
the selection. It prompts with the following:

ENTER PARAMETER VALUE:

The message box contains the value I selected. But I have
to type it in as well. And then the report displays with
the proper selection.

FYI: I will use stFilter = vbNullstring. Makes my code
look professional. Smile...


Any advise would be appreciated. Thanks.....


- Les

 

[FancyPrairie]

I'm assuming your report is based on a query. If so, can you run the query via the Query Builder (Open query via database window)? Does the query ask you for a parameter value? Try building a query in query builder with your where clause included. Just to see if it works (checking syntax). For example: Select * from YourTable Where [CATEGORY] = 'DAVIDS';"

Using the debugger is really quite easy. You can do it a couple of ways.

1. Put the cursor on the statement you want the program to stop at and select the button labeled "Toggle Breakpoint". Or select it from the menubar DEBUG|TOGGLE BREAKPOINT. Or press F9. Or type the word STOP before the line you want to examine.

2. Now run your program and select your command button. The program will pause execution where you set the breakpoint (or stop)

3. Once the program has stopped where you want it, view the debugger immediate window. To examine the value of a variable, type in the immediate window ?stFilter.

4. Press F8 to step thru your code one line at a time. Press F5 to continue execution of the code until the next breakpoint (if one exists).

5. The debugger local window will show the values of the local variable you have declared.

There's alot more to the debugger, but this will get you started. Will save you a lot of time.

 

[GodofSmallThings]

Hello,

I've wrestled with it, but I get the following message:

The Microsoft jet database engine does not recognize
"PREMIUM" as a valid field name or expression."

.......Perhaps this would help?

The following is an SQL view of the query that the report is generated:

SELECT tblPaper.PAPERID, tblPaper.DESCRIPTION, tblPaper.DATE, tblPaper.STOCK, qryTotalShipped.[Total Of QUANTITY], tblCategory.CATEGORY
FROM tblCategory INNER JOIN (tblPaper LEFT JOIN qryTotalShipped ON tblPaper.PAPER=qryTotalShipped.PAPER) ON tblCategory.ID=tblPaper.tblCategory_ID;

The Query works. The stFilter works. But I'm not sure
of the proper syntax...

...Thanks for your assistance!

 

[FancyPrairie]

Since your SQL statement works when you run it via the Query Builder (along with the Where clause), then I'm assuming you have a field on your report whose control source is bogus. That's why it's asking you to "Enter a Parameter Value:" I don't know where "Premium" is coming from. Unless it's a field on your report.