[RESOLVED] AutoIncrement Problem

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

are your table normalized??? it seems that you're trying to save your header data and detail data into one table...
please correct me if im wrong...

Yes i'm going to save in one table.

what is the best way save the data and detail?, Please

Elvis Cabral

I'd recommend following up on what nick2k3 hinted at - you should Normalise your tables. For information, see the article about it in the "Design" section of our Database Development FAQs/Tutorials (at the top of this forum)

I'd also recommend not doing your own auto-increment routine, but instead letting the database system do it (as it deals with several issues you don't seem to have thought of yet).

"as it deals with several issues you don't seem to have thought of yet"

yes mr. MODERATOR this is VERY VERY TRUE!!! some times value is there but not really...heheheh!!!

Thanks Alot Guys

Elvis Cabral