Solved

Nested subforms | Change source object based on loaded main form

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

803 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