Current Event / Refresh / Datasheet scrolling woes (1 Viewer)

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
I have a datasheet with a recordset/recordsource set to a non-updateable query. It's a query on several tables.

The user wants to change one of the displayed values on one of the datasheet lines, so it's one field in one of the tables.

So a pop-up window takes the user keyed value and the parent form updates the table in VBA.

Then I need to refresh the recordset so that the value appears in the datasheet, so the SQL query will fetch the data.

This is somehow causing the scroll position to change.

This is the sequence of events:
User highlights a sub form datasheet line, presses a button, pop-up window takes input for new value
Update value in the table in parent form using VBA (non-updateable recordset)
Me.refresh in parent form (running subform.refresh does nothing)
The sub form datasheet refreshes, I can see the value that was keyed into the pop-up form and saved, and the datasheet position has not changed.

Current event of sub form datasheet fires. Why ? I haven't changed the current record.

There is no code in the sub form current event except for a msgbox
The message box displays. All is ok so far, datasheet has not yet scrolled and the new value is still visible.
Then on the very next press of F8 (exiting the sub form current event) the datasheet scrolls.

Seems like a requery, goes back to the first record. But I've stepped through the code, there's no requery. So, refreshing a datasheet which is comprised of a query on several tables must requery ? But I can already see the new value that was saved.

It's really annoying. I can navigate back to the record using FindFirst on a clone recordset, but the record then goes right to the end of the datasheet window when it may have been scrolled centrally. Is there a way to reposition the datasheet to the original position within the window ?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:36
Joined
Sep 12, 2006
Messages
15,614
So a pop-up window takes the user keyed value and the parent form updates the table in VBA.

if you are changing something displayed in the subform, why not have the code to write the change in the subform. The parent should be independent of the subform, so you should only need to update the form OR the subform - not both. Having said that, if you have other objects open that would also be affected by this change you may need to work out the best way to propagate the change. Me.refresh may not always work to show the change - you may need me.requery which produces a different effect. Just a doevents may work in some instances.

I am not sure whether refresh would fire the current event, without testing. It may, but maybe there is something else - maybe a change event - leading to the current event firing.
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
Try to use Application.Echo False , do a requery of the form, find the record in the subform then Application.Echo True.

Cheers,
Vlad
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
I haven't quite figured out what's causing the apparent requery, but I suspect it's because when I take the user input for one of the fields in the query, I insert a new record in one of the tables that makes up the query.

Navigating back to the relevant record isn't a big issue - I know how to do that - it's the fact that the datasheet scrolls out of place which is a pain. So I've got the record of interest near the top of the datasheet, I edit it, then on save the record appears at the bottom and all the other records have scrolled up.
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
So wouldn't a full requery of the main form/subform reset the record's position within the datasheet? Do you have the subform sorted by a date stamp?

Cheers,
Vlad
 

sxschech

Registered User.
Local time
Today, 03:36
Joined
Mar 2, 2010
Messages
791
This was from an older project and can't remember if it is what you are currently doing or if it does what you would like it to do.
This goes in a standard module

Code:
Public Sub RequeryFormAndKeepCurrentlySelectedRecord(f As Form)
'http://stackoverflow.com/questions/2426371/in-an-access-form-how-to-return-to-previous-record-after-requery
'http://stackoverflow.com/questions/8787979/how-do-i-reference-the-current-form-in-an-expression-in-microsoft-access
'20160128
    Dim Position As Long
    Position = f.CurrentRecord
    f.Requery
    If f.Recordset.RecordCount > 1 And Position > 1 Then
        f.Recordset.Move Position - 1 'Zero based
    End If
End Sub

Something like this goes in the event on your form
Code:
    Call RequeryFormAndKeepCurrentlySelectedRecord(Me)
'    Me.Requery
' requery subform instead
    Forms!frmExchange!frmExchangeTS_Sub.Form.Requery
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
Still working on this. Here's my code wait msgbox:

Code:
'ctlMatchDsheet.Form.Refresh    ' Does NOTHING

MsgBox "about to me.refresh"
' not visible yet
Me.Refresh
' visible for a brief moment then scrolls due to apparent requery
MsgBox "me.refreshed"

' now we must navigate back
' create a clone recordset
Set rs = ctlMatchDsheet.Form.RecordsetClone
' navigate back to saved record
rs.FindFirst "TransHeaderID = " & RecordID
' set visible recordset to our original record
ctlMatchDsheet.Form.Bookmark = rs.Bookmark

