CODEWITHSUNDEEP
vbams-accessms-access-2016
Peter
Peter

Reputation: 589

MS-Access: How to refresh a form after data was updated?

I have a query shown in a form as a table. Additionally I have a button which opens another form where you can manipulate saved data. As soon as this form closes I would like to have the query in the other form to be updated by a macro. I tried couple of macro commands. Nothing worked. I thought I could use the requery macro with my subform as parameter but even that didn't work. What can I do?

Data gets only updated when I hit 'refresh all' but this should happen automatically.

Upvotes: 3

Views: 18137

Answers (3)

HackSlash
HackSlash

Reputation: 5812

I have ran in to all sorts of strange edge cases with forms containing subforms. What happens when the current record is removed?

This may seem overkill but if you want to be sure that all forms are up to date then you can use this on the parent form:

'@Description("Refresh the data on the form and all subforms")
Public Sub RefreshForm(ByVal theForm As Form)
    On Error GoTo ErrorHandling
    Echo False

    RequeryInPlace theForm
    theForm.Refresh
    MoveToValidRecord theForm
    
    Dim childForm As Control
    For Each childForm In theForm.Controls
        If TypeOf childForm Is SubForm Then
            RequeryInPlace childForm.Form
            childForm.Form.Refresh
            MoveToValidRecord childForm.Form
        End If
    Next
    
ErrorHandling:
    ' Fail fast, ensure echo is always left on!
    Echo True
End Sub

'@Description("Requery an entire form without losing the currently selected record")
Public Sub RequeryInPlace(ByVal theForm As Form)
    Dim whereIam As Variant
    With theForm
        whereIam = .Form.Bookmark
        .Form.Requery
        .Form.Bookmark = whereIam
    End With
End Sub

'@Description("Move cursor to a valid record")
Public Sub MoveToValidRecord(ByVal theForm As Form)
    With theForm.Recordset
        If .EOF And .BOF Then Exit Sub
        
        If .EOF Then
            .MoveLast
        ElseIf .BOF Then
            .MoveFirst
        Else
            .MoveNext
            .MovePrevious
        End If
    End With
End Sub

Upvotes: 1

Aladdin
Aladdin

Reputation: 21

the query is not being updated in background unless you are opening the form which calls the Query to run , an alternative is to request the query to run again to update the form as follows

Form.Requery

if your form is having a sub form then you need to address the sub form as well

Upvotes: 2

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

Ok, the way to do this?

If you just editing data? (not adding new rows) to that grid?

Your code to launch the 2nd form will look like this:

me.Refresh   - optional but REQUIRED if you do allow editing in the gride.
docmd.OpenForm "frmDetailsEdit",,,"ID = " & Me!ID,,acDialog
me.refresh   - this will show any data changes you made in the 2nd dialog form

Note carefull: You don't need the first me.Refresh UNLESS you allow edits in the data/grid display. The acDialog will cause the code to WAIT/HALT until the user is done editing in the 2nd form.

The final me.refresh will update any data changes, and it will also keep the reocrd pointer on the same current row.

However, if your 2nd dialog form launched ALSO allows adding of records, then a me.refresh will NOT show the newly added records.

If you allow adding? Then you need to do a me.Requery on that last line in place of me.refresh. This will re-load the form based on its query, but you will ALSO lose the current position. You can if you wish re-position the record pointer/location if you need to, but we don't know if you need this ability as of yet.

Upvotes: 2

Related Questions