Stop my Nag Messages on "archived" records

[Junkee Brewster]

Hi There,

My form is set up to show all current records, and allow users to select a button and then view "archived" records (I archive by having a Yes/No box). I'll show you:

Private Sub cmdShowArchived_Click()

If cmdShowArchived.Caption = "Show Archived" Then
        Me.RecordSource = "SELECT QryVehicles.* FROM QryVehicles 
       WHERE (((QryVehicles.Archived)=Yes));"
        DoCmd.Requery
        DoCmd.GoToRecord , , acFirst
        cmdShowArchived.Caption = "Show Current"
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        
        Me.Archived.Visible = True
        Me.archivedBox.Visible = True
   
 Else
        Me.RecordSource = "SELECT QryVehicles.* FROM QryVehicles 
        WHERE (((QryVehicles.Archived)=No));"
        DoCmd.Requery
        DoCmd.GoToRecord , , acFirst
        cmdShowArchived.Caption = "Show Archived"
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        
        Me.Archived.Visible = True
        Me.archivedBox.Visible = True
        End If
End Sub

Secondly, I've got a setup that will: flag expired dates by changing the background colour of the field, and also showing "Nag Messages" in a message box when the date is expired OR the date expires in the next 2 weeks. Example:

If Not IsNull(txt90DayService) And _
       Not IsEmpty(txt90DayService) Then
           
          If ((CDate(txt90DayService) < Date)) Then
            MsgBox "Reminder: 90 Day Service for vehicle is Overdue", vbInformation
         ElseIf ((CDate(txtReTest) - Date) < 14) Then
            MsgBox "Reminder: 90 Day Service for vehicle is due within two
        weeks", vbInformation
         End If
    End If

  If (Not IsNull(Me.txt90DayService)) Then
    
        If fncPersonnelExpiredLicenses(Me.txt90DayService) Then
            Me.txt90DayService.BackColor = vbRed
        Else
            Me.txt90DayService.BackColor = vbWhite
        End If
        
    Else
        Me.txt90DayService.BackColor = vbWhite
    End If

How do I prevent the Nag messages and the changing the background of records that are ARCHIVED?

My visual basic is only basic, and I've built upon some existing code and I'm lucky to have worked out this far hehe. I can KIND of see what I have to do, but I just can't get it right. Any help would be greatly appreciated.

 

[WayneRyan]

JB,

If [Archived] is a yes/no field, then:

If ((CDate(txt90DayService) < Date))  And Not [Archived] Then
   Nag Message ...
End If

[Archived] can be an invisible field if your want. It doesn't HAVE to be
displayed.

Wayne

 

[Junkee Brewster]

Thanks Wayne! Worked a treat.

If you're still about, I have a second question for you, relating to the same code. Say I wanted to flag the background of the 90DayService with a different colour (Cyan I think!), to indicate the dates that will be expiring in the next two weeks (to match my Nag Messages I already have, just like the expired dates.

I can't seem to do it because of the fncPersonnelExpiredLicences bit in the code I gave in my first post (it's not mine, I'm building around it). Here is the function:

Public Function fncPersonnelExpiredLicenses(strLicense As String) As Boolean

    If CDate(strLicense) < Date Then
        expiredLicenses = True
        fncPersonnelExpiredLicenses = True
    End If
    
End Function

Again, I can kind of see it, but I can't compose it in the right way.

P.S Initially I had the "Archived" box set so it only appeared when I was in the archived section (i.e, to unarchive). Then I thought, but how will I archive records in the current? So I just made it visible on both (in a sloppy way, I couldn't be bothered setting the form so I just turned Visible on both in code hehe). Thanks

 

[WayneRyan]

JB,

Use the Search Facility here and look for Conditional Formatting, it can save
you a lot of code.

For instance, you can use your Public Function in the query that feeds your
form. Add a new column to the query:

NewColumn: fncPersonnelExpiredLicenses([License])

You can then use the form's Conditional Formatting to set the [90DayService]
field to the desired color.

Wayne

 

[Junkee Brewster]

Thanks again Wayne - this is fantastic! It will take a little while to fully comprehend the things I can really do with conditional formatting, but I can see it's potential.

Thanks for pointing it out to me - I never really knew of it before. Lucky I'm good at expressions already. This should be easy! Oh the possibilities!

Cheers

 

[WayneRyan]

JB,

Good to hear. In the form's Design View, just highlight the field you want
to alter the color of [90DayService] and use "Expression Is" and just put
your query's new field [NewColumn] which is based on your Public Function.

Enjoy!

Wayne