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

User · Answer

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    amp                  Qry Pedidos distintos   Codigo      amp                  Qry Pedidos distintos   Razao social      amp                 COUNT  Qry Pedidos distintos   Pedido Avante   As  Pedidos realizados      amp                 SUM IIf NZ  Qry Pedidos distintos   Pedido Flexx   0   gt  1  1  0   As  Pedidos Importados     amp                 FROM  Qry Pedidos distintos     amp                 WHERE  Qry Pedidos distintos  Vd      amp  Vd  amp                  Group BY    amp                  Qry Pedidos distintos   Razao social      amp                  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Requery a subform from another form?

Examples related to vba

Examples related to forms

Examples related to ms-access