• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Requery A Field On Sub-Form

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
Bob_Collison
Asked:
Bob_Collison
3 Solutions
 
als315Commented:
Syntax should be like:
Forms![MyMainForm]![MySubForm].Form![ControlName].Requery
You can use query builder and get proper syntax:
query builder
0
 
Rey Obrero (Capricorn1)Commented:
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
 
BitsqueezerCommented:
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
 
Bob_CollisonAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now