DMax, incrementing number, creating duplicate values

[Kryst51]

The code below is in the click event on a button. Basically it is supposed to assign the next sequential number to a record.

I then have a text box on a form that pulls from a query that concatenates the sequential number with the year in the date field.

It worked at first, but once I got to the tenth record it started duplicating the number 10. So I set the the field in the table to indexed no duplicates. Also, for testing sake, I deleted a couple of in between records out. so that the numbers went like this: 6,7,10. When I created a new record, it assigned it to "8", and then the next, "9". Then when it got to 10 it wouldn't let it and said that whole shebang about there being duplicate records. I thought that the dmax would have started the whole numbering at 11 then 12 then 13.

So I then deleted the record with the 10 in it, and I still got the error, but when I look at the table, the 10 is now in a new record. Any help is greatly appreciated. This number is used as a sequential number for a nonconformance log, basically its for ISO purposes. And since I learned that autonumbers aren't the best to use for this type of purpose I am trying this. Any help is appreciated, even a suggestion for a different way to achieve my purpose is helpful .

EDIT: In the future, deletes will not be allowed, as it is a log, and Needs to remain for audit purposes.

Private Sub btnSaveNCR_Click()
On Error GoTo Err_btnSaveNCR_Click
    Me.EnterDate = Now()
    Me.NCRNUMBER = Nz(DMax("[NCRNumber]", "tblNonconformanceInformation", "Year([EnterDate]) =" & Year_
(Me.txtEnterDate)), 0) + 1
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
    Me.lstNCRNumDisplay.Requery
    Me.lstNCRNumDisplay.Visible = True
Exit_btnSaveNCR_Click:
    Exit Sub
Err_btnSaveNCR_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveNCR_Click
 
End Sub

 

[DannySeager]

So does the field called NCRNumber just contain the incremented number or does it contain the year as well?

can you post a sample?

 

[Kryst51]

It only contains the incremented number. There is a date field which Is set to Now() using the code.

The two fields are concatenated in a query then a text box displays the concatenation so it can be written on the paperwork. Like: 10-2009.

 

[Kryst51]

Here is a condensed version, with no sensitive info in it.

 

[DannySeager]

Change the field from text to number

 

[Kryst51]

I knew it would be something simple! Thank you Thank you Thank you!

 

[DannySeager]

You're welcome