Control Source of subform in On Current event

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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
 
Jeff said:
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
Click to expand...

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 said:
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
Click to expand...

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.
 
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?
 
Dirk said:
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.
Click to expand...

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)
 
Jeff said:
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?
Click to expand...

I think I see. You want the subform's text box, txb_Name, to be bound to
EmployeeName when the user updates cboEmployees on the main form, and to
ClientName when the user updates cboClients on the main form. I have some
reservations about what's going to happen if the main form is bound and
moves from record to record, but that's another question.

For what you're asking, you could use these two event procedures:

'----- start of code -----
Private Sub cboEmployees_AfterUpdate()

Me!SubformControlName!txb_Name.ControlSource = _
"EmployeeeName"

End Sub


Private Sub cboClients_AfterUpdate()

Me!SubformControlName!txb_Name.ControlSource = _
"ClientName"

End Sub
'----- end of code -----

You'll have to modify the above code to replace "SubformControlName" with
the name of the subform control (on the main form) that is displaying the
subform. This may or may not be the same as the name of the form object the
subform control is displaying.

Note also that changing the controlsource of this text box isn't going to
change the way the subform is linked via the Link Master/Child Fields.
 
Mike Painter said:
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)
Click to expand...

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