Refreshing subform when form refreshed

Folks



I have a "Refresh" button on my main form that doesn't refresh the subform when it's clicked on. What do I need to add to my VBA code so that both the main form and the subform are refeshed when I click on "Refresh"?

Thanks

Remster

You do understand that Refresh does *not* drop any deleted records or pick up any new records, right?

In the main form in question it seems to do what I want it to do.

Let's call that form "DocumentsForm" and another one of my main forms "PeopleForm". DocumentsForm contains a combo box called "PeopleList", which is populated with data from PeopleTable. If, while I have DocumentsForm open, I open PeopleForm and add a new record, that new record produces a new value in PeopleList after (and only after) I click on my Refresh button. Are you telling me that shouldn't work?

A *Requery* will update the recordset with any new records. I have no idea what is behind your Refresh button.

Originally Posted by RuralGuy

A *Requery* will update the recordset with any new records.

Can you give me some tips on how to do that please?

Originally Posted by RuralGuy

I have no idea what is behind your Refresh button.

Here you go:

Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Refresh_Click:
Exit Sub
Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click

End Sub

Access wrote this itself: it's one of the pre-programmed options when you add a button.

Even though it was written by Access, the DoMenuItem syntax has been replaced with the RunCommand syntax. Here's a site to make the conversion easier: http://www.accessruncommand.com/domenuitem.htm
Having said that, I don't use either. I prefer simply using code, in this case what you have would be replaced with:

Code:

Private Sub Refresh_Click()
   On Error GoTo Err_Refresh_Click
 
   Me.Refresh     '-- Which simply refreshes the form and its underlying RecordSet.
 
Exit_Refresh_Click:
   Exit Sub
Err_Refresh_Click:
   MsgBox Err.Description
   Resume Exit_Refresh_Click
End Sub

Adding the additional code to either Refresh or Requery the SubForm is trivial.

Hmm, the new data still doesn't appear in my drop-downs. I wonder what I'm missing.

Are you talking about a SubForm or ComboBoxes (dropdowns) on a MainForm? If it is ComboBoxes then there are other things that need to be done.

I'm talking about combo boxes within a subform. They're the sort of combo boxes where you're not restricted to the values already in the list. So suppose a new value is added within the subform of Record 1. I want that value to appear in the list when completing the subform of Record 2. At the moment it's not appearing until I close and reopen the (main) form.

Are you using the NotInList event to add to the RowSource of the ComboBox in the SubForm? Is the SubForm in Continuous Form view?

I'm not sure what you mean by 'Continuous Form view', so I've added a screenshot. I suspect this is what you mean.

What's the NotInList event and what do I need to do to include it?

The Hyperlink area of that form is a SubForm in either Datasheet or Continuous Form view. Is that the area of this form that you are trying to get to update?

Yep, that's the one! All the other combo boxes refresh when I click my 'refresh' button, but not the ones in the subform.

This link should shed some light on the subject: http://www.mvps.org/access/forms/frm0031.htm
BTW, I'll bet you could make everything happen automatically without the need for a Refresh button.

This seems to work:

Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click

Me.Refresh
Me![Miscellaneous Hyperlinks subform].Form.Refresh

Exit_Refresh_Click:
Exit Sub
Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click
End Sub

Whether the whole thing looks sufficiently streamlined to an experienced eye, I've no idea. But it seems to work. (I tried replacing 'Refresh' with 'Requery', which doesn't.)

As for dispensing with the 'refresh' button, are you talking about adding an AfterUpdate event or something like that?