Set rs = Nothing

So when the first msgbox appears, the record that I updated in VBA (because this is a non-updateable recordset), is not yet visible

I click ok, and for a brief moment, I can see the value that I updated.
But it scrolls up out of sight and the second msgbox displays.

I've read and re-read that a refresh should not change the record position.
So that me.refresh is somehow causing the recordset to requery.

I can navigate back to the record (per the code above) but the record has scrolled to a different place and it's not a good user experience.


sxschech, I tried that code but the datasheet is still scrolling. It's called, RequeryFormAndKeepCurrentlySelectedRecord, but I haven't actually done a requery, something else appears to have caused it when I refresh.
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
Have you tried to use the Application.Echo to hide the moves/flickering while doing it?
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
Yes I have. It doesn't stop the datasheet from scrolling, though. I want the edited record to remain in the position.

Is there a way to say, navigate back to record x and position it 2 lines from the top of the datasheet window, I wonder.

Either that or somehow prevent the requery. I have discovered that the sub form current event is called when I refresh but there's no code in there.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:36
Joined
Sep 21, 2011
Messages
14,050
PMFJI
I use this in one of my forms when I have forced a requery
I have just added the Application.Echo to hide the scrolling, though this is a continous form.
Save the ID and go back to it.

Code:
Private Sub cmdRequery_Click()
    Dim lngId As Long
    Dim rst As DAO.Recordset
    Dim strCriteria As String
        
    ' Save record so requery on subform will pick up all last record written
    If Me.Dirty Then Me.Dirty = False
    lngId = Me.ID
    Application.Echo False
    Me.Requery
    strCriteria = "ID=" & lngId
    ' Go back to record we were on
    'Me.Recordset.FindFirst strCriteria
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    Me.Recordset.FindFirst strCriteria
    Application.Echo True

End Sub
/code]
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
That is what I was suggesting , using Application.Echo in conjunction with a full requery (which should reposition the record in the same place within the datasheet).
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
Still haven't solved this but I think I'm getting close ... the issue was never about navigating back to the record but the fact that the datasheet was being refreshed like a requery and the record of interest was changing it's position within the sub form datasheet window.

I think I need to be refreshing the sub form, not the main form. If I can do that, I don't think I'll get the datasheet scrolling so the problem goes away.
Should be easy, but, I discovered that none of my sub form refresh commands are now working (they were).

I've verified that the sub form SourceObject and the RecordSource are both valid, immediately following the subform.refresh line, but the sub form is not being refreshed. I changed the subform.refresh line to subform.requery, and that works, so the reference is correct.

I found an old old thread describing someone with the same issue when they have two subforms on the main form (as I have). Someone said it's an Access bug. I'm working through the fixes.

https://www.access-programmers.co.uk/forums/threads/access-subform-will-not-refresh.111250/
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:36
Joined
Sep 21, 2011
Messages
14,050
I've reread you original post.

User amends a value that is written back to the table.?
So to see the new data, surely you need a requery.?

If you requery, the data starts at the top of the recordset?

What am I missing here? :confused:
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
You *could* be right. It's my understanding that a refresh will show values that have changed in *existing* records. I don't need a requery for that, do I ?
However this is complicated by the fact that the SQL datasource for this datasheet is on 3 tables, so a single datasheet line comprises fields from all 3 tables.

If I write data separately to one of the fields using SQL, to an *existing* record, do I need a requery for that ? Won't a refresh pick up the new value ? Actually I know that answer to that, because for a brief moment, I *can* see the edited data until the datasheet scrolls.

Here's a screenshot so you can see what the issue is.
Value of Prev.Alloc comes from table b.
Value of Match/Pay comes from table c, BUT, there may or may not actually be a record in table c, UNTIL the user elects to place or edit a value in the field Match/Pay.
If the record in table c already exists,
Screenshot.jpg
ie there's already a value in Match/Pay, it's edited, If the record in table c doesn't exist (Match/Pay field is empty), it's inserted. The value of whatever is keyed for Match/Pay is written to table c.
All other column values come from table a.

The whole lot comes in via a SQL query on all 3 tables, so the datasheet RecordSource is the SQL query, and it's not updateable. So I *must* write the value of Match/Pay separately to table c using VBA.

