Refreshing Data On A continuous Screen, without using ‘Requery’

Posted on 2014-11-05
Last Modified: 2014-11-05
I developed a continuous screen ‘frmContinuous’ for a client.  The screen usually contains over a 100 records in the data set being worked on by the users.  Many more than can fit on the visible screen.  So the users have to scroll to get to many of the entries.

The recordsource of ’frmContinuous’ is a query combining data from multiple tables and other queries.

Each line on the continuous form has an ‘Edit’ button to the far left.  When the users clicks the ‘Edit’ button they are taken to another screen, ‘frmEdit’ that allows to revise some of the information shown on the continuous form.

When the users exit ‘frmEdit’ and return to ‘frmContinouos’ they want to be positioned on the record they were edting and have the information on ‘frmContinuous’ reflect changes (if any) that had been made in ‘frmEdit’

I don’t seem to be able to accomplish both.
If I don’t execute the ‘refresh’ when they return to frmContinuous, they are positioned exactly where the information on the screen for that record does not reflect any changes they made.

If I execute  a ‘Me.Requery’ when I return to frmContinuous’ the updated information is shown on the screen.  However, it also repositions the cursor to the first record on the screen.  This is particularly time consuming for them because they may have to scroll thru many records to get back to the record they want to work on next.

Is there any way to accomplish both objectives when returning to the continuous for?
1.       Maintain the position in the recordset
2.      Update the information shown on the screen for that record.
Question by:mlcktmguy
  • 2
LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40424897
Why do do the refresh, then DoCmd.GoToRecord to the same record they were on?
LVL 24

Expert Comment

ID: 40424997

you can use "Me.Refresh" instead if you only want to reflect changes to a record, that will not change the position of the record. But if you delete or insert a record you can only use Requery to get the changes.

I developed a "Reposition" method which is able to work with all kind of changes and returns to the edited record and also at the same position on the screen. You can find that here:

Reposition - requery with positioning to the recent record



Author Comment

ID: 40425338
Thanks for the responses:

More Info:
No records can be deleted or added on the continuous form or the 'edit' form.

First response
I would like to try this but I'm not familiar with the 'GoToRecord' command.

Could you be more specific as to the coding to implement your suggestion.

This is the code in my 'Edit' click event.

Private Sub btnEdit_Click()
 DoCmd.OpenForm "frmClientDistribution_New", , , , acFormEdit, acDialog, Me.ClientRecID
End Sub

I'm guessing prior to the 'Docmd.openform... I would have to store something that will take me directly to my record after I return from the form.

Then after the refresh I would use the GotToRecord but I'm not clear on the specifics

Something like:

Private Sub btnEdit_Click()

dim recordpointer as long

recordpointer = ???????
 DoCmd.OpenForm "frmClientDistribution_New", , , , acFormEdit, acDialog, Me.ClientRecID

docmd.gotorecord ????? recordpointer

End Sub

I tried using 'refresh' instead of 'requery' to stay on the same record but the information on the records was not updated.  It is only updated when I do the 'requery'.

Author Closing Comment

ID: 40425513
Here is what I ended up with in my 'Edit' button routine

Private Sub btnEdit_Click()
Dim whichRecord As Long
whichRecord = Me.CurrentRecord
DoCmd.OpenForm "frmClientDistribution_New", , , , acFormEdit, acDialog, Me.ClientRecID
DoCmd.GoToRecord acDataForm, "frmProjectedTransportation_Pre", acGoTo, whichRecord
End Sub

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 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

16 Experts available now in Live!

Get 1:1 Help Now