Resetting a value on a field and incrementing with each new record (1 Viewer)

[kaldana77]

I have an ID field that needs to be reset each new year, once records are archived (starting back with 1 then increment with each new record). I am able to get the 1st record entered starting with 1 after I purged (testing) records. However, when I go to add another record, it wants to keep putting it at 1. I was using the DLookup function, which I do understand why it thinks there is a null value in that field, so I was trying to incorporated the Dmax function within my command but it keeps giving me an error like I am missing a ( or a " or '. I am at a loss. Any help would be appreciated. I want it to look at the last value in the table and increment. I'm having brain fog as it's been a very long day today. Any help is very appreciated.

If IsNull(DLookup("ExhibitorID", "[tblExhibitorDetails]", "[ExhibitorID] = " & DMax("[ExhibitorID]", "tblExhibitorDetails")) Then
Me!ExhibitorID = 1
Else
Me!ExhibitorID = DMax("ExhibitorID", "tblExhibitorDetails") + 1
End If

 

[Gasman]

Look at similar threads at the bottom, this gets asked a lot, and recently as well

 

[theDBguy]

Hi. Welcome to AWF!

Not sure an If/Then/Else block is necessary without seeing your setup. Normally, a simple Nz(DMax(),0)+1 is all that's needed.