So the top part of the screenshot:
I've double clicked the second line in the datasheet.
A window pops up and I key in a value to Match/Pay.
I click Save and Close, pop-up window closes.
The value I've keyed and written to table c, is not yet visible.
I do me.refresh on the main form (which I now think is wrong, but, refreshing the datasheet does nothing, which I think is a glitch)
For a brief moment, I can see the value that I keyed, it appears, but then the datasheet scrolls. It's scrolling, I *think*, because I did me.refresh on the main form, but I could be wrong about that.
The I do a FindFirst to navigate back to the record that I've edited. But look where it ends up, at position 10.

The *issue* is that double clicking the second datasheet line, then editing it, for it to then re-appear at position 10, is really annoying.

I want the edited record to be refreshed and remain in the same position 2 on the datasheet. Which it used to. This form/module isn't a new one, it's been working as I want. I changed the data/table model to properly normalise it and make it more robust.

I really haven't worked out why my subform.refresh is no longer working. I *think* that will solve the problem, because the datasheet won't scroll (as it didn't before).

Hope that makes it a bit clearer. Help always appreciated !
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:36
Joined
Sep 21, 2011
Messages
14,050
I think that is just how access works?
It finds that record and I believe that will always be at the bottom of the form if enough records exist.?
If you can calculate the top record being shown in the form which is 291 then you can calculate how many records you need to adjust, then go back to your desired record?
I still think a requery is needed, but could be wrong. Refresh works for calculated controls etc.?, but you say you are inserting records?

I am doing something similar with my code as my requery would only show one record with the GoToLast, so I move back 5 records to show a full form of records.?

HTH
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
Just tested something. Deleted ALL records from table b and table c.
Added a value in to the Match/Pay window, which causes a record to be inserted into table c.
Did the me.refresh (on main form)
I can see, briefly, the value that I keyed.
Refreshing the main form seems to requery the sub form.
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
Can you try:

Code:
'ctlMatchDsheet.Form.Refresh    ' Does NOTHING

Application.Echo False

me.ctlMatchDsheet.sourceobject="yoursubformname" 'resetting the sourceobject should requery the subform and reset the position the record'

' now we must navigate back
' create a clone recordset
Set rs = ctlMatchDsheet.Form.RecordsetClone
' navigate back to saved record
rs.FindFirst "TransHeaderID = " & RecordID
' set visible recordset to our original record
ctlMatchDsheet.Form.Bookmark = rs.Bookmark
Set rs = Nothing

Application.Echo True

Cheers,
Vlad
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
Thanks Vlad. Two things happen:

I put a msgbox to pause after the ctlMatchDsheet.SourceObject = "sfrmPaymentMatchDsheet". I get #Name? on all fields

Then I get run time error 7951 on the next line, Set rs = ctlMatchDsheet.Form.RecordsetClone. Don't understand why.

Let's just revist what I have in case I've missed something glaringly obvious:

The container for the sub form is called TransHeaderDsheet, and there is NO SourceObject defined in the form properties.
Then I have this line of code in the parent form:
Set ctlMatchDsheet = Me!TransHeaderDsheet
followed by:
ctlMatchDsheet.SourceObject = "sfrmPaymentMatchDsheet". This is actual name of the form object
then:
ctlMatchDsheet.Form.RecordSource = "qryfrmPayment_UnallocHeaders". Or the equivalent because I have to pass the current CustSuppID into the query string.

qryfrmPayment_UnallocHeaders is a non-updateable saved query.

It works as I expect, it's just the refresh. It's not refreshing. I even opened the form, which showed a value from one of the tables, then edited the table field directly, then did the user routine followed by: ctlMatchDsheet.Form.Refresh, and the field I amended manually didn't refresh.

It only 'refreshes' when I do the me.refresh, main form, which *seems* to requery the sub form, but then I've got this scrolling issue.

Does Access only refresh a form when the data has been edited by the bound controls? No-one has said so, but that might explain why it's not refreshing, because I update the table by a separate VBA SQL command.

This form worked perfectly well in its non-normalised schema because (I think) the datasheet was updateable. Refresh worked.
 

GK in the UK

Registered User.
Local time
Today, 10:36
Joined
Dec 20, 2017
Messages
274
PS just tested by doing the user edit and closing the pop-up form, then clicking Refresh on the ribbon and it too sends the datasheet scrolling.
 

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
Would you be able to upload a sample DB with all the objects involved (empty tables is fine as I have your screen shots to enter some dummy data), I could have a look a bit later today and have it for you for your morning :).
Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom