Access Report OnFormat Hangs with all users except Admins group

[heidii]

HELP!!

I know this is probably a permissions problem, but I can not locate
it. Here is what I have in a summary:

Search form with combo boxes and text boxes to filter report
(frmMaterialsSearch). OnClick Event has code to open my report in
designview first and make different groupings visible or invisible
depending on my forms selections, then saves my report. Code
continues to open report in preview, onopen code has code to check
search form for criteria, depending on the criteria it changes my
recordsource on the OnOpen event. All filters and report
modifications work great.
Here is the issue. If I am logged in as the Admin the report runs
perfectly, changing my groups orders, and making some visible not
visible, and filters correctly, no hangs on formatting. But if I log
in as a regular user I can only get the report to open into preview
mode when I make a particular selection on my search form. The other
two selections leave the report hanging on the formating of the
report. Yet under admin nothing hangs at all. I have checked all my
objects in the permissions, and everything is selected. I can not
locate why the formatting hangs with users and not admins. I have
even changed my permissions on all the objects related to the form and
reports to full admin permissions for those groups of users, but it
still hangs on the format. It just doesn't make sense to me. If it
was truly a permission problem it should I think not open the report
at all or even get to the format part of the code? right?

Please does anyone have input?

 

[heidii]

So I thought I would add just a little more input to show what I have
been trying.

1. If I open the database in 2003 instead of 2000 then I can get the
reports to finish loading for admin and users. If I open them in
2000, then only one of the report criteria from the form will finish
loading for the report
2. All of these tests are being done on the same computer.
3. Admin finishes opening all reports / Users only finishing opening
one filtered criteria
4. If I removed this bit of code then I can get all the users to
beable to finish opening the reports, but I need this code to hide and
unhide my groupsings
This code is in the OnOpen Event of my report
If [Forms]![frmMaterialAppsSearch]![LOTCHECK] = True Then
Me.GroupLevel(2).ControlSource = "LotNumber"
Me.GroupLevel(0).ControlSource = "=-1"
Me.GroupLevel(1).ControlSource = "=-1"
Me.LOTHEADER.Visible = True
Me.SEARCHLOTNUMBER.Visible = True
Me.lblLotNumber.Visible = True
Me.AreaHeader.Visible = False
Me.SearchArea.Visible = False
Me.LBLAREA.Visible = False
Me.WSDAHeader.Visible = False
Me.SearchWSDA.Visible = False
Me.LBLWSDA.Visible = False
ElseIf [Forms]![frmMaterialAppsSearch]![WSDACHECK] = True Then
Me.GroupLevel(1).ControlSource = "WSDACertNumber"
Me.GroupLevel(0).ControlSource = "=-1"
Me.GroupLevel(2).ControlSource = "=-1"
Me.WSDAHeader.Visible = True
Me.SearchWSDA.Visible = True
Me.LBLWSDA.Visible = True
Me.AreaHeader.Visible = False
Me.SearchArea.Visible = False
Me.LBLAREA.Visible = False
Me.LOTHEADER.Visible = False
Me.SEARCHLOTNUMBER.Visible = False
Me.lblLotNumber.Visible = False
ElseIf [Forms]![frmMaterialAppsSearch]![AREACHECK] = True Then
Me.GroupLevel(0).ControlSource = "Area"
Me.GroupLevel(1).ControlSource = "=-1"
Me.GroupLevel(2).ControlSource = "=-1"
Me.AreaHeader.Visible = True
Me.SearchArea.Visible = True
Me.LBLAREA.Visible = True
Me.WSDAHeader.Visible = False
Me.SearchWSDA.Visible = False
Me.LBLWSDA.Visible = False
Me.LOTHEADER.Visible = False
Me.SEARCHLOTNUMBER.Visible = False
Me.lblLotNumber.Visible = False
End If

There is more code below this but it works with or without the code
above:

If [Forms]![frmMaterialAppsSearch]![C] = True Then
Me.SearchCommodity.Visible = True
Me.lblCommodity.Visible = True
Else
Me.SearchCommodity.Visible = False
Me.lblCommodity.Visible = False
Me.GroupLevel(3).ControlSource = "=-1"
Me.COMMODITYHEADER.Visible = False
End If
If [Forms]![frmMaterialAppsSearch]! = True Then
Me.SEARCHBLOCKNAME.Visible = True
Me.lblBlockName.Visible = True
Else
Me.SEARCHBLOCKNAME.Visible = False
Me.lblBlockName.Visible = False
Me.GroupLevel(4).ControlSource = "=-1"
Me.BLOCKNAMEHEADER.Visible = False
End If
If [Forms]![frmMaterialAppsSearch]! = True Then
Me.SearchStatus.Visible = True
Me.lblStatus.Visible = True
Else
Me.SearchStatus.Visible = False
Me.lblStatus.Visible = False
Me.GroupLevel(5).ControlSource = "=-1"
Me.STATUSHEADER.Visible = False
End If
If [Forms]![frmMaterialAppsSearch]![V] = True Then
Me.SearchVariety.Visible = True
Me.lblVariety.Visible = True
Else
Me.SearchVariety.Visible = False
Me.lblVariety.Visible = False
Me.GroupLevel(6).ControlSource = "=-1"
Me.VARIETYHEADER.Visible = False
End If
If [Forms]![frmMaterialAppsSearch]![CHECKDATE] = True Then
Me.SEARCHDATE.Visible = True
Me.lblDateRange.Visible = True
Else
Me.SEARCHDATE.Visible = False
Me.lblDateRange.Visible = False
End If

 

