How to requery a subform from another form?

Question

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.

Answer 1

Just discovered that if the source table for a subform is updated using adodb, it takes a while until the requery can find the updated information.

In my case, I was adding some records with 'dbconn.execute "sql" ' and wondered why the requery command in vba doesn't seem to work. When I was debugging, the requery worked. Added a 2-3 second wait in the code before requery just to test made a difference.

But changing to 'currentdb.execute "sql" ' fixed the problem immediately.

Answer 2

I had a similar kind of issue, but with some differences...

In my case, my main form has a Control (vendor) which value I used to update a Query in my DB, using the following code:

Sub Set_Qry_PedidosRealizadosImportados_frm(Vd As Long)
Dim temp_qry As DAO.QueryDef

'Procedimento para ajustar o codigo do cliente na Qry_Pedidos realizados e importados
'Procedure to adjust the code of the client on Qry_Pedidos realizados e importados
Set temp_qry = CurrentDb.QueryDefs("Qry_Pedidos realizados e importados")
temp_qry.SQL = "SELECT DISTINCT " & _
            "[Qry_Pedidos distintos].[Codigo], " & _
            "[Qry_Pedidos distintos].[Razao social], " & _
            "COUNT([Qry_Pedidos distintos].[Pedido Avante]) As [Pedidos realizados], " & _
            "SUM(IIf(NZ([Qry_Pedidos distintos].[Pedido Flexx], 0) > 1, 1, 0)) As [Pedidos Importados] " & _
            "FROM [Qry_Pedidos distintos] " & _
            "WHERE [Qry_Pedidos distintos].Vd = " & Vd & _
            " Group BY " & _
            "[Qry_Pedidos distintos].[Razao social], " & _
            "[Qry_Pedidos distintos].[Codigo];"
End Sub

Since the beginning my subform record source was the query named "Qry_Pedidos realizados e importados".

But the only way I could update the subform data inside the main form context was to refresh the data source of the subform to it self, like posted bellow:

Private Sub cmb_vendedor_v1_Exit(Cancel As Integer)
'Codigo para atualizar o comando SQL da query
'Code to update the SQL statement of the query 
    Call Set_Qry_Pedidosrealizadosimportados_frm(Me.cmb_vendedor_v1.Value)

'Codigo para forçar o Access a aceitar o novo comando SQL
'Code to force de Access to accept the new sql statement
    Me!Frm_Pedidos_realizados_importados.Form.RecordSource = "Qry_Pedidos realizados e importados"
End Sub

No refresh, recalc, requery, etc, was necessary after all...

Answer 3

You must use the name of the subform control, not the name of the subform, though these are often the same:

 Forms![MainForm]![subform control name Name].Form.Requery

Or, if you are on the main form:

 Me.[subform control name Name].Form.Requery

More Info: http://www.mvps.org/access/forms/frm0031.htm

Answer 4

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.

Answer 5

By closing and opening, the main form usually runs all related queries (including the subform related ones). I had a similar problem and resolved it by adding the following to Save Command button on click event.

DoCmd.Close acForm, "formname", acSaveYes
DoCmd.OpenForm "formname"

Answer 6

I tried several solutions above, but none solved my problem. Solution to refresh a subform in a form after saving data to database:

Me.subformname.Requery

It worked fine for me. Good luck.