Good Morning:

I have a problem autoincrement a field "OrderNumber", I have a Listview with four column: "QTY", "Description", Price:

This is my code to save the Listview Item to the database Item:

Private Sub cmdSave_Click()

Dim rs As ADODB.RecordSet: Set rs = New ADODB.RecordSet
Dim Conn As ADODB.Command: Set Conn = New ADODB.Command

With Conn
.ActiveConnection = DataBaseServer
.CommandType = adCmdText

End With


Dim SQLQuery As String
Dim i As Integer




For i = 1 To ListView1.ListItems.count


SQLQuery = "INSERT INTO Item ([OrderNumber],[Qty],[Description],[Price] Values ("
SQLQuery = SQLQuery & Generate_No & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).Text) & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).SubItems("Description")) & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).SubItems(("Price"))) & ", "

SQLQuery = SQLQuery & ")"


Conn.CommandText = SQLQuery
Conn.Execute

Next i


End Sub


this is the function that I call to autoincrement the "OrderNumber"


Private Function Generate_No() As Integer

Dim rst As ADODB.RecordSet: Set rst = New ADODB.RecordSet
Dim Conn As ADODB.Command: Set Conn = New ADODB.Command

With Conn
.ActiveConnection = DataBaseServer
.CommandType = adCmdText

End With


RecordSet.Open "Select Max(OrderNumber) as NextNum from Item", Conn.ActiveConnection, adOpenStatic, adLockOptimistic

Generate_No = RecordSet("NextNum") + 1

Rst.Close

Set Rst = Nothing


End Function


The Problem is that this generate for each number row an autoincrement number

I need One OrderNumber For all Items when i saved and I need to generate this secuence 00000-99999

I saw somes posted about this here but i found some that can fix with my

Please I'll Appreciate you help

Elvis Cabral