VBA Countrol Source change on continuous form.

  • Thread starter Thread starter Jaazaniah
  • Start date Start date
J

Jaazaniah

Hey all,

I have a continuous subform that lists a calcuated field based on a
table value. Here's some sample code:

Private Sub Hours_GotFocus()
Me.Hours.ControlSource = ""
Me.Hours.Text = Round(Me.sngDuration / 60, 2)
Me.Hours.SelStart = 0
Me.Hours.SelLength = Len(Me.Hours.Text)
End Sub

Private Sub Hours_LostFocus()
Me.sngDuration = CSng(Me.Hours.Text) * 60
Me.Hours.ControlSource = "=Round([sngDuration]/60,2)"
Me.dtDateTime.SetFocus
DoCmd.GoToRecord , , acNext
End Sub

What I've noticed is that the whole column of fields has their control
sources reset. This is apparently normal, but has anyone found a work
around to allow for just the active record to be affected? or am I
splitting hairs? Mostly I'm looking to avoid having the user be
confused when the column changes. I've tried a 2nd text box to toggle
visibility and calulate based on that with focus juggling, and it also
has the same effect.
 
You can create a public function in the form, and then bind that function to
the text box (that one column of the continues form).

If you pass that function the values needed...then you get a correct value
for each instance

eg:

=MyHours([sngDuration])


So, that function cna then return your calc..

Public Function MyHours(vDur as varient)

myuHours = Round(vDur / 60, 2)

end function

