which event to assign code - for change in text

[What]

I created this code to change some text on my form to indicate if the name record is a current card holder or not. It's overly complicated because all cards expire at the end of the year, but if they applied within the last two months of the year then we give them a card that will last until the end of next year.

After having made it though, I'm not sure what even to put it under!

Also, I'm not sure if the 12/31/[datepart("yyyy",now)] syntax is correct. My background has nothing to do with VB or Access if you couldn't tell...

Any help would be greatly appreciated!

    Dim datecount As Integer    'assigned the # of days between issue date and end of year
    Dim expirecount As Integer  'assigned the # of days before card expires
    Dim issuedate As Date
    
    issuedate = Me.frmNameSubApp.Form!appIssueDate
    datecount = DateDiff("d", issuedate, 12 / 31 / [datepart("yyyy",now)])
    
        If datecount < 60 And datecount > 0 Then
            expirecount = 365 + datecount
        Else
            expirecount = 365 - DateDiff("d", Now, 12 / 31 / [datepart("yyyy",now)])
        End If
        
    If issuedate = Null Then
        Text27.Text = Null
    ElseIf DateDiff("d", issuedate, Now) >= expirecount Then
        Text27.Text = "Current Card Holder"
    Else
        Text27.Text = "Not Current"
    End If

 

[MarkK]

Looks like a few problems with the code, but the best way to find errors is run it.
Run it under the current event of the form...

Private Sub Form_Current()
  If Not Me.NewRecord Then
[COLOR="Green"]    'your code here[/COLOR]
  End If
End Sub

 

[HiTechCoach]

This may help:

Displaying specific dates

If it were me, I would first create some function in a standard module.


1) Calculate the Expiration date

Public Function CalculateExpirationDate(pIssueDate As Date) As Date

Dim datExpireDate As Date

datExpireDate = DateSerial(Year(pIssueDate), 12, 31)

If Month(pIssueDate) > 10 Then

  datExpireDate = DateAdd("yyyy", 1, datExpireDate)

End If


CalculateExpirationDate = datExpireDate

End Function

2) Calculate the Display message

Tis uses the previous function

Public Function CalculAteCardholderText(pIssueDate As Date) As String

    If CalculateExpirationDate(pIssueDate) >= Date Then
        CalculAteCardholderText = "Current Card Holder"
    Else
        CalculAteCardholderText = "Not Current"
    End If
        

End Function

To use the above functions setthe control source for Text27 to be:

= CalculAteCardholderText([issuedate])


Hope this helps ...

 

[What]

Lagbolt, thanks for letting me know what event to place the code into. Will definitely come in handy in the future. Somehow I glanced over the event current in my search...

HiTechCoach! That's exactly what I was looking for. You coded it and everything, awesome! I hate to go asking questions after your laying it out on a silver platter for me, but if you give a mouse a cookie...

The code currently displays "#error" if the issuedate is null, as is the case when the application is not approved, or when a name record has no applications. I tried to add an if statement into the CalculateCardholderText function from many angles without any luck. Adding

ElseIf pIssueDate = Null Then
  datExpireDate = Null

to the calc expire function and adding

ElseIf CalculateExpirationDate(pIssueDate) = Null Then
        CalculateCardholderText = "Not Current"

to the calc card text function produced the same results. I also tried removing both of those if statements and inserting an iff iserror statement straight into the text field and then tried an if iserror statement into the text function. Not sure what else to try!

If the name has multiple applications on file, it appears to only be reading the first one. Ideally the field text would be determined solely by the latest application. Given my table structure I would imagine the highest appID in the frmNameSubApp subform would be the most recent. I'm not sure at what level would be best to approach this one. Any suggestions on how to make the functions look at the most recent app only?

Again, thanks for the code, it's been a huge help!