Produce invoice number serially

[silversurf]

Hello everyone. I am developing a inventory and pos software myself with vb 6.0 and ms-access as database. In my sales invoice I want to generate the sales invoice number serially. I mean when I load the sales invoice form the txtsinvno text box will automatically generate the sales invoice no adding 1 to the last sales invoice no. I have the following code in my form load :

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim NewInv1 As Integer
Dim newinv2 As Integer
'Dim strsql As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
con.Open
With rs
.Open ("Select sinv_no from tblsinv"), con, adOpenDynamic, adLockOptimistic
'.Open ("Select Max (sinv_no) from tblsinv"), con, adOpenDynamic, adLockOptimistic
 NewInv1 = rs!sinv_no
.MoveLast
newinv2 = NewInv1 + 1
txtinvno.Text = newinv2
txtinvno.Text = Format(txtinvno.Text, "0000000")
End With

please do note that I have tried both Max (sinv_no) and sinv_no but Max (sinv_no) give only 00000000 in the text box. Where I am doing wrong, plz help.

[AndreRet]

''Change this around...

''First move to last record...
.MoveLast

''Then get the value...
NewInv1 = rs!sinv_no

newinv2 = NewInv1 + 1
txtinvno.Text = newinv2

[silversurf]

Thank you sir for replying. I have changed as you said, but the thing is when I load the form I found 0000001 in the txtsinvno box as text. I have 0000003 in the database as the last record I am sure about that. But when I have the code in my way, I got 0000002 in my txtsinvno box. Any further suggestions plz.

[AndreRet]

It seems that the data is not loading by order. Do the following...

.Open ("Select sinv_no from tblsinv ORDER BY sinv_no ASC"), con, adOpenDynamic, adLockOptimistic

ASC (ascending) will load the data from sinv_no 1 to 30 (whatever records) in order from 1 to 30. This means that 30 will be the last record and newinv2 will be 31

[silversurf]

Thanks sir, by adding order by and moving rs.movelast I have done it. I think it will continue for whatever records. Thanks again.