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
Solved

Nested subforms | Change source object based on loaded main form

Posted on 2013-05-16
9
363 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
  • 5
  • 4
9 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 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
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.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

829 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