Reproduce F9 behaviour exactly

[Guest]

Hi,

We have a snapshop of data coming from SQL Server 2000 (linked tables) which
is shown as continuous records in a subform in Access 2003.

When a user views a record we update a tick box on that subform (via a
stored procedure becuase as it is a snapshot view) to show that it has been
viewed.

There are three ways that we know of making the tick visible after it has
been updated in the backend:

1. Use the Me.Requery method which works but causes the user to lose their
place on the screen. Yes we can set the focus back to the record they
clicked by using a bookmark but it gets messy when the record they clicked
was in the middle of the screen and after we requery and setfocus the record
moves to the top of the screen (they get confused!).

2. Change the Snapshot view to a Dynamic view and update the tickbox
directly via code and not via a stored procedure. Again, this works but makes
the whole loading process very slow and when scrolling through more than 40
or so records the screen is very slow to update.

3. Press F9. This button does exactly what we want, it requeries the data
without moving the focus or moving the position of the scroll bars. We have
tried to reprodude this with code by using various methods but we cannot seem
to figure it out!

We thought that this button was a shortcut to the menu item "Records",
"Refresh" but when using the menu option and not F9 it does not do the same.
The record is not requeried.

So the real question is, what exactly does F9 do and how can I reproduce it
in code?

Thanks
Mark Donne

 

[Guest]

Hi

Me.Recalc

is the equivalent of F9.

 

[Guest]

Nope,

Sorry, Me.Recalc does not do the same as F9.

Any other ideas?

 

[Guest]

Sorry Marc

I took this from the MSweb site.

Recalc Method
See Also Applies To Example
The Recalc method immediately updates all calculated controls on a form.

expression.Recalc

expression Required. An expression that returns one of the objects in the
Applies To list.

Remarks
Using this method is equivalent to pressing the F9 key when a form has the
focus. You can use this method to recalculate the values of controls that
depend on other fields for which the contents may have changed.

Example
The following example uses the Recalc method to update controls on an Orders
form. This form includes the Freight text box, which displays the freight
cost, and a calculated control that displays the total cost of an order
including freight. If the statement containing the Recalc method is placed in
the AfterUpdate event procedure for the Freight text box, the total cost of
an order is recalculated every time a new freight amount is entered.

Sub Freight_AfterUpdate()
Me.Recalc
End Sub

 

[Dirk Goldgar]

Hi,

We have a snapshop of data coming from SQL Server 2000 (linked
tables) which is shown as continuous records in a subform in Access
2003.

When a user views a record we update a tick box on that subform (via a
stored procedure becuase as it is a snapshot view) to show that it
has been viewed.

There are three ways that we know of making the tick visible after it
has been updated in the backend:

1. Use the Me.Requery method which works but causes the user to lose
their place on the screen. Yes we can set the focus back to the
record they clicked by using a bookmark but it gets messy when the
record they clicked was in the middle of the screen and after we
requery and setfocus the record moves to the top of the screen (they
get confused!).

2. Change the Snapshot view to a Dynamic view and update the tickbox
directly via code and not via a stored procedure. Again, this works
but makes the whole loading process very slow and when scrolling
through more than 40 or so records the screen is very slow to update.

3. Press F9. This button does exactly what we want, it requeries the
data without moving the focus or moving the position of the scroll
bars. We have tried to reprodude this with code by using various
methods but we cannot seem to figure it out!

We thought that this button was a shortcut to the menu item "Records",
"Refresh" but when using the menu option and not F9 it does not do
the same. The record is not requeried.

So the real question is, what exactly does F9 do and how can I
reproduce it in code?

I don't know the nature of this check box, and how it is updated by the
stored procedure. If Me.Requery works but loses your place, have you
tried

Me.Refresh

? That should do two things: (1) save the current record if it has
been modified, (2) reload the record from the server to pick up any
other users' changes -- or, hopefully, changes made by a trigger.

 

[Dirk Goldgar]

1. Use the Me.Requery method which works but causes the user to lose
their place on the screen. Yes we can set the focus back to the
record they clicked by using a bookmark but it gets messy when the
record they clicked was in the middle of the screen and after we
requery and setfocus the record moves to the top of the screen (they
get confused!).

If Refresh doesn't work, it is theoretically possible to requery, return
to the previously current record, and reposition that record to the same
location on the form. This is the sort of thing that Stephen Lebans has
tinkered with, so I think you can find the method on his website,
www.lebans.com . I'd try other means first, though, to avoid all that
trouble.

 

[Guest]

If Refresh doesn't work, it is theoretically possible to requery, return
to the previously current record, and reposition that record to the same
location on the form. This is the sort of thing that Stephen Lebans has
tinkered with, so I think you can find the method on his website,
www.lebans.com . I'd try other means first, though, to avoid all that
trouble.

Hi Dirk,

Nope, Refresh doesn't work either. We did look at the code from
www.lebans.com last week and found that it worked 90% of the time. The other
10% it would error out on one of the recordset clone lines.

Any more ideas?

Thanks
Mark Donne

 

[Dirk Goldgar]

Nope, Refresh doesn't work either. We did look at the code from
www.lebans.com last week and found that it worked 90% of the time.
The other 10% it would error out on one of the recordset clone lines.

Do you mean that in some places it worked and in other places it didn't?
Or do you mean that *in the same place* it worked only 90% of the time.
The two cases have different implications about where the trouble lies.
The latter case is probably trickier to debug. If you're getting
partial success, I'd be looking to fix the error there rather than
discard the whole approach.

Any more ideas?

It seems very odd that Recalc and Refresh between them don't do the job.
Did you try Refresh followed by Recalc? I don't have enough information
about your setup to figure out exactly why this isn't working, though.
Are you updating records via a separate connection from the one Access
is using? That usually involves a latency delay, so updates aren't
refelcted immediately. Are there triggers involved on the SQL Server
end? The stored procedure is a likely culprit, but I don't see quite
how.

If you want to give full details of the tables, queries, stored
procedures, recordsource and controlsources involved, maybe I can see
what the problem is. But at the moment I only have a very limited
amount of time for the newsgroups, so I can't promise anything.