Solved

Requery A Field On Sub-Form

Posted on 2013-11-24
4
405 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

19 Experts available now in Live!

Get 1:1 Help Now