Prev: Crosstab used in Graphing Incidents per Day and per Week for a Month
Next: Terminal Server color
From: evenlater on 26 Jun 2008 17:41
Here's what I'm trying to do. All of the reports in my Access 2007
application are opened via a dialog box that allows the user to pick
filter criteria. On this dialog box there is a "Sort" button. When the
user clicks on that button, a second dialog box opens that allows the
user to choose the fields they want to order by. The sort dialog box
form has four combo boxes containing a field list from the
recordsource of the report. The rowsources for these combo boxes are
populated when the user clicks the Sort button, and in order for this
to work the original dialog box needs to know what the recordsource of
the report is.

The way I was doing this was for the OnClick event of the Sort button
to open the report invisibly in design view, check the recordsource of
the report, and then close the report again. This is the VBA statement
I was using to open the report:

DoCmd.OpenReport strReport, acViewDesign, , , acHidden

Well, here's where the problem comes up. Once I convert the accdb file
to an accde file, I can't open the form in design view anymore. I have
tried opening the report in Report and Print Preview using the
acHidden variable for the WindowMode argument, but the report is
visible when I do this.

Is there a way to open a report invisibly in preview or report mode?
Or is there a better way to determine a report's recordsource in code
without opening the report at all?

Thanks!



From: ARC on 26 Jun 2008 19:18
What I do is to call an event on the On_Open event of the report. I'll paste
in my even proc at the bottom, just to show you the code, but you would
definetely have to make major changes to fit your logic. Hope this helps.


For the On_Open of report, I call:

Call SetSortLevel(Reports!rQuoteReport.Report, 3)

Here's the proc, which does work in the runtime version (I'm sure it can be
optimized quite a bit). Hope this helps. Andy

--------------------------------------------------------------


Public Function SetSortLevel(RepName As Report, i As Integer)
On Error Resume Next
'I = starting sort/group level num in the array
'Application.Echo (-1)
If RepName.Name = "rJobAgreement" Then
Select Case Forms!frmOpt.Form!MasterSort.Caption
Case 1 'Sort by existing Sort ID's in part category setup
RepName.GroupLevel(i).ControlSource = "SortID"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "PartDes"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "JobPartsID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
Case 2 'By Order Added to Quote, Invoice
RepName.GroupLevel(i).ControlSource = "JobPartsID"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "PartDes"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "SortID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
Case Else 'By Part Description
RepName.GroupLevel(i).ControlSource = "PartDes"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "SortID"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "JobPartsID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
End Select
Else
Select Case Forms!frmOpt.Form!MasterSort.Caption
Case 1 'Sort by existing Sort ID's in part category setup
RepName.GroupLevel(i).ControlSource = "SortID"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "PartDescription"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "LineID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
Case 2 'By Order Added to Quote, Invoice
RepName.GroupLevel(i).ControlSource = "RefNo"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "PartDescription"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "SortID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
Case Else 'By Part Description
RepName.GroupLevel(i).ControlSource = "PartDescription"
RepName.GroupLevel(i).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 1).ControlSource = "CatDescription"
RepName.GroupLevel(i + 1).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 2).ControlSource = "SortID"
RepName.GroupLevel(i + 2).SortOrder = False 'Set to Ascending
RepName.GroupLevel(i + 3).ControlSource = "LineID"
RepName.GroupLevel(i + 3).SortOrder = False 'Set to Ascending
End Select
End If
Exit Function
ErrRtn:
MsgBox Err.Number & " - " & Err.Description
Resume Next
End Function
"evenlater" <evancater(a)gmail.com> wrote in message
news:18b0e1ba-9e28-4dce-b27e-edd7fa67d476(a)b1g2000hsg.googlegroups.com...
> Here's what I'm trying to do. All of the reports in my Access 2007
> application are opened via a dialog box that allows the user to pick
> filter criteria. On this dialog box there is a "Sort" button. When the
> user clicks on that button, a second dialog box opens that allows the
> user to choose the fields they want to order by. The sort dialog box
> form has four combo boxes containing a field list from the
> recordsource of the report. The rowsources for these combo boxes are
> populated when the user clicks the Sort button, and in order for this
> to work the original dialog box needs to know what the recordsource of
> the report is.
>
> The way I was doing this was for the OnClick event of the Sort button
> to open the report invisibly in design view, check the recordsource of
> the report, and then close the report again. This is the VBA statement
> I was using to open the report:
>
> DoCmd.OpenReport strReport, acViewDesign, , , acHidden
>
> Well, here's where the problem comes up. Once I convert the accdb file
> to an accde file, I can't open the form in design view anymore. I have
> tried opening the report in Report and Print Preview using the
> acHidden variable for the WindowMode argument, but the report is
> visible when I do this.
>
> Is there a way to open a report invisibly in preview or report mode?
> Or is there a better way to determine a report's recordsource in code
> without opening the report at all?
>
> Thanks!
>
>
>

