How to create and track a unique number with vba

jspisak777

I have a database that I use a function to create inspection report number by setting the date in a specific format and extension number on the end. Here is what I have so far:

Public Function NextAutoNum() As String
Dim I As Integer
Dim D As Date
Dim MySQL As String



D = DLookup("[LastDate]", "tblICNNumber")
I = DLookup("[LastNumber]", "tblICNNumber")
If D = Date Then
I = I + 1
Else
D = Date
I = 1
End If

MySQL = "Update tblICNNumber SET[LastDate] = Date()"
CurrentDb.Execute MySQL
MySQL = "Update tblICNNumber SET[LastNumber] = " & I & ""
CurrentDb.Execute MySQL


NextAutoNum = Format$(Year(D), "0000") & Format$(Month(D), "00") & Format$(Day(D), "00") & "-" & Format$(I, "000")
End Function

It will give me an inspection number in a format such as 20090424-001 for the first inspection conducted on the 24th of April. It works great as longer as inspections are inputted on time, but if a inspection is inputted several days later, I don't know how to draw the last extension number inputted on the previous date. I could just continue to assign the control corresponding to the date the inspection was inputted, but I would like to have the control correspond to the date the inspection was performed. Any ideas???

shasur

Do you want to retrieve the last number from DB?
VBA Tips & Tricks ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])

C# Code Snippets ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link])

jspisak777

I got that problem fixed, but now I have a different problem. The function works great but it doesn't account for the possibility a deleted record. If several records are entered on the same day, one of them deleted, and a new report added the same day it assigns a duplicate number of the last report. Is there a way to code that if a duplicate is entered, add 1 to the assigned number? Here is the code I have:

Option Compare Database
Public Function NextAutoNum() As String
Dim I As Integer
Dim D As String
Dim MySQL As String
D = [Forms]![frm1EvalMainHistory_CI]![EvalDate]

Dim CN1 As ADODB.Connection
Set CN1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = CN1

MySQL = "SELECT Count(tbl1EvalMainHistory.ICN) AS CountOfICN"
MySQL = MySQL & " FROM tbl1EvalMainHistory"
MySQL = MySQL & " WHERE tbl1EvalMainHistory.EvalDate= #" & Forms!frm1EvalMainHistory_CI!EvalDate & "#"

myRecordSet.Open (MySQL)

I = myRecordSet!CountofICN
I = I + 1

NextAutoNum = Format$(Year(D), "0000") & Format$(Month(D), "00") & Format$(Day(D), "00") & "-" & Format$(I, "000")

End Function

Thanks for any help you can provide.

Joe