1,105,447 Community Members

Produce invoice number serially

Member Avatar
silversurf
Junior Poster in Training
96 posts since Jul 2012
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 
''Change this around...

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

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

newinv2 = NewInv1 + 1
txtinvno.Text = newinv2
Member Avatar
silversurf
Junior Poster in Training
96 posts since Jul 2012
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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

Member Avatar
silversurf
Junior Poster in Training
96 posts since Jul 2012
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 1 Year Ago by AndreRet
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article