From: Albert D. Kallal on 26 Jun 2008 22:49
"evenlater" <evancater(a)gmail.com> wrote in message
news:18b0e1ba-9e28-4dce-b27e-edd7fa67d476(a)b1g2000hsg.googlegroups.com...

> Is there a way to open a report invisibly in preview or report mode?
> Or is there a better way to determine a report's recordsource in code
> without opening the report at all?
>
> Thanks!
>

Good question, and the answer is yes, you can deterine the recordsouce at
rutnime.

Also to save a lot of time and grief for you, I'm just gonna say this in a
pretty firm tone:

You simply cannot build an application in which you constantly opened forms
or reports in design mode, then flip them back into full mode. This type of
design will cause the VBA code to become un compiled every time you flip
into desing mode. Worse, you'll be saving an extra copy of that form/report
in the mdb file untill you do a compact + repair. To state this even more
simple: your application will massively bloat (grow in size), and you will
have just delived the most unbelievable crashing prone and un-stable
application to your client. You simply can't do design time modifications to
forms/reports in your application and expectit run with good performance, or
even with any high degree of stability. (You can certainly modified queries
and some other objects, but forms and reports are simply hands off at
runtime).

OK, now that we've got that out of the way, there is a good number of
approaches that you can use to solve your problem.

The first issues do you actually need to change the data source of the
report, or is actually the parameters that you're passing to the report?
This is a common feature request, and access has what is called a "where"
clause that's designed specifically to solve this exact problem you're
having.

Simply use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query. Nor do you need a modify the reports record source in
this case.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

you can also do the same for the order by, and it's quite simple

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate
strOrderBy = "Lastname;Firstname"
strReport = "InvoiceReport"
docmd.openReport strReprot,acViewPreview,,strWhere
reports(strReport).OrderBy = strOrdreBy
reports(strReport).OrderbyOn = true

Also, in case it is a truly is that you must actually change the real data
source for the report to different tables (which hints at an normalized
design), you can also do this.

Simply setup a variable (or function) that is global, and it returns the sql
you want. Then, in the reports on-open event, go:

me.RecordSource = strMySqlstring

Without question, using the where clause is the easiest approach if you're
not actually changing the table that the report is going to be based on.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


From: Kelii on 26 Jun 2008 23:00
Evenlater,

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 = ""
Else
strClassFltr = "((qryInventoryReportBuild2.Class = '" &
varClass & "')) AND "
End If

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

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

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

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

Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
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.

Kelii
From: evenlater on 27 Jun 2008 00:49
Albert, thanks for your words of caution about opening reports in
design view. I wasn't aware of the compile and bloat issues.

I am familiar with the Where argument in DoCmd.OpenReport, in fact I'm
using it in this case already. My users pick filter criteria in an
unbound form (dialog box) and I build a where string based on their
selections, just as you suggest. But that only applies to the Where
clause of a SQL string, and what I'm trying to change here is the
Order By clause.

I wasn't trying to change the data source or anything else in the
report at all when I opened it in design view. All I want to do is to
ascertain the recordsource of the query so I can give my users a list
of the fields in the report from which they can choose a sort order.
I'm sure opening in design view is a bad way to do this... but so far
I don't think anyone's suggested an alternative that will allow me to
determine the recordsource without opening the report - ?

Thanks again to everyone for your suggestions. I need to take a closer
look at some of the code that's been offered.

 |  Next  |  Last
Pages: 1 2
Prev: Crosstab used in Graphing Incidents per Day and per Week for a Month
Next: Terminal Server color