Solved

Requery A Field On Sub-Form

Posted on 2013-11-24
4
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 40

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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