Set default value in different subform

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I must be suffering from old-timers disease today - just can't get this to
work!

I'm trying to set the default value for a textbox control on a subform, from
code run in a different subform on the same main form. The subforms are on
different tab pages on the mainform, but that shouldn't have any influence
on my problem (I'm pretty sure of that). I keep getting RTE 2455 "You
entered an expression that has an invalid reference to the property
Form/Report" at the following line in the Current event of the active
subform:

Me.Parent!sbfrmActionComments.Form!txtActionID.DefaultValue = ActionID

I've tried several other variants, all of which fail in exactly the same
fashion:

Me.Parent.sbfrmActionComments.Form.txtActionID.DefaultValue = ActionID
Me.Parent.sbfrmActionComments.Form!txtActionID.DefaultValue = ActionID
Me.Parent.Form.sbfrmActionComments.Form!txtActionID.DefaultValue =
ActionID
Forms!frmArising!sbfrmActionComments.Form!txtActionID.DefaultValue =
ActionID
Forms("frmArising").Form.sbfrmActionComments.Form!txtActionID.DefaultValue
= ActionID

sbfrmActionComments is the name of the subform control for the second
subform on the main form. The parent form is frmArising, and the code is in
a subform in a control named sbfrmAction.

I know this can be done - in fact, I'm sure I've done it before, just can't
remember exactly where to go check the syntax ;-)

TIA,

Rob
 
Hi Rob,

This in one subform should refer to the default value of a control on
another subform of the same form:

Me.Parent.Controls("sbfrmActionComments").Form.Controls("txtActionID").DefaultValue
and it shortcuts to
Me.Parent!sbfrmActionComments.Form!txtActionID.DefaultValue
which you've already tried.

So my first thought is that the problem could be with the RHS of the
assignment. What is "ActionID": the name of a variable, of a field, of a
control? Does it need to be qualified?

Have you set a breakpoint on this line so you can use the Immediate Pane
to check the values of
ActionID
Me.Parent.Controls("sbfrmActionComments").Form.Name
and so on?
 
Hi John, thanks for the reply.

ActionID is a field in the recordsource of the first subform. It's actually
an autonumber, and it does have a value when I'm trying to set it as the
defaultvalue for a control in the second subform; the code I presented is in
an
If Not IsNull(ActionID)
...
End If
construct. And I've already confirmed via a breakpoint that it does have a
valid value.

At the breakpoint, checking
Me.Parent.Controls("sbfrmActionComments").Form.Name (as in your response)
gives the same error. The control name IS correct (and is the same as the
name of the form); I've just tried cut/paste the name from the properties
box into the code, in case there was a non-visible character or some such
other corruption, and the problem still exists. I'm at a loss as to why it
doesn't work

By way of explanation, the second subform is actually for 1-many data from
the first subform, but for my user interface I need to display this in a
separate tab on the main form. Since I can't use the built-in master/child
link fields, I'm setting the defaultvalue for a control bound to the second
subforms foreign key field; I'm also setting the recordsource for the second
subform using:
Me.Parent.Form.sbfrmActionComments.Form.RecordSource = strCommentsSQL
(also within the same If ). This gives the same error.

Anxiously awaiting further suggestions,

Rob

BTW: Here's the complete code for the first subform's Current event, which
is where all the action is happening (or not happening, at present!).

Private Sub Form_Current()
'Last edited: Rob Parker, 16 Feb 2006
'NOTE: Copying ActionID into parent form for use in linking action comments
subform fails
' - can only use fields from parent recordset as links
'Need following code to set subform's recordsource
Dim strCommentsSQL As String
Dim strResponseSQL As String

If Not IsNull(ActionID) Then
'set up Comments subform
strCommentsSQL = "SELECT * FROM RWOP_DAT_Actions_Comments WHERE ActionID
= " & ActionID & ";"
Debug.Print strCommentsSQL
'put ActionID into subform for saving with subform records
'NEXT LINE CRASHES!!!
' Me.Parent.sbfrmActionComments.Form.txtActionID.DefaultValue = ActionID
' Me.Parent.sbfrmActionComments.Form!txtActionID.DefaultValue = ActionID
' Me.Parent.Form.sbfrmActionComments.Form!txtActionID.DefaultValue =
ActionID
' Forms!frmArising!sbfrmActionComments.Form!txtActionID.DefaultValue =
ActionID
'
Forms("frmArising").Form.sbfrmActionComments.Form!txtActionID.DefaultValue =
ActionID
Me.Parent!sbfrmActionComments.Form!txtActionID.DefaultValue = ActionID
Me.AllowEdits = Not Locked
Me.Parent.Form.sbfrmActionComments.Form.RecordSource = strCommentsSQL
Me.Parent.Form.sbfrmActionComments.Form.AllowEdits = Not Locked
Me.Parent.Form.sbfrmActionComments.Form.AllowAdditions = Not Locked
'set up Response subform
strResponseSQL = "SELECT * FROM RWOP_DAT_Responses WHERE ActionID = " &
ActionID & ";"
Debug.Print strResponseSQL
'put ActionID into subform for saving with subform records
Me.Parent.Form.sbfrmResponse.Form.txtActionID.DefaultValue = ActionID
Me.Parent.Form.tabArisingTabs.Pages(3).Visible = True
Me.Parent.Form.tabArisingTabs.Pages(4).Visible = True
Else
'hide Comments and Response Remarks tab if no Action data
Me.Parent.Form.tabArisingTabs.Pages(3).Visible = False
Me.Parent.Form.tabArisingTabs.Pages(4).Visible = False
Me.Parent.Form.tabArisingTabs.Pages(5).Visible = False
End If

btnLock.Visible = (IsManager And Not Locked)
lblLocked.Visible = ((IsInspector Or IsManager) And Locked)
SetNavButtons

End Sub
 
More info - and the root cause of my problem:

I get this error when my main form opens. It arises because, as the
subforms load, their current events trigger. And when the current event in
sbfrmAction triggers, the next subforms have not yet loaded, so they don't
exist! Hence the error. The syntax works perfectly well to access controls
or properties of already-loaded subforms, but not those which are yet to
load.

So ... all I need is some method of avoiding this during the main form open
event. But I do need it to happen before the user start navigating to
various tabs on the mainform (where the subforms are). Any thoughts on the
best way to do this? My initial thought is that I could set up a flag for
MainFormOpen, and only run my subform Current code if that flag is not set.

Rob
 
Hi Rob,

Glad you've found the cause of the trouble. Maybe the simplest thing to
do to use something like this in the first subform's current event to
trap the error when the second subform is not yet open:

Dim sbfrmOther As Form
...
On Error Resume Next
Set sbfrmOther = Me.Parent.Controls("sbfrmActionComments")blah blah
On Error Goto 0
If not (sbfrmOther Is Nothing) Then
'Other subform is loaded and accessible
'Do stuff with it
...

'If you can refer to the subform via the parent,
'it means the parent is also loaded, so you can
'manipulate it as well
...

'Tidy up
Set sbfrmOther = Nothing
End If
 
Hi John,

Thanks for the suggestion; I'm sure I can code my way past this now. And
thanks for your initial post, which got me to the spot by confirming the
sysntax - once I found it worked in some cases and not in others, the
problem became obvious.

I think I'll also add code in the latter subforms to check their
recordsource, and if it's not correct then run the code in the
"pseudo-parent" subform (which I can do, as long as I remember to declare
the Sub as Public!) - that should cover all bases, regardless of the order
in which the subforms load.

Thanks again,

Rob
 
Back
Top