Requery and bookmarks

[Paradigm]

I have a continuous form and need to requery it after updating data with a
pop up form. I can return the focus to the current record by storing the
record id and then doing a findfirst and bookmark etc. The problem is with a
long list of data, if the record is near the end then when I set the focus
back to the record the form scrolls so that the record is at the top of the
form. Sometimes this only leaves one or two records being displayed with a
mainly blank form.
Is there any way of requerying a form, setting the focus back to the
selected record without scrolling to the top of the form, i.e. leave the
form viewing the same records.
Alec

 

[Albert D. Kallal]

Well, does that popup form update data, or does it actually add records?

If you ONLY update data, then a requery is not needed, and you can use a
me.refresh

However, if you *must* do a re-query, then all book marks are lost, and you
have to use a findfirst.

However, a continues form should not have that much data in it
anyway..right?

 

[Paradigm]

It only updates the data but the form is based on a query and it requires a
requery to show the updated data.
The issue is that I can move to the required record but the form will scroll
so that the record moves to the top of the form when I am updating a record
near the bottom.
Alec

 

[Albert D. Kallal]

It only updates the data but the form is based on a query and it requires
a
requery to show the updated data.

Does not matter. You SHOULD NOT need a reqry then!!

It should not. did you try a me.refresh?

eg:

me.Refresh
docmd.OpenForm "your popup form"

In the close event of "your popup form", place a

forms!NameOfFormToRefresh.Refresh

So, if you are only updating data, and not adding new records, then you DO
NOT NEED a requery to show that updated data...

If you form (the one we are refreshing) does NOT allow updating of data,
then you can get rid of the me.Refresh I have as above that occurs right
before the openform (you don't need it).

So, you simply need to execute a me.refresh when you return to this form.
You can use the close event of the popup form (s) , or if you wish (and have
several popup forms), then you can use the on-activate event of the form and
not have to "add" code to each popup form.

 

[Paradigm]

I did try a refresh. Maybe it is because the data is in SQL server.
Alec

 

[Albert D. Kallal]

I did try a refresh. Maybe it is because the data is in SQL server.
Alec

yes, a significant big detail you left out on your part.

However, does the table have a timestamp field exposed? In addition, you
also need a primary key field exposed (all tables need both a timestamp
field, and also a primary key exposed to the ms-access form).

Given this is sql server, try the above - it should fix this. However, if
you already have the required timestamp + pk field exposed to the table,
then you might very well be back to a requery. The next solution would be to
simply reduce the records the form loads (There is generally not much need
to have more then 50-100 records in ANY form anyway).

 

[Larry Linson]

The next solution would be to simply reduce
the records the form loads (There is generally
not much need to have more then 50-100
records in ANY form anyway).

Especially in the client-server environment where the key to performance is
reducing the amount of data transmitted across the network. Almost always,
when you take a look at the real _requirements_ (rather than what was 'an
easy implementation' or leftover design from single-user standalone), what
amazes is the number of times that the required number of records is just
ONE, if it exists, or NONE, if it does not.

Larry Linson
Microsoft Access MVP

 

[Paradigm]

The application is actually a staff in/out register like a notice board that
shows where staff are. It usually replaces a sign in/out register.
It needs to display a list of all the staff which is rarely more than about
50 names and there are only a few fields.

It works fine if the staff member is in the upper part of the list but if
the staff member is in the lower part, after the requery the form is
scrolled so that the staff member is at the top of the form. If the staff
member is the last in the list, after the scroll it is the only record that
is displayed with a nearly blank form. I want to be able to requery the
records, set the focus back to the selected record BUT also show the same
view on the form.
Alec