I've struggling with this problem on my own, then with some help, then search about it; but I haven't had any luck. So I decided to ask.
I have two forms in Access 2007 lets call them MainForm
and EntryForm
.
MainForm
has a subform and a button. The button opens the EntryForm
in Add Mode. What I want to do is when the EntryForm
saves the new record it would update (requery) the subform in MainForm
.
I've try this setup code
Private Sub cmdSaveAndClose_Click()
DoCmd.Save
'requery list
Forms![MainForm]![subformName].Requery
'' I've also tried these
'Forms![MainForm]![subformName].Form.Requery
'Forms.("MainForm").[subformName].Requery
'Forms.("MainForm").[subformName].Form.Requery
DoCmd.Close
End Sub
None of these attempts seem to work. Is there a way to make this requery? Thanks for the help in advance.
Just a comment on the method of accomplishing this:
You're making your EntryForm permanently tied to the form you're calling it from. I think it's better to not have forms tied to context like that. I'd remove the requery from the Save/Close routine and instead open the EntryForm modally, using the acDialog switch:
DoCmd.OpenForm "EntryForm", , ,"[ID]=" & Me!SubForm.Form!ID, , acDialog
Me!SubForm.Form.Requery
That way, EntryForm is not tied down to use in one context. The alternative is to complicate EntryForm with something that is knowledgable of which form opened it and what needs to requeried. I think it's better to keep that kind of thing as close to the context in which it's used, and keep the called form's code as simple as possible.
Perhaps a principle here is that any time you are requerying a form using the Forms collection from another form, it's a good indication something's not right about your architecture -- that should happen seldom, in my opinion.