Solved

Requery A Field On Sub-Form

Posted on 2013-11-24
4
406 Views
Last Modified: 2013-11-28
Hi Folks,
An Expert just provide the following code to call a Form from a Form and after adding a record via the Called Form refresh the data in the Calling Form.  This works.

'Parent Form Code.  e.g. [F-10-010 - Member Master Maintenance Form - Maxi Data]
'Open F-03-100 Address Master Maintenance Form With Linkage.  e.g. [F-10-010 - Member Master Maintenance Form - Maxi Data]
Private Sub CmdF03100_10_Click()
On Error GoTo Err_CmdF03100_10_Click
Dim stDocName_F03100_10 As String
Dim stLinkCriteria_F03100_20 As String
stDocName_F03100_10 = "F-03-100 - Address Master Maintenance Form"
DoCmd.OpenForm stDocName_F03100_10, , , stLinkCriteria_F03100_10, , , Me.NAME & "|" & "FieldName"
'DoCmd.OpenForm stDocName_F03100_10, , , stLinkCriteria_F03100_10, , , "F-10-010 - Member Master Maintenance Form - Maxi Data" & "|" & "HOME_ADDRESS_KEY"

Exit_CmdF03100_10_Click:
    Exit Sub
Err_CmdF03100_10_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_CmdF03100_10_Click
End Sub

'Child Form Code.  e.g. [F-03-100 Address Master Maintenance Form]
This code is a generic.
Option Compare Database
Dim strForm As String, strControl As String  'Generic Parent - Child Linkage.

'Form Load Event Code.
Private Sub Form_Load()
On Error GoTo Err_Form_Load
'Create Linkage to Parent Form.
If Me.OpenArgs & "" <> "" Then
  strForm = Split(Me.OpenArgs, "|")(0)
  strControl = Split(Me.OpenArgs, "|")(1)
End If
Exit_Form_Load:
    Exit Sub
Err_Form_Load:
    'MsgBox Err.DESCRIPTION
    Resume Exit_Form_Load
End Sub

'Form Unload Event Code
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Form_Unload
'Refresh Parent Form Field After Update.
Forms(strForm)(strControl).requeryExit_Form_Unload:
Exit_Form_Unload:
    Exit Sub
Err_Form_Unload:
    'MsgBox Err.DESCRIPTION
    Resume Exit_Form_Unload
End Sub
End Sub

Now  I need to do this where the Calling Form is a Sub-Form of another Form and I can't seem to get the syntax correct.

Can someone help?
Thanks.  Bob Collison.
0
Comment
Question by:Bob_Collison
4 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 50 total points
ID: 39672823
Syntax should be like:
Forms![MyMainForm]![MySubForm].Form![ControlName].Requery
You can use query builder and get proper syntax:
query builder
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 39673276
for all the SUB forms that open the Address master maintenance form, use this code format,

change "NAMEOFCONTROL" with the actual name of the control

DoCmd.OpenForm stDocName_F03100, , , stLinkCriteria_F03100, acFormAdd,,Me.name & "|" & "NAMEOFCONTROL" & "|" & Me.parent.name
  

Open in new window

                                         
                                                                  
                                                            
                                                            
the following codes will be placed in the Address Master Maintenance form code module


Option Compare Database
Dim strForm as string, strControl as string, strParent as string  ' place this at the top of the module

Open in new window



now place this codes in the load event of the form



private sub form_load()

if me.openargs & ""<> "" then

  strForm=split(Me.openargs,"|")(0)
  strControl =split(Me.openargs,"|")(1)
  if ubound(split(Me.openargs,"|"))>1 then
	strParent = split(Me.openargs,"|")(2)
  end if
end if

end sub

Open in new window

                     

' in the unload event

private sub Form_unload(cancel as integer)

if strParent & ""="" then
	Forms(strForm)(strControl).requery
	else
	Forms(strparent)(strForm).Form(strControl).requery
end if
end sub              

Open in new window

0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 50 total points
ID: 39674248
Hi,

if you only want to access the parent form inside of a subform you can simply use "Me.Parent" in the subform's code because this will not address the subform container but the form with the subform container (if you have more nested subforms this will of course address the direct parent form only, not the main form).

You could go on and use such wild syntax to find the right control on the right form but you will get heavy work to do if you ever need to change the name of such a control so you must search for all the requery codes (or in your case the parameters you pass to the form). It will get more complicated if you need to refresh more than one control at the same time.

I recommend to never bind a form to another form, instead, use events. An event sends only a signal "hey, I'm the address form and the user has changed a row, so any form which has something to do with addresses: Please refresh!". The sending form only sends the signal without knowing if there's someone to listen and it's job is done.
All the forms which want to hear of this message registers itself to the message sender (without knowing if there will be any message) and if a message is sent they get the signal and can do whatever needed on their own. So if the message above is sent the form displaying a combobox with addresses can be requeried without the sending form need to know that this combobox exist and that it needs to be requeried.

On this way you are uncoupling any form from each other and you have a clean code where each form takes care of itself only.

The way to do this is described in detail here:
How to synchronize forms using own events

Cheers,

Christan
0
 

Author Closing Comment

by:Bob_Collison
ID: 39684605
Capricorn1 had already provided much of the solution and I like it along with his additional information the best as it is comprehensive and at the same time understandable by my level of expertise.
ALS315 solution was too simplistic for this particular need although it works on a more limited basis.
Bitsqueezer probably has the best solution however it is too advance for my level of expertise.
Thanks to all!
Bob Collison.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now