Create auto invoice number with ms access database using dapper in VB.Net

Hi kana250688,

You need to pass the selected Year and Month in the select query to filter the record.

Refer below modified code.

Imports Dapper
Imports System.Configuration
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BDDTRANSACTION.Items.Add("ITEM TRANSFER OUT")
        BDDTRANSACTION.Items.Add("SALES INVOICE")
    End Sub

    Private ConstInvnoStocksout As String '
    Private HeaderInvno As Integer
    Private CurrentMonthYear As String
    Private SOservice As New StocksoutService()
    Private Sub CreateInvno()
        Cursor.Current = Cursors.WaitCursor
        Dim myDate As DateTime = Bdpinvoicefirst.Value
        Dim strTime As String = myDate.ToString("yyMM-")
        Dim newMonthYear As String = myDate.ToString("yyMM")

        ' Set ItemTransferIn based on ComboBox1.Text
        If BDDTRANSACTION.Text = "ITEM TRANSFER OUT" Then
            ConstInvnoStocksout = "TEST-ITO-"
        ElseIf BDDTRANSACTION.Text = "SALES INVOICE" Then
            ConstInvnoStocksout = "TEST-SI-"
        End If
        ' Retrieve the HeaderInvno and MonthYear from the database
        Dim stockout = SOservice.SelectTop(ConstInvnoStocksout, newMonthYear)
        If stockout IsNot Nothing Then
            HeaderInvno = stockout.HeaderInvno
            CurrentMonthYear = stockout.MonthYear
        Else
            HeaderInvno = 0
            CurrentMonthYear = ""
        End If
        '' Check if the month and year have changed
        If stockout Is Nothing OrElse myDate.Year <> stockout.InvnoDate.Year OrElse myDate.Month <> stockout.InvnoDate.Month Then
            'If Not newMonthYear.Equals(CurrentMonthYear) Then
            HeaderInvno = 1
            CurrentMonthYear = newMonthYear
        Else
            HeaderInvno = stockout.HeaderInvno + 1
        End If

        ' Concatenate ItemTransferIn with other parts of the invoice number
        BtxtInvoNo.Text = ConstInvnoStocksout & strTime & HeaderInvno.ToString("000")
        Cursor.Current = Cursors.Default
    End Sub

    Private Sub BDDTRANSACTION_SelectedValueChanged(sender As Object, e As EventArgs) Handles BDDTRANSACTION.SelectedValueChanged
        CreateInvno()
    End Sub

    Private Sub Bdpinvoicefirst_CloseUp(sender As Object, e As EventArgs) Handles Bdpinvoicefirst.CloseUp
        CreateInvno()
    End Sub
End Class

Public Class Stocksout
    Public Property Invno() As String
    Public Property HeaderInvno() As Integer
    Public Property InvnoDate() As Date
    Public Property ConstInvnoStocksout() As String
    Public Property MonthYear() As String
End Class

Public Class StocksoutService
    Private ReadOnly _conn As OleDbConnection
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\WAREHOUSEGB.accdb;Persist Security Info=False;"
    End Function
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function SelectTop(ConstInvnoStocksout As String, monthYear As String) As Stocksout
        Dim sql = $"SELECT TOP 1 HeaderInvno,MonthYear,InvnoDate FROM Stocksout WHERE ConstInvnoStocksout = '{ConstInvnoStocksout}' AND MonthYear = '{monthYear}' ORDER BY HeaderInvno DESC"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stocksout)(sql).FirstOrDefault()
        End Using
    End Function
    Public Sub Insert(invno As String, constInvnoStocksout As String, monthYear As String)
        Dim sql = $"INSERT INTO Stocksout VALUES ('{invno}', '1', '{DateTime.Now}', '{constInvnoStocksout}', '{monthYear}')"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

Screenshot