Solved

Access ADP subform refreshes / fickers as many times as rows in the datasheet it contains

Posted on 2006-07-07
5
397 Views
Last Modified: 2008-01-09
I have a main form and a subform. In the OPEN event for the subform the code  below is executed.  The subform displays the results in datasheet format. There are no Master / Child links. The LogonID stored on a hidden & minimized form is used to retreive the rows in the SavedActivity table. The stored procedure has 1 parameter, @UserID, which is the primary key for the table.

The resulting recordset produces exactly what I want; but when the focus is placed on the subform when a staffer clicks on any  row on the subform, the datasheet in the subform "flickers / refreshes" about as many times as the number of rows returned.  This "refresh" happens only the first time any row is clicked on or the scroll bar moved. After a staffer posts the updated row, then the problems reoccurs again.

The "refreshs" can take a long time to finish compared to the 1 - 2 second time it takes for the form to display the results.  I do not invoke any refresh / requery / repaint methods.

I have tried playing with every setting I could find, but I  don't know causes the problem or how to fix it.

Could someone please help?
 
   Dim strParm As String
   strParm = "Execute  stpSavedActivity_ByID  '" & Forms![frmSetup01]![LogonID] & "'"
  Me.RecordSource = strParm

0
Comment
Question by:hwr734s
  • 3
5 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17067742
Hi

Sunny Sunday afternoon here - so what better time to answer ADP questions :-)

The behaviour you're seeing is obviously not by design.
And you're absolutely sure you have no refresh/requery/recalc commands anywhere present in either form?  (Recalc in particular can be an absolute *pain* for doing this kind of thing).

Does this same behaviour happen when the recordsource is assigned to the subform by a more umm.. traditional method?
For example directly assigning an SQL statement to it?
(OK not as fast in theory - but we're looking for culprits here).

Do you have other forms using the method you describe?  In particular other subforms?

What about other methods.
For example

  Me.InputParameters = "'" & Forms![frmSetup01]![LogonID] & "'"
  Me.RecordSource = "stpSavedActivity_ByID"

or

  Set Me.Recordset = CurrentProject.Connection.Execute("EXEC stpSavedActivity_ByID  '" & Forms![frmSetup01]![LogonID] & "'")

Any different?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17105334
LPurvis,

Could this have anything to do with one of the "Advanced" "Interval" setting being set to low?
Refresh Interval
ODBC Refresh Interval
(Either one of these being set to 1 or Zero)

Forcing Access to "Refresh" too soon/often?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17106235
I wouldn't have expected so to be honest.

You can check the OLE timeout and the refresh interval (any ODBC settings will - or at least should - be absent in an ADP).  But I don't think they'd affect the ADP's normal use. (They should be the normal sort of 30/60 seconds... worth a quick check of course).
But for this to be happening only on entering the subform - doesn't sound like a constant interval problem as such.

It looks like this might become a "no feedback" type question - but I'd certainly wonder about the other methods of assigning the source for now.

And if there really is no refresh/requery code - then I wonder what happens if you actually put some in?
It does it - but still visibly requeries upon entry again?
0
 

Author Comment

by:hwr734s
ID: 17122201
Sorry I did not respond sooner. A co-worker resigned and I had to work on transitioning his work to me.

I did try  your suggestions, but with mixed results.

I was so frustrated, I decided to make a copy of the file and start over.  I deleted ALL events on the main form and subform and added one event back at a time. When I got it to work properly, I compared the two  VBA event files.

The culprit turned out to be the "Enter" event on main form. Some input parameters on the  properties sheet  left over from my multiple attemps to pass the parameters to the stored proceedure caused the subform to  refilter the data again when the subform datasheet got the focus.

I can not even find an "Enter" event on any properties sheet, but it showed up in the VBA editor.

A check in HELP on "Events" turned up this: "Because the Enter event occurs before the focus moves to a particular control, ..."; so i presume that after processing  the "Execute  stpSavedActivitySelected  '" & Forms![frmSetup01]![LogonID] & "' , 'T' "  when the update button on the main form was pressed, a second query was triggered by the "Input parameters"  data.

Again, I apologize for the tardy response.
In summary, either the:
        Me.InputParameters = "'" & Forms![frmSetup01]![LogonID] & "'"
        Me.RecordSource = "stpSavedActivity_ByID"
OR
       strParm = "Execute  stpSavedActivity_ByID  '" & Forms![frmSetup01]![LogonID] & "'"
        Me.RecordSource = strParm

would have worked alone, but not the two together.; which is in essence is what I had by the Data tab "Input Parameters" variables coded.

I am awarding you the points, and appreciate your help





0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17122353
Enter is an event of the subform control itself - rather than the underlying form acting as a subform.
(On the parent form the subform control has only two events - Enter and Exit - as well as the plethora that exist for the form itself.)

Ultimately - it was something firing in one of the events as the subform receives focus.  (Just not where you expected ;-)
Which is reassuring as the first thing that iccurs in such a situation.

Glad you're sorted.

(P.S. Any of the suggested methods should work well by themselves under normal circumstances)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now