Automatic annual numbering

[MADA BLACK]

Hello VBForums
Hello every one
Please if you can help me
With my Table named (TBL_FACTORY) i have a field named (Case_Year).how to make that when I click Button1 will automatically appear in (TextBox2) if my table is empty the number (0001-2018) .the record will be (0002-2018) .. (0003-2018) and so on. In the next year (2019) the number will be start like this ( 0001-2019).
I make like this :

  Public Function Max_Record_In_FACTORY()
        Dim Number As Integer
        Try
            Dim cmd As New OleDbCommand(" SELECT MAX(Case_Year) FROM TBL_FACTORY  ", Conne)
            If Conne.State = 1 Then Conne.Close()
            Conne.Open()
            Number = cmd.ExecuteScalar
            Conne.Close()
        Catch ex As Exception
            Number = 0
            Conne.Close()
        End Try
        Return Number
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TextBox1.Text = Max_Record_In_FACTORY() + 1
        TextBox2.Text = TextBox1.Text & " - " & TextBox3.Text & ""
    End Sub

And in Form1 Load :

   Dim NowYear As Integer = Date.Now.Year
        TextBox3.Text = NowYear

But the misfortune of this way the numbering will continue in 2019 ..
While I want that in 2019 the numbering will change and will start again from (0001-2019)
Thank you in advance for help
Cordially
MADA

[jmcilhinney]

What type of database? As you're using OleDb, I suspect Access. If it's a database that supports sequences, you can use a sequence. If it's Access or some other database that doesn't support sequences, you can simulate one yourself with a dedicated table and a stored procedure (or whatever Access calls the equivalent). You should do some reading on sequences in databases. Basically, you would store a sequence name and the next value in a table and your procedure would get the next value from the table to return and also increment the value in the table. You'd wrap that in a transaction to ensure that it was atomic.

[MADA BLACK]

Hii jmcilhinney
I have an access database 2010 ....(accdb)
Cordially
MADA

[jmcilhinney]

Then the obvious first step would be to search the web for "access database sequence" or the like.

[MADA BLACK]

Why this Marathon jmcilhinney !! ??
There's no simple code that does that ??
Cordially
MADA

[jmcilhinney]

Like I said, if you use a database that supports sequences then the functionality is built in but if you use a database that doesn't support sequences then you need to create them yourself and that means a bit more work. If you want that functionality without doing the work yourself then use a database that supports that functionality.

[jmcilhinney]

If there's only one client using the database then I guess you could do this in the application rather than in the database. You could add two settings, one an Integer for the year and one an Integer for the sequence number. Each time you want to save a record, you can compare the record year to the year in the settings. If they are the same, get the sequence number from the settings, increment it and save the new value. If they are different, save the new year and also reset the sequence number to 1. Just be aware that anything that messes with those settings will cause issues the data. Having the database manage the sequence is a little more work but safer overall.

[ChrisE]

Hi Mada,

In a Multiuser App it is a bad Idea todo something like that (Post#1)

I supose you could Create a Table in you Database with Numbers allready in there.. something like

create some Numbers..

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim today As String = DateTime.Today.ToString("MMM-yyyy")
        For seq As Integer = 1 To 999
            Dim s As String = String.Format("Invoice-{0}-{1}", today, seq.ToString("D4"))
            ListBox1.Items.Add(s)
        Next
    End Sub

add those Numbers to the Table with another Field True/False for Indication if that Number is used.

my advice ...it's always better to let the MDB do the Numbers for you

HTH

[MADA BLACK]

Thank you very much dear ChrisE
Nice to meet you again
I will take your advice seriously and I will try your method
Thank you very much
Cordially
MADA