[heidii]

Okay I have progressed further but still SOMEBODY'S HELP.

I pinpointed the problem to this bit of code: It works with my admin
account because I permissions to change objects and make save
changes. But this bit of code is not saving the changes when the code
if fired when logged in by my users. Even though I have given that
group of users all admin permissions on the queries and report. How
can I get around the fact that since it is a multiuser database, it
won't allow any report design changes? This is the only thing I can
come up with.

This code is on my OnClick Event of my search form.

If Me.AREACHECK = True Then
DoCmd.Echo False
DoCmd.OpenReport
"rptProductUseSummaryPestGlobal", acViewDesign
Set obReport =
Reports(rptProductUseSummaryPestGlobal)
With obReport
.Visible = False
' code to change report properties
Reports!
rptProductUseSummaryPestGlobal.AreaHeader.RepeatSection = True
Reports!
rptProductUseSummaryPestGlobal.WSDAHeader.RepeatSection = False
Reports!
rptProductUseSummaryPestGlobal.LOTHEADER.RepeatSection = False
End With
DoCmd.Close acReport,
"rptProductUseSummaryPestGlobal", acSaveYes

ElseIf Me.WSDACHECK = True Then
DoCmd.Echo False
DoCmd.OpenReport
"rptProductUseSummaryPestGlobal", acViewDesign
Set obReport =
Reports(rptProductUseSummaryPestGlobal)
With obReport
.Visible = False
' code to change report properties
Reports!
rptProductUseSummaryPestGlobal.WSDAHeader.RepeatSection = True
Reports!
rptProductUseSummaryPestGlobal.AreaHeader.RepeatSection = False
Reports!
rptProductUseSummaryPestGlobal.LOTHEADER.RepeatSection = False
End With
DoCmd.Close acReport,
"rptProductUseSummaryPestGlobal", acSaveYes

ElseIf Me.LOTCHECK = True Then
DoCmd.Echo False
DoCmd.OpenReport
"rptProductUseSummaryPestGlobal", acViewDesign, , acHidden
Set obReport =
Reports(rptProductUseSummaryPestGlobal)
With obReport
.Visible = False
' code to change report properties
Reports!
rptProductUseSummaryPestGlobal.LOTHEADER.RepeatSection = True
Reports!
rptProductUseSummaryPestGlobal.AreaHeader.RepeatSection = False
Reports!
rptProductUseSummaryPestGlobal.WSDAHeader.RepeatSection = False
End With
DoCmd.Close acReport,
"rptProductUseSummaryPestGlobal", acSaveYes

End If
DoCmd.Echo True
DoCmd.OpenReport [Forms]![frmMaterialAppsSearch]!
[txtReportName], acViewPreview
End If

 

[heidii]

But this still doesn't account for why the report runs fine for the
users under 2003???

 

[heidii]

If I give that group of users permission under Database as Run
Exclusive then it works for the users in 2000. But that again will
only work if no one else is logged in at the same time. But I don't
have to do this to get it to work in 2003. Which is fine, but my
users are logging in with 2000. Other than making many individual
reports for all the different criteria, I am at a loss here on a work
around.

PLEASE HELP WITH INPUT OR IDEAS???

 

[heidii]

Well no comments, But here is my solution for anyone going through
this later. I gave that one group in permissions, Database / Open
Exclusive access. And since each user has their own front end of the
database it should work out okay and won't have any obstacles with
more than one user on at the same time while the code edits the report
in design view.

 

[Marshall Barton]

HELP!!

I know this is probably a permissions problem, but I can not locate
it. Here is what I have in a summary:

Search form with combo boxes and text boxes to filter report
(frmMaterialsSearch). OnClick Event has code to open my report in
designview first and make different groupings visible or invisible
depending on my forms selections, then saves my report. Code
continues to open report in preview, onopen code has code to check
search form for criteria, depending on the criteria it changes my
recordsource on the OnOpen event. All filters and report
modifications work great.
Here is the issue. If I am logged in as the Admin the report runs
perfectly, changing my groups orders, and making some visible not
visible, and filters correctly, no hangs on formatting. But if I log
in as a regular user I can only get the report to open into preview
mode when I make a particular selection on my search form. The other
two selections leave the report hanging on the formating of the
report. Yet under admin nothing hangs at all. I have checked all my
objects in the permissions, and everything is selected. I can not
locate why the formatting hangs with users and not admins. I have
even changed my permissions on all the objects related to the form and
reports to full admin permissions for those groups of users, but it
still hangs on the format. It just doesn't make sense to me. If it
was truly a permission problem it should I think not open the report
at all or even get to the format part of the code? right?

Please does anyone have input?

I have some input, but you probably won't want to hear it
:-(

Making persistent changes to objects in a running
application is a really serious mistake: it opens the door
a lot wider for corruption to walk in; it causes major
bloat, which forces more frequent compacting that requires a
robust backup procedure; causes you to fool with the
security settings; and will not work at all in an MDE or
ACCDE.

Instead of making design time changes, you should use code
in the report's Open event to change the GroupLevel
properties and make controls and/or sections visible or
invisible.

The filtering should be done using the OpenReport method's
WhereCondition argument, but can also be done on the
report's Open event via the Filter property. So there
rarely is a need to reconstruct the report's record source
query.