DMax Criteria

[Foe]

I'm using the following code to generate a sequential number"

Private Sub Form_Load()
    Me.txtReceivedDate = Now()
    'assign an EmailID when form loads
    Dim CurMax As Long
    Dim NewMax As Long
    'poll the current EmailIDs for the largest number from current year and add 1
    If IsNull(DMax("EmailID", "tblEmails", Year([ReceivedDate]) & " = " & Year(Forms!frmNewEmail!txtReceivedDate))) Then
        NewMax = 1
    Else:
        CurMax = DMax("EmailID", "tblEmails", Year([ReceivedDate]) & " = " & Year(Forms!frmNewEmail!txtReceivedDate))
        NewMax = CurMax + 1
    End If
    'put that new number in the text box
    txtEmailID = NewMax
End Sub

Until recently, I didn't have any criteria on the DMax and it worked perfectly.
It's now been decided that the EmailID should reset back to 1 when the calendar year changes.*

*EmailID is not my primary key - There is a separate autonumber field used as the primary key.

The table I'm using the DMax on has a field named ReceivedDate which stores a Now() when a record is created. The form I'm working in has a textbox named txtReceivedDate that is set to Now() when the form loads as can be seen in the code above.

What I'm trying to use as my criteria in the DMax is to match the Year() of ReceivedDate in tblEmails to the Year() of txtReceivedDate on the form.

For 2013, the current max (CurMax) EmailID is 21. If I set my computers date back to 2012 (where the CurMax is 3) the txtEmailID is still being populated with 22, even though txtReceivedDate shows a 2012 date.

I've tried a handful of permutations of the criteria string:

Year([ReceivedDate]) & " = " & Year(Forms!frmNewEmail!txtReceivedDate)
Year([ReceivedDate]) & "=" & Year(Forms!frmNewEmail!txtReceivedDate)
Year([ReceivedDate])  =  Year(Forms!frmNewEmail!txtReceivedDate)

I've tried a few others long the way that I don't even remember, but there's probably little value in listing all the ways that don't work.

Could someone please help me understand what I'm missing? I feel pretty confident I've got a syntax issue within the criteria since it's the first time I've tried to use one and it contains a couple moving parts.

Once I get this sorted out a follow on question is about dealing with the users changing the txtReceivedDate to a different year. As of now, I have the same code (everything except "Me.txtReceivedDate = Now()") also present in txtReceivedDate_LostFocus(), but I'm not sure that's the best place for it.

Thanks in advance since I know it's only a matter of time before someone helps me get this working as intended.

 

[pbaldy]

Try

DMax("EmailID", "tblEmails", "Year([ReceivedDate]) = " & Year(Forms!frmNewEmail!txtReceivedDate))

 

[Foe]

thanks Paul. That did it. I tested it through the txtReceivedDate_LostFocus() event procedure and txtEmailID would update correctly every time.

May I have your thoughts on the best place to handle updating txtEmailID if a user manually changes txtReceivedDate to a different year? I've considered OnChange and AfterUpdate as well.

 

[pbaldy]

You don't want the change event as it fires with every keystroke. I'd probably use the after update event.

 

[Foe]

Thanks again.

 

[pbaldy]

No problem.