Change event not firing

[Guest]

Hello,

I have a bound form which displays records which record steps through a
process. 1 text box displays an ID number and the rest are text boxes which
display a date. A date being present indicates that step in the process is
complete. No updates, changes, deletes, adds allowed on this form.
Navigation buttons are on so I can scroll through the records. The form also
has 2 txtboxes that are used to display how many steps in the process are
completed and a completion percentage.

Form has buttons to navigate through the records. When I first open the
form, the first record is displayed. IDNumber and dates are all present and
the two text boxes display the correct data. When I hit one of the
navigation buttons to scroll through the records, I have the IDNumber Change
Event coded to re-calc the 2 text boxes for the current record, however, the
Change event does not seem to fire. Breakpoint is set for the Change event
procedure and it never executes.

Can anyone detail how the change event operates, when it should fire, etc.
And, help me code this correctly?

IDNumber_Change procedure is below.

Private Sub txtS3ID_Change()
'Determine how many dates have been provided and display a completion
percentage.

Dim StepsComp As Integer

StepsComp = 0
If Not IsNull(txtEmplStartDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtARFSubmitDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtS3IDNotifyDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtXEmailSetNotifyDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtXEANSetNotifyDate) Then
StepsComp = StepsComp + 1
End If
txtStepsCompleted = StepsComp
txtPercentComplete = (StepsComp / 5)


End Sub


TIA,
Rich

 

[Dirk Goldgar]

In

Hello,

I have a bound form which displays records which record steps through
a process. 1 text box displays an ID number and the rest are text
boxes which display a date. A date being present indicates that step
in the process is complete. No updates, changes, deletes, adds
allowed on this form. Navigation buttons are on so I can scroll
through the records. The form also has 2 txtboxes that are used to
display how many steps in the process are completed and a completion
percentage.

Form has buttons to navigate through the records. When I first open
the form, the first record is displayed. IDNumber and dates are all
present and the two text boxes display the correct data. When I hit
one of the navigation buttons to scroll through the records, I have
the IDNumber Change Event coded to re-calc the 2 text boxes for the
current record, however, the Change event does not seem to fire.
Breakpoint is set for the Change event procedure and it never
executes.

Can anyone detail how the change event operates, when it should fire,
etc. And, help me code this correctly?

IDNumber_Change procedure is below.

Private Sub txtS3ID_Change()
'Determine how many dates have been provided and display a completion
percentage.

Dim StepsComp As Integer

StepsComp = 0
If Not IsNull(txtEmplStartDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtARFSubmitDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtS3IDNotifyDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtXEmailSetNotifyDate) Then
StepsComp = StepsComp + 1
End If
If Not IsNull(txtXEANSetNotifyDate) Then
StepsComp = StepsComp + 1
End If
txtStepsCompleted = StepsComp
txtPercentComplete = (StepsComp / 5)


End Sub


TIA,
Rich

The Change event of a control fires when the text displayed by the
control is changed by user action. It doesn't fire when the displayed
value changes by record navigation. (It also doesn't normally fire when
you change the value of the control via code, but I don't think that's
your problem.)

I think you should probably be using the form's Current event for your
code. You may be able to simplify it like this:

StepsComp = _
Abs(IsNull(txtEmplStartDate) _
+ IsNull(txtARFSubmitDate) _
+ IsNull(txtS3IDNotifyDate) _
+ IsNull(txtXEmailSetNotifyDate) _
+ IsNull(txtXEANSetNotifyDate))

txtStepsCompleted = StepsComp
txtPercentComplete = (StepsComp / 5)

 

[Guest]

Hi Dirk,

Thanks very much for your reply. I am however kind of confused.

You mention using the current event which I am not familiar with (yet). Did
you mean to use the code you provided in the current event?

To make sure I am I understanding you code correctly...

Check if each individual field IsNull.
Add the result of each check (I assume 0 or 1).
Take the ABS of the final result and assign that value to stepsComp.

Am I understanding this correctly?

Thanks,
Rich

 

[Dirk Goldgar]

In

Hi Dirk,

Thanks very much for your reply. I am however kind of confused.

You mention using the current event which I am not familiar with
(yet). Did you mean to use the code you provided in the current
event?

To make sure I am I understanding you code correctly...

Check if each individual field IsNull.
Add the result of each check (I assume 0 or 1).
Take the ABS of the final result and assign that value to stepsComp.

Am I understanding this correctly?

Close. I'll explain.

The form's Current event fires whenever a new record becomes "current"
on the form. Although I'm not sure I understand exactly what you're
doing, it sounds to me as though you want the code to execute each time
you navigate to the next record. If that logic is correct, the Current
event seems to be the one to use.

The code I provided would replace the core of the event procedure you
posted, which you would move to the Current event. So you would then
have an event procedure for the form's Current event, and no event
procedure for the Change event of the text box. However, I now think I
made a mistake in the code I originally posted -- it was calculating the
number of uncompleted steps, not the number of completed steps. Try
this event procedure for the Current event instead.

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

' Determine how many dates have been provided and
' display a completion percentage.

Dim StepsComp As Integer

StepsComp = _
5 + _
IsNull(txtEmplStartDate) + _
IsNull(txtARFSubmitDate) + _
IsNull(txtS3IDNotifyDate) + _
IsNull(txtXEmailSetNotifyDate) + _
IsNull(txtXEANSetNotifyDate)

txtStepsCompleted = StepsComp
txtPercentComplete = (StepsComp / 5)

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

The IsNull() function returns True, which is equal to -1, if its
argument is Null, and 0 if the argument is not Null. So if we add up
the result of IsNull() for each of the date text boxes, we'll get the
negative of the number of uncompleted steps. If, for example, the first
three of those text boxes are filled in and the last two are Null, then
the result of adding IsNull() for all of them will be 0 + 0 + 0 + -1
+ -1 = -2. If we then add that to 5, we get 5 + -2 = 5 - 2 = 3, which
is the number of steps that are complete.

 

[Guest]

Hi Dirk,

Placing my original code into the Current event did the trick. You were
also correct on your 2nd set of code. I verified this by adding it directly
after my code with a simple message box displaying the computed values.

As a follow-up, I need to get this into a query and report. I'm trying to
add the formula as a selected/computed field/column in a query using using
design view.
I am selecting all the data correctly, just not getting the result in the
computed column. It displays a 0 (zero) in every row.

This is what I have as the field. It is one complete string:

Steps Complete:
5+IsNull([txtEmplStartDate])+IsNull([txtARFSubmitDate])+IsNull([txtS3IDNotifyDate])+IsNull([txtXEmailSetNotifyDate])+IsNull([txtXEANSetNotifyDate])


When I get this, I'll add another column for the percent complete.

Thanks,
Rich

 

[Dirk Goldgar]

In

As a follow-up, I need to get this into a query and report. I'm
trying to add the formula as a selected/computed field/column in a
query using using design view.
I am selecting all the data correctly, just not getting the result in
the computed column. It displays a 0 (zero) in every row.

This is what I have as the field. It is one complete string:

Steps Complete:
5+IsNull([txtEmplStartDate])+IsNull([txtARFSubmitDate])+IsNull([txtS3IDNotifyDate])+IsNull([txtXEmailSetNotifyDate])+IsNull([txtXEANSetNotifyDate])

I don't see anything wrong with that, so long as all those field names
are actually field names in the table you're querying. I usually use
the "txt" prefix for controls on forms, and not for fields in tables.
Are these fields date/time fields, or are they text fields? Text fields
could conceivably contain zero-length strings ("") which aren't the same
as Null.