You can also bind the continues form a query, and simply put that calc
expression as a column in the query (you likely don't even need a function).

Last but not least...you like could put that whole expression you have now
as the data source for the next box in the continues form....

eg:

=(round(sngDuration / 60))

At the end of the day, you can't stuff a value into a un-bound text box,
since you only have one instance of that text box on the form. However, if
you bind that text box to a expression (or function), then you get a new
value for each row during the display process
 
Jaazaniah said:
Hey all,

I have a continuous subform that lists a calcuated field based on a
table value. Here's some sample code:

Private Sub Hours_GotFocus()
Me.Hours.ControlSource = ""
Me.Hours.Text = Round(Me.sngDuration / 60, 2)
Me.Hours.SelStart = 0
Me.Hours.SelLength = Len(Me.Hours.Text)
End Sub

Private Sub Hours_LostFocus()
Me.sngDuration = CSng(Me.Hours.Text) * 60
Me.Hours.ControlSource = "=Round([sngDuration]/60,2)"
Me.dtDateTime.SetFocus
DoCmd.GoToRecord , , acNext
End Sub

What I've noticed is that the whole column of fields has their control
sources reset. This is apparently normal, but has anyone found a work
around to allow for just the active record to be affected? or am I
splitting hairs? Mostly I'm looking to avoid having the user be
confused when the column changes. I've tried a 2nd text box to toggle
visibility and calulate based on that with focus juggling, and it also
has the same effect.
Click to expand...


So what you want to do is have a calculated control that nevertheless can be
used to update the control on which its value is based. Correct? There is
a way to do this, or at least simulate it, but not by changing the
controlsource on the fly. Instead, you have to use two text boxes overlaid
on one another so that only the one with the focus is visible at a time.
One text box, which is normally on top, is the calculated control that
displays the value computed from the base field; the other is an unbound
text box that is used to update the base field by reversing the calculation.

So, in your case (if I understand it) you would have two text boxes:
"Hours", which you already have, and "txtHoursUpdate". Both text boxes
would have the same size and formatting, and with Hours placed exactly on
top of txtHoursUpdate (you can use Format -> Send To Back to move
txtHoursUpdate behind Hours, if you need to).

The ControlSource of Hours would be set (permanently) to your expression,
"=Round([sngDuration]/60,2)". The ControlSource of txtHoursUpdate would be
blank, making it unbound.

Set the Tab Indexes of these controls so that txtHoursUpdate immediately
follows Hours in the tab order. Set the Tab Stop property of Hours to No,
and the Tab Stop property of txtHoursUpdate to Yes.

Now all you need is code for various events to do two things: (1) transfer
the focus automatically from Hours to txtHoursUpdate, if the user happens to
click on it, and (2) handle the calculations and assignments to create the
effect you want. Here's code for those events:

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

Me.txtHoursUpdate.SetFocus

End Sub

Private Sub txtHoursUpdate_AfterUpdate()

With Me.txtHoursUpdate
If IsNull(.Value) Then
Me.sngDuration = Null
Else
Me.sngDuration = CSng(Me.txtHoursUpdate) * 60
End If
End With

End Sub

Private Sub txtHoursUpdate_BeforeUpdate(Cancel As Integer)

' Validate the user's entry -- must be numeric.

With Me.txtHoursUpdate
If Not IsNull(.Value) Then
If Not IsNumeric(.Value) Then
Cancel = True
MsgBox _
"You've entered an invalid value.Invalid value. " & _
"Please correct it, or press {Esc} to clear it.", _
vbExclamation,
"Invalid Entry"
End If
End If
End With

End Sub

Private Sub txtHoursUpdate_GotFocus()

' Make sure the initial value of this text box
' matches the current calculated value.

Me.txtHoursUpdate = Me.Hours

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

That should do it, maybe with some minor tweaking.
 
Hey all,
Click to expand...
I have a continuous subform that lists a calcuated field based on a
table value. Here's some sample code:
Click to expand...
Private Sub Hours_GotFocus()
Me.Hours.ControlSource = ""
Me.Hours.Text = Round(Me.sngDuration / 60, 2)
Me.Hours.SelStart = 0
Me.Hours.SelLength = Len(Me.Hours.Text)
End Sub
Click to expand...
Private Sub Hours_LostFocus()
Me.sngDuration = CSng(Me.Hours.Text) * 60
Me.Hours.ControlSource = "=Round([sngDuration]/60,2)"
Me.dtDateTime.SetFocus
DoCmd.GoToRecord , , acNext
End Sub
Click to expand...
What I've noticed is that the whole column of fields has their control
sources reset. This is apparently normal, but has anyone found a work
around to allow for just the active record to be affected? or am I
splitting hairs? Mostly I'm looking to avoid having the user be
confused when the column changes. I've tried a 2nd text box to toggle
visibility and calulate based on that with focus juggling, and it also
has the same effect.
Click to expand...

So what you want to do is have a calculated control that nevertheless canbe
used to update the control on which its value is based.  Correct?  There is
a way to do this, or at least simulate it, but not by changing the
controlsource on the fly.  Instead, you have to use two text boxes overlaid
on one another so that only the one with the focus is visible at a time.
One text box, which is normally on top, is the calculated control that
displays the value computed from the base field;  the other is an unbound
text box that is used to update the base field by reversing the calculation.

So, in your case (if I understand it) you would have two text boxes:
"Hours", which you already have, and "txtHoursUpdate".  Both text boxes
would have the same size and formatting, and with Hours placed exactly on
top of txtHoursUpdate (you can use Format -> Send To Back to move
txtHoursUpdate behind Hours, if you need to).

The ControlSource of Hours would be set (permanently) to your expression,
"=Round([sngDuration]/60,2)".  The ControlSource of txtHoursUpdate would be
blank, making it unbound.

Set the Tab Indexes of these controls so that txtHoursUpdate immediately
follows Hours in the tab order.  Set the Tab Stop property of Hours to No,
and the Tab Stop property of txtHoursUpdate to Yes.

Now all you need is code for various events to do two things: (1) transfer
the focus automatically from Hours to txtHoursUpdate, if the user happensto
click on it, and (2) handle the calculations and assignments to create the
effect you want.  Here's code for those events:

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

    Me.txtHoursUpdate.SetFocus

End Sub

Private Sub txtHoursUpdate_AfterUpdate()

    With Me.txtHoursUpdate
        If IsNull(.Value) Then
            Me.sngDuration = Null
        Else
            Me.sngDuration = CSng(Me.txtHoursUpdate) * 60
        End If
    End With

End Sub

Private Sub txtHoursUpdate_BeforeUpdate(Cancel As Integer)

    ' Validate the user's entry -- must be numeric.

    With Me.txtHoursUpdate
        If Not IsNull(.Value) Then
            If Not IsNumeric(.Value) Then
                Cancel = True
                MsgBox _
                    "You've entered an invalid value.Invalid value. " & _
                        "Please correct it, or press {Esc} to clear it.", _
                    vbExclamation,
                    "Invalid Entry"
            End If
        End If
    End With

End Sub

Private Sub txtHoursUpdate_GotFocus()

    ' Make sure the initial value of this text box
    ' matches the current calculated value.

    Me.txtHoursUpdate = Me.Hours

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

That should do it, maybe with some minor tweaking.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you both for the help. I ended up with two text boxes and used
the layered approach. Works great, thanks!
 
Back
Top