Control Source of subform in On Current event

[Jeff]

Can I use a conditional (if statement) in the On Current event of my subform
that changes the control source of a text box on the subform? Note: I need
the conditional to look at a textbox on the main form.
I have an example of what I need although it does not work. What's wrong?
eg:
This would be in the onCurrent event of the SUBFORM

IF forms!mainform!textbox.value > 0 then
textboxonsubform.controlsource = 1
else
textboxonsubform.controlsource = 2
end if

 

[Dirk Goldgar]

Can I use a conditional (if statement) in the On Current event of my subform
that changes the control source of a text box on the subform? Note: I need
the conditional to look at a textbox on the main form.
I have an example of what I need although it does not work. What's wrong?
eg:
This would be in the onCurrent event of the SUBFORM

IF forms!mainform!textbox.value > 0 then
textboxonsubform.controlsource = 1
else
textboxonsubform.controlsource = 2
end if

In principle you can do this, but your sample code is incorrect. The
ControlSource property is a string, and so you must assign a string
expression to it. That string expression must evaluate either to the name
of a field in the form's RecordSource or to an expression preceded by an
equals sign (=). So you might write something like this:

' Set controlsource to one field or another.
If Me.Parent!MainFormTextbox > 0 Then
Me!SubformTextbox.ControlSource = "SomeFieldName"
Else
Me!SubformTextbox.ControlSource = "AnotherFieldName"
End If

or this:

' Set controlsource to one calculated value or another.
If Me.Parent!MainFormTextbox > 0 Then
Me!SubformTextbox.ControlSource = "=1"
Else
Me!SubformTextbox.ControlSource = "=2"
End If

 

[Dirk Goldgar]

In principle you can do this, but your sample code is incorrect. The
ControlSource property is a string, and so you must assign a string
expression to it. That string expression must evaluate either to the name
of a field in the form's RecordSource or to an expression preceded by an
equals sign (=). So you might write something like this:

' Set controlsource to one field or another.
If Me.Parent!MainFormTextbox > 0 Then
Me!SubformTextbox.ControlSource = "SomeFieldName"
Else
Me!SubformTextbox.ControlSource = "AnotherFieldName"
End If

or this:

' Set controlsource to one calculated value or another.
If Me.Parent!MainFormTextbox > 0 Then
Me!SubformTextbox.ControlSource = "=1"
Else
Me!SubformTextbox.ControlSource = "=2"
End If

It further occurs to me that, since subforms are loaded before their parent
forms, it may be that the subform's Current event will fire before the text
box on the parent form has any value, and this may cause an error. I'm not
sure whether this will happen, but if it does, you may need to put some
error-handling in the subform's Current event to trap and ignore that error.

 

[Jeff]

Dirk,
Thanks for the reply...
I think I am making this harder than it is.....

OK...I have a textbox on my subform called txb_Name that is unbound. The
subform is sourced to a query that has an EmployeeName and a ClientName. On
the main form I want to set the control source of the txb_name to either
"ClientName" or "EmployeeName".
In the On Update event of my cbo_Employees(on the main form) I want to reset
the Control Source of the txb_Name (on the subform) to "EmployeeName" and
same with the cbo_Clients.
This way the subform displays EmployeeNames or ClientNames depending on the
combobox that is updated last. Clear as mud huh

How do I reset the control source of the txb_name in the subform?

 

[Mike Painter]

It further occurs to me that, since subforms are loaded before their
parent forms, it may be that the subform's Current event will fire
before the text box on the parent form has any value, and this may
cause an error. I'm not sure whether this will happen, but if it
does, you may need to put some error-handling in the subform's
Current event to trap and ignore that error.

You could use two textboxes and make one visible when the other is not.

You could also base the subform field on a calculated field.
IIF(MainFormTextboxField > 0 , thisfield, thatfield)

 

[Dirk Goldgar]

You could use two textboxes and make one visible when the other is not.

You could also base the subform field on a calculated field.
IIF(MainFormTextboxField > 0 , thisfield, thatfield)

True, though that will make the control non-updatable.