How to create and track a unique number with vba

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???



Comments

  • 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])
  • 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

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories