Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Nested subforms | Change source object based on loaded main form

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
schaIT
Asked:
schaIT
  • 5
  • 4
1 Solution
 
pdebaetsCommented:
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
 
schaITAuthor Commented:
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
 
pdebaetsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
schaITAuthor Commented:
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
 
schaITAuthor Commented:
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
 
pdebaetsCommented:
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
 
schaITAuthor Commented:
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
 
pdebaetsCommented:
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
 
schaITAuthor Commented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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