471,066 Members | 1,252 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Re: Is there a way to open a report in preview or report mode andhave it be invisible?


Yeah, I don't open the report in preview mode when I setup my
recordsource, filters, or sorts. Before I give my solution, I would
say that your code will be simpler if you drop the "Sort" button and
simply include the sorts in the primary window rather than a window in
dialog mode. For example, I use 5 sorts in my preview screen, the
first sort is always enabled, the second sort only becomes enabled if
the first is not null and not empty string.

Back to your question, the solution I use is as follows:
1. Setup a bunch of variables in your code that identify the pieces of
the recordsource, filter, or sort criteria that you want to modify
(e.g., varCategory, varType, varLocation); I use variant here b/c I
can get null strings
2. Set these variables equal to the values chosen by the user
3. Build a SQL string that can accomodate your recordsource, filter,
or sort under the variety of circumstances imposed by the user
4. Open the report
5. Set the recordsource, filter, or sort equal to your SQL string

Here's an example:
This is in the cmdPreviewReport object:
'Open inventory count sheet report with proper filter and sequence
DoCmd.OpenReport "rptInventory", acPreview
Reports!rptInventory.Filter = FilterSequence
Reports!rptInventory.FilterOn = True
Reports!rptInventory.OrderBy = SortSequence
Reports!rptInventory.OrderByOn = True

This is the Function FilterSequence:
Private Function FilterSequence()
On Error GoTo Error_Handler

Dim varClass As Variant
Dim varCategory As Variant
Dim varType As Variant
Dim varLocation As Variant

Dim strClassFltr As String
Dim strCategoryFltr As String
Dim strTypeFltr As String
Dim strLocationFltr As String

'Check to see if any filters exist
If Me.fraPrintOptions = 1 Then
'No filters exist, exit sub with no filter
FilterSequence = "(SortZero<>0)"
Exit Function
End If

'Set variables equal to values in filtering combo boxes
varClass = Me.cboSelectClass
varCategory = Me.cboSelectItemCategory
varType = Me.cboSelectItemType
varLocation = Me.cboSelectItemLocation

'Compile Class, Category, Type, and Location portions
'of the filter
'Note: company location portion of filter is handled
'within the various build queries as criteria; this was
'done to improve the speed of the final source query
If varClass = "" Or varClass = "<all>" Then
strClassFltr = ""
strClassFltr = "((qryInventoryReportBuild2.Class = '" &
varClass & "')) AND "
End If

If varCategory = "" Or varCategory = "<all>" Then
strCategoryFltr = ""
strCategoryFltr = "((qryInventoryReportBuild2.Item_Category =
'" & varCategory & "')) AND "
End If

If varType = "" Or varType = "<all>" Then
strTypeFltr = ""
strTypeFltr = "((qryInventoryReportBuild2.Item_Type = '" &
varType & "')) AND "
End If

If varLocation = "" Or varLocation = "<all>" Then
strLocationFltr = ""
strLocationFltr = "((qryInventoryReportBuild2.Item_Location =
'" & varLocation & "')) AND "
End If

FilterSequence = "(" & strClassFltr & strCategoryFltr &
strTypeFltr & strLocationFltr & _
"SortZero <0)"

On Error Resume Next
Exit Function
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function

I hope something like this works for you.

Jun 27 '08 #1
0 1428

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Henry J. Murphy | last post: by
1 post views Thread by solar | last post: by
4 posts views Thread by Richard Sherratt | last post: by
8 posts views Thread by paii, Ron | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.