By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,249 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,249 IT Pros & Developers. It's quick & easy.

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

P: n/a

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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.