Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested subforms | Change source object based on loaded main form

Posted on 2013-05-16
9
Medium Priority
?
385 Views
Last Modified: 2013-05-20
I have a form that is nested as shown below:
frmHR
sfrmEmpDet
sfrmEmpEval
sfrmTactic
The form sfrmEmpEval can be opened as  subform on frmHR or by itself.
frmEmpEval
sfrmTactic
I would like sfrmTactic to change it's source object based on the main form that is open.

Questions:

1.

What is the syntax of the code?

2.

Which event procedure should be used (on open, on load)?

3.

On which form should the code be placed?Any help would be greatly appreciated!
0
Comment
Question by:schaIT
[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
  • 5
  • 4
9 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 1000 total points
ID: 39173132
In the form "frmEmpEval" OnLoad event procedure, place the code

dim s as string
on error resume next
s = ""
s = me.parent.name
err.clear
on error goto 0     '* Or reset error to whatever it was before the Resume Next
if s = "" then
    '* There is no parent form. Set control source of subform accordingly
    me!sFrmTactic.controlsource = "<some form name>"
else
    '* There IS a parent form. Set control source of subform accordingly
    me!sFrmTactic.controlsource = "<some form name>"
end if

Open in new window

0
 

Author Comment

by:schaIT
ID: 39175237
Thank you for your quick reply.  I receive an error message when I open frmEmpEval without the parent form:

Error Number: 2467
Error Desc: The expression you entered refers to an object that is closed or doesn't exist
Error Line: me.parent.name

When I open the frmEmpEval with the parent form, I receive parameters errors which are directly related to the sourceobject of the subform.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39175344
Do you have "Break on all errors" enabled? If so, disable it.

Did you remember to include this line of code:

on error resume next

?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:schaIT
ID: 39175582
I went back to check the code based on your last post and made some adjustments.  The following code now works without errors when I open frmEmpEval by itself:
Private Sub Form_Open(Cancel As Integer)
Dim s As String

On Error Resume Next
s = Me.Parent.Name
Err.Clear

On Error GoTo 0

If s = "" Then
    'There IS a parent form. Set control source of subform accordingly
    Me!sfrmObjective.SourceObject = "sfrmEvalObjLst"
    Me!sfrmTactic.SourceObject = "sfrmEvalTacLst"
Else
    'There IS NOT a parent form.  Set the control source object accordingly
    Me!sfrmObjective.SourceObject = "sfrmEvalObjHR"
    Me!sfrmTactic.SourceObject = "sfrmEvalTacHR"
End If

'Update the information on the tactics (fsubEvalMeasures) page
Me!sfrmTactic.Form!cboGoal.Requery
End Sub

Open in new window

When I open frmHR with frmEmpEval as a subform I get a parameter box:
Forms!frmEmpEval!sfrmTactic.Form!txtSearch
The sourceobject is not changing when frmEmpEval is opened as a subform.
0
 

Author Comment

by:schaIT
ID: 39175587
The comments in the code I just posted were not accurate here is the updated version:
Private Sub Form_Open(Cancel As Integer)
Dim s As String

On Error Resume Next
s = Me.Parent.Name
Err.Clear

On Error GoTo 0

If s = "" Then
    'There IS NOT a parent form. Set control source of subform accordingly
    Me!sfrmObjective.SourceObject = "sfrmEvalObjLst"
    Me!sfrmTactic.SourceObject = "sfrmEvalTacLst"
Else
    'There IS a parent form.  Set the control source object accordingly
    Me!sfrmObjective.SourceObject = "sfrmEvalObjHR"
    Me!sfrmTactic.SourceObject = "sfrmEvalTacHR"
End If

'Update the information on the tactics (fsubEvalMeasures) page
Me!sfrmTactic.Form!cboGoal.Requery
End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39176160
First, my suggestion was to put the code in the OnLoad event procedure. Did that not work for you? The .requery method in the form OnOpen event procedure may cause problems because there are no records loaded at that time. So try using the OnLoad event procedure. This may or may not resolve the parameter box issue. Let me know if parameter box problem persists.
0
 

Author Comment

by:schaIT
ID: 39181003
It would be helpful if I followed directions...lol!  I moved the code the the appropriate event procedure as you suggested.  I still receive the parameter when opening frmHR with frmEmpEval as a subform.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39181184
The parameter dialog may be a different issue.

Are you able to open frmEmpEval both as a subform, and stand-alone?

Parameter dialog issues can be a mis-named field in a control source or in a combo box somewhere. I'll need more info in order to help with it.
0
 

Author Closing Comment

by:schaIT
ID: 39181829
Got it!  The original suggestion you provided did the trick.  lso, I made an adjustment to get rid of the paramter issue.

The form frmEmpEval has a subform called sfrmTactic.  I am using the code you provided to change the source object of this form based on the way frmEmpEval is opened.

It only makes since at this point to make the subform sfrmTactic unbound so the source object can change at run time.  Now that the subform is unbound it works perfectly!  No more parameter issues.

Thank you so much for helping me figure this out.  I was beating my head against the desk on this one!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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