Home > Software design >  Refresh All VBA Code Equivalent to Refresh Data in Form from another form?
Refresh All VBA Code Equivalent to Refresh Data in Form from another form?

Time:10-10

I have a Form that is linked to a Query. In this form, each row contains a hyperlink value that opens another Form containing the data of that row. The data obtained in the secondary form is executed like (below code is in secondary form):

Public Sub Form_Load()
   
    DoCmd.SearchForRecord acTable, "Secondary_Form", acFirst, "[Field1] = " & TempVars!currentRec

End Sub

The parent form contains the following code upon clicking the hyperlink field value for a particular record:

Public Sub Field1_Click()
   TempVars.Add "currentRec", "" & [Field1]

   DoCmd.openForm "Secondary_Form", acNormal, "", "", , acDialog
End Sub

In the secondary form containing the details of the record, I have a "Close" button with the following event, that upon closing the data, "should" refresh the parent form with any changes made from the secondary form, but it doesn't:

Private Sub Cancel_Click()

   CommandBars.ExecuteMso "DataRefreshAll"
   DoCmd.Close acForm, Me.name

End Sub

Since the main form is linked to a Query, which is linked to a Table, I have verified that changes in the secondary form to the record is reflected in the Table, and thus in the Query as well. However, the only way I can get the parent form to reflect the changes made in the secondary form is to manually hit the Refresh All in the Home tab.

I've also tried to open the parent form (even though it's already opened behind the secondary form), and run a Requery and Refresh, which had no result in updating the main form with the changes of the record:

Private Sub Cancel_Click()

   Dim fm As Form

   DoCmd.openForm "Main_Form", acNormal

   Set fm = Forms("Main_Form")

   fm.Requery
   fm.Refresh

   DoCmd.Close acForm, Me.name

End Sub

How can I refresh the main form using VBA code instead of manually clicking "Refresh All"?

CodePudding user response:

Requery the main form when code returns after closing the secondary form:

Public Sub Field1_Click()

   TempVars.Add "currentRec", "" & [Field1]
   DoCmd.openForm "Secondary_Form", acNormal, "", "", , acDialog
   ' Seconday Form closes.
   Me.Requery

End Sub

No need to reopen the main form as it hasn't been closed:

Private Sub Cancel_Click()

   DoCmd.Close acForm, Me.name

End Sub
  • Related