Record After Update

[SueBK]

Overview: I have two date fields - activity started, activity finished. I have three possible report options - current, future or not reporting. Future is by default ticked and there is no start or finish date; current and future are ticked if there is a start date but no finish date; current only is ticked if finished is within the last 31 days; no report is ticked if the finish date is older than 31 days. (Users can not enter a finish date without entering a start date.)

I have some code (below), which determines which boxes are ticked based on the data entered into two fields. Neither date field needs be updated, so putting the code on "after update" on either field doesn't necessarily ensure the code is run. At the moment I have a command button that the user clicks to update the output. I'd really prefer the code ran automatically.

Question 1: can I put a copy of the code on BOTH fields, so that if either one or the other (or both) are changed the code will run? Or will this cause me headaches?

Question 2: if I can't do 1, is there a suitable "after record update" option that I can attach the code to.

Private Sub Command54_Click()
If IsNull(Me.Started) And IsNull(Me.Closed) Then
Me.NoRpt = False
Me.CurrentRpt = False
Me.FutureRpt = True
Else
End If
If Not IsNull(Me.Started) And IsNull(Me.Closed) Then
Me.NoRpt = False
Me.CurrentRpt = True
Me.FutureRpt = True
Else
End If
If Not IsNull(Me.Started) And Closed >= Date - 31 Then
Me.NoRpt = False
Me.CurrentRpt = True
Me.FutureRpt = False
Else
End If
If Not IsNull(Me.Started) And Closed < Date - 31 Then
Me.NoRpt = True
Me.CurrentRpt = False
Me.FutureRpt = False
End If
End Sub

 

[pbaldy]

This is a bit of a normalization issue, as you have fields whose value is dependent on other fields. Also, their value is dependent on the passage of time. I would simply make this determination on the fly.

That said, you can do 1, though the cleaner method would be to create a function and call it from both places (so the code is in one place). For 2, the after update event of the form would fire if any changes were made to the record.

But again, I wouldn't store those values at all. What happens if the 31 days passes but nobody edits the record? Your values are wrong. This is why you don't store calculated values.

 

[SueBK]

I see your point about the potential for the stored values becoming incorrect over time. The three yes/no fields have no purpose other than to guide users as to where their data will report. The actual reports run off queries attached to the table. Obviously if the tick is in the wrong box, a user may panic.

The form is a continuous form, and my issue with unbound boxes is that every record changes, rather than just the one you're looking at.

If I was to put the code on the form's On Open command, then the records should be updated every time the form is used, bypassing the need for any data changes? Yes? No? Maybe?

As to the other bit - you're saying create a function (whatever that may be) that says "run this code" and then attach the function to the After Update of the two date fields? (Plus the form on open if I go with the logic above).

 

[DCrake]

Carrying on from the previous thread if you create a sub routine in your form based on the OnClick Event of the command button and call this on the AfterUpdate of each control it should not cause any problems. such as:

Private Sub UpdateControls()
'Turn everything off to start with
   Me.NoRpt = False
   Me.CurrentRpt = False
   Me.FutureRpt = False

If IsNull(Me.Started) And IsNull(Me.Closed) Then
   Me.FutureRpt = True
ElseIf Not IsNull(Me.Started) And IsNull(Me.Closed) Then
   Me.CurrentRpt = True
   Me.FutureRpt = True
ElseIf Not IsNull(Me.Started) And Me.Closed >= (Date - 31) Then
   Me.CurrentRpt = True
ElseIf Not IsNull(Me.Started) And Me.Closed < (Date - 31) Then
  Me.NoRpt = True
End If

End Sub

So on the AfterUpdate Event of say the Me.Started control

Private Sub Started_AfterUpdate()
   Call UpdateControls
End Sub

The good thing about this is that all the actions are in one place, and should the logic change you only need to go to one place to amend it. Replicating the code in various places is sloppy and bad practice.

Yoo can even call this on the OnLoad event of the form and/or the OnCurrent Event of the record.

David

 

[SueBK]

Lovely. Thank you.