Solved

Nested subforms | Change source object based on loaded main form

Posted on 2013-05-16
9
332 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

21 Experts available now in Live!

Get 1:1 Help Now