New to Access/VBA: Expiration Dates

[la1814]

Greetings. I am very new...which will be evident...to Access/VBA. I am building a database which has expiration dates. I want to place a flag ("^") next to a persons name when they are within 14 days of an expiration or if a yes/no box is checked. I want the flag if ONE of the 8 due dates or the yes/no box is not checked. The following is my poor attempt to automatically update while the information is put or changed into a form. This is just for the AllRead (yes/no) category. I need to incorporate the dates in this AfterUpdate to ensure they are accounted for if the AllRead is changed back to yes (no flag) but a date is within 14 days (a flag needed) then the flag stays.

Private Sub AllRead_AfterUpdate()
'updates Flag if AllRead is "No" or date expires is within 14 days of 'current date

Dim Daysa As Integer
Dim Days1a As Integer
Dim Days4a As Integer
Dim Days5a As Integer
Dim Days6a As Integer
Dim Days7a As Integer
Dim Days8a As Integer
Dim Days10a As Integer

'calculates days until due
Daysa = DateDiff("d", Now, Me.FlightPhysicalExpires)
Days1a = DateDiff("d", Now, Me.NATOPSFA18EFExpires)
Days4a = DateDiff("d", Now, Me.INSTCheckExpires)
Days5a = DateDiff("d", Now, Me.SwimPhysExpires)
Days6a = DateDiff("d", Now, Me.Seat_Brief_SJU5617Expires)
Days7a = DateDiff("d", Now, Me.NVGLabExpires)
Days8a = DateDiff("d", Now, Me.CRMFA18AFExpires)
Days10a = DateDiff("d", Now, Me.FirefightingExpires)

If AllRead = No Then
Dim Flag As String
Flag = Me.NameLookup + " ^"
Me.NewNameLookup = Flag
Else
Dim Flagged As String
Flagged = Me.NameLookup
Me.NewNameLookup = Flagged

If Daysa Or Days1a Or Days4a Or Days5a Or Days6a Or Days7a Or _Days8a Or Days10a <= 14 Then
Dim Flagging As String
Flagging = Me.NameLookup + " ^"
Me.NewNameLookup = Flagging
Else

End If
End If
Form.Refresh
End Sub

It will not remove the "^" when the 'No' box is checked to 'Yes'.

As I mentioned...I am self taught and new to this world. Any help is much appreciated...I am sure there are many holes in this. Happy Holidays.

.......................
Below is what I was originally using to update the expiration when input the date completed the initial training. This only accounts for that individual date though, and removes the "^" if the expiration is updated, but doesn't account for any other dates near expiration before removing the "^".
Private Sub NATOPSFA18EF_AfterUpdate()
'Computes NATOPS expiration dates
If IsMissing(Me.NATOPSFA18EF) Then
Me.NATOPSFA18EF = " "
End If
Me.NATOPSFA18EFExpires = DateSerial(Year(NATOPSFA18EF) + 1, Month(NATOPSFA18EF) + 1, 0)

'updates Flag if NATOPSFA18EFExpires is within 14 days of current date

Dim Days1 As Integer
Days1 = DateDiff("d", Now, Me.NATOPSFA18EFExpires)

If Days1 <= 14 Then
Dim Flag1 As String
Flag1 = Me.NameLookup + " ^"
Me.NewNameLookup = Flag1
Else
Me.NewNameLookup = Me.NameLookup
End If

Form.Refresh

End Sub

 

[Mihail]

Welcome to the forum !

One mistake:

If Daysa Or Days1a Or Days4a Or Days5a Or Days6a Or Days7a Or _Days8a Or Days10a <= 14 Then

should be

If (Daysa <= 14) Or (Days1a <= 14) Or (Days4a <= 14) Or (Days5a <= 14) Or (Days6a <= 14) Or (Days7a <= 14) Or (Days8a <= 14) Or (Days10a <= 14) Then

Save your DB As Access 2003, ZIP it and upload it to the forum (navigate to "Advanced" in order to upload)

 

[Cronk]

la1814,

Another way of getting the same result, not necessarily better but IMO neater, is to make each of the calculations into a boolean variable

Dim booFlightPhysical as boolean, booINSTCheck as boolean

booFlightPhysical =(Me.FlightPhysicalExpires -Date < 14)
booINSTCheck=(Me.INSTCheckExpires -Date < 14)

If booFlightPhysical or booINSTCheck or .... = true then

 

[la1814]

Gentlemen,

Thanks for he quick reply. Huge help and very much appreciated. Glad to hear I was on the right track and it was something that should have been obvious but I kept looking for the deeper issue. Suppose you can help me on the next issue. I am trying to update a field on a form with the current time when another field is checked 'yes'. I am using military time (00:00). I figured it would be as easy as this but fear I am way off on this one. Can't seem to find much on it during my searches:

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
Dim Launch As Integer
If Airborne = Yes Then
Launch = Now()
Departure = Launch
Else
End If
End Sub

Any good VBA books you would rec? Thanks again.

 

[Mihail]

Use code tags when you post code
Press "Go Advanced" bellow this window, press "#" button then paste your code between [ CODE] and [ /CODE] labels that will appear.

When you post some code is not enough to say "this don't work".
You should say us WHERE is the trouble. What Access say ? (the error message) and, better, to highlight the line in trouble.
Also inform us about your names. I don't know what Airborne or Departure is

So you should say:

This code generate the error : "Compile error: Variable not defined"
Airborne - check box, bounded to field Airborne (or unbound)
Departure - text box bounded to field Departure (Date/Time)

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
 Dim Launch As Integer
   If Airborne = [COLOR=Red][B]Yes [/B][/COLOR]Then
        Launch = Now()
        Departure = Launch
      Else
      End If
End Sub

Based on this information I can replay:

1)
Yes is not a VB variable. A VB variable is vbYes = 6.
But, a check box can take one of 2 values: -1 = checked or 0 = unchecked
So, Airborne can't be equal with vbYes.
BTW: Do a try using this code and try to understand what happen (press F5 when the code will stop) :

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
Dim Launch As Integer
    If Airborne = vbYes Then
        Launch = Now()
        Departure = Launch
        Stop
    Else
        Stop
    End If
End Sub

So, until now, we decided that Yes or vbYes are not good
Lets try with True. Tis is a VB variable: True = -1
So, replace Yes with True and see what happen.
What Access say ? Why ?

2)
Because Departure is a Date/Time field (you informed us about that, isn't it ? ) and you try to store an Integer (your Launch variable is declared as Integer) in this field.
One more try:

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
Dim Launch As Date
    If Airborne = True Then
        Launch = Now()
        Departure = Launch
        Stop
    Else
        Stop
    End If
End Sub

Now seems to be OK, so we can remove the Stops.

3)
About the If - Then - Else structure:

    If SomethingIsTrue Then
        'Run code from here
    Else 'That mean that that "Something" is not True => SomethingIsFalse
        'Run code from here
    End If

So is no need to compare "Something" with True.
=> Your code can be simplified a bit:

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
Dim Launch As Date
    If Airborne Then
        Launch = Now()
        Departure = Launch
    Else
    End If
End Sub

The "Else" Part can be omitted if has no code in.

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
Dim Launch As Date
    If Airborne Then
        Launch = Now()
        Departure = Launch
    End If
End Sub

The code is functional but, is no reason for the Launch variable here.
So, the final code should be:

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
    If Airborne Then
        Departure = Now()
    End If
End Sub

I said the final code because this structure allow you to further improve it.
As is now, the code can be one more time simplified:

Private Sub Airborne_AfterUpdate()
'when click Airborne it will update Departure
    If Airborne Then Departure = Now()
End Sub

but this structure will not allow you to add a "Else" part if need.

Good luck and remember the blue advices when you will post next time.

 

[la1814]

Mihail...many many thanks. Not only for the fix, but more importantly your teachings! It of course worked and I was able to apply this in a few other parts of my database.

 

[Mihail]

You are welcome any time.