Access window forces itself into the foreground when form updates

[Bennet]

Hi,

Just looking for some input on the following.

I have a couple of different databases which are often left open in the background whilst the user works on other tasks, and this makes total sense considering their purpose.

Every ten minutes or so, the main form timer causes the main subform to refresh. Sometimes when this update happens, the database will bring itself into the foreground of Windows and suddenly leave the user looking at Microsoft Access, whilst they were happily typing an email or something.

Is there any way I can stop this from happening?

Thanks.

 

[theDBguy]

Can you show us the code in the Timer event?

 

[Bennet]

Sorry for the delay. The timer on the main form calls a procedure on the subform as below.

Note it doesn't always steal the focus. Just sometimes.

Public Sub RequerySubformWithoutLosingPosition()
    Dim OriginalSelTop As Long
    Dim OriginalSectionTop As Long
    Dim RowsFromTop As Long
    Dim RecordCount As Long

    'Seltop = number of row selected
    'SectionTop = position record within current view
    
    'Before requery, note selected record number and its current position from the top of the scroll space.
    OriginalSelTop = Me.SelTop
    OriginalSectionTop = Me.CurrentSectionTop
    RecordCount = RecordsetClone.RecordCount

    Me.Requery

    'If no records were returned, the remainder of this sub will cause an error.
    'So we do this test:
    If Me.RecordsetClone.RecordCount = 0 Then
    Exit Sub
    End If

    'These two numbers change depending on your form size I think.
    RowsFromTop = ((OriginalSectionTop - 285) / 675)

    'The first figure is the one you get as the CurrentSectionTop when you have the top record selected.
    'The second is the constant that gets added each time you move one record down and check
    'what your new section top is.
    
    If OriginalSelTop - RowsFromTop <= 0 Then Exit Sub

    'Select bottom record.
    Me.SelTop = RecordCount

    'Select record that was at the top earlier on.
    Me.SelTop = OriginalSelTop - RowsFromTop

    'Now select the actual record.
    Me.SelTop = OriginalSelTop
End Sub

 

[Gasman]

Wow, what is wrong with the bookmark method?

 

[Bennet]

Wow, what is wrong with the bookmark method?

That's not something I've heard of. Like many, I learnt VBA by googling things. That code I've been using for probably 13 or more years, starting in Access 2003. It's always worked fine so I never looked any further.

 

[Gasman]

This does not even use a BookMark.
From ChatgPT.

Dim lngID As Long

' Store the current record's ID (assuming the primary key is called ID and is a Long)
lngID = Me!ID

' Requery the form
Me.Requery

' Find the record with the same ID
Me.Recordset.FindFirst "ID = " & lngID

' Check if the record was found
If Me.Recordset.NoMatch Then
    MsgBox "Record not found."
End If

From an AI on Googling

' Assuming you have a form named "MyForm" and a Recordset named "rs"
' Declare a variable to hold the bookmark
Dim savedBookmark As Variant

' Get the bookmark for the current record
savedBookmark = Me.Bookmark

' Navigate to another record (e.g., using a button click)
' ... (code to move to a different record) ...

' Return to the bookmarked record
Me.Bookmark = savedBookmark

None of which would mess around with any window, to my knowledge.

Edit: I have no idea why it mentions form and recordset, which are not even used?

https://learn.microsoft.com/en-us/o...ss/desktop-database-reference/using-bookmarks

 

[Bennet]

Will have a mess with that, but the purpose of the code I use is not just to scroll to a record, but to keep the form in whatever position it was in. The currently active record may not even be on the screen. It just keeps the subforms scrollbars in the same position they were in after the requery.

 

[Gasman]

Ah, then any of that code will not work, as it purely relocates the record in question.
Moving the form is likely the cause, but if I move to another application, how is the window going to then move? I have left the application and wherever that form is located.