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.

Hedgepeth

What does the subform display? A listbox, the details from the Add/Edit form? – Equip

the subform displays the records from the add/edit form in spreadsheet – Hedgepeth

docmd.save saves the object and not the record You really want 'If Not Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord' – Excretion

Allen Browne (allenbrowne.com/bug-01.html) suggests Me.Dirty = False – Quillet

@Remou +1 for the site with the bug and the site of tips, itself! – Kind

Answer 1

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.

Bolster

That's great it worked with the acDialog. At first I was trying to do all of these from the main form, but it didn't occur to me to use the acDialog, but I guess my brain was fried by the end of these week. Thanks. – Hedgepeth

Works like a charm! – Cumulative

This does not address the issue, but rather a different approach. I searched on "requery subform" and that is what I am looking for. – Amr

This should be the #1 answer as it not only addresses the issue but it also avoids compile errors on the forms that are attached to the main form when debugging. – Rounder

Answer 2

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

Quillet

The later syntax will perform better. (Bang operators cause an implicit type conversion.) – Verbatim

Not if you are not running from the form that contains the subform it won't :) – Quillet

I've tried the first, like this Forms![MainForm]![subformControl].Form.Requery and it doesn't requery the subform. T The code is tied to the other form button. Unless there is a way to requery the subform from the parent form when the other form is closed, I can't try the later syntax. – Hedgepeth

Can you ensure that id is selecting the subform by say, setting focus to a field? This should eliminate the possibility that there is some sort of spelling error. – Quillet

The . operator depends on implicitly-created properties over which you have no control (that's how Me.ControlName becomes subject to compile-time checking, because there's a hidden property wrapper around it). This causes a marginal increase in the tendency to VBA code corruption. Some people find the compile-time checking and the better Intellisense worth that risk. I don't -- I always use the ! operator. There is no performance difference that could possibly make a real-world difference. – Bolster

@Oorang: citation on the implicit type conversion assertion? – Bolster

Answer 3

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.

Exsanguinate

Answer 4

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...

Eustis

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"

Tarver

Answer 6

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.

Columbia

Answer 7

All your controls are belong to us!

Fionnuala answered this correctly but skimmers like me would find it easy to miss the point.

You don't refresh the subFORM you refresh the subform CONTROL. In fact, if you check with allforms() the subForm isn't even loaded as far as access is concerned.

On the main form look at the label the subform wizard provided or select the subform by clicking once or on the border around it and look at the "caption" in the "Other" tab in properties. That's the name you use for requerying, not the name of the form that appears in the navigation panel.

In my case I had a subform called frmInvProdSub and I tried for many hours to figure out why Access didn't think it existed. I gave up, deleted the form and re-created it. The very last step is telling it what you want to call the control so I called it frmInvProdSub and finished the wizard. Then I tried and voila, it worked!

When I looked at the form name in the navigation window I realized I'd forgotten to put "Sub" in the name! That's when it clicked. The CONTROL is called frmInvProdSub, not the form and using the control name works.

Of course if both names are identical then you didn't have this problem lol.

Histone

Answer 8

I ran into this issue today. I was in a form that contained a button on subform1. Subform1 made changes to Subform2 that required a requery. I found success with: Me.Parent.subform2.form.recordset.requery

Avatar