[RESOLVED] Number Generation

[preethi_rjs]

hi,

i need to generate numbers automatically.

it needs to be of format 00001,00002 and so on...

i tried the following coding. but its not generating consecutive numbers. instead each time 00001 appears on text5.

c = MsgBox("Wish to add a new Payment", vbExclamation + vbYesNo, "Payment Entry")

If c = vbYes Then

Set rs1 = d.Execute("select no from payment")
Do While Not rs1.EOF
rs1.MoveNext
Loop

Set rs2 = d.Execute("select max(val(no)) from payment")

If IsNull(rs2(0)) = True Then
Text5 = "00001"
Else
Text5.Text = Format(rs2(0) + 1, "0000#")
End If
End If

NOTE: no is second column in the database.

Please help me solve this simple problem.

Thank You.

[shakti5385]

Set rs2 = d.Execute("select max(val(no)) from payment")

Change This

"select max(no) from payment"

[Andrew G]

Maybe this?
Text5.Text = Format(Clng(Text5) + 1, "0000#")

[preethi_rjs]

No.

Both replies are not working.

same 00001 is appearing in Text5.

[danasegarane]

Have a Look at this thread

[Merri]

I don't have anything now for replying, but could you please add [vbcode][/Highlight] tags around your code in the first post and use them from now on?

[preethi_rjs]

hi,

as the number must preceed with zero's i hav given its datatype as Text and not as number.

so it needs to be converted in number format for incrementing by 1.

[Besoup]

you could do

Format$(Clng(text1.text) + 1, "00000#")

[leinad31]

hi,

as the number must preceed with zero's i hav given its datatype as Text and not as number.

so it needs to be converted in number format for incrementing by 1.

Don't do that, make a distinction on how data is stored and how its presented to the user. 00001 is just 1 after all, the leading zeroes are just for aesthetics, just format accordingly when data is being displayed. You can now use the Autonumber feature of your database, if its available. If not, no prob since the Max() function will work with numerics.

[Andrew G]

Have you tried stepping through your code to make sure that IsNull(rs2(0)) is not always true? To set a breakpoint on the line of code you wish to pause at, click on the grey bar on the left next to the line and it should add a dot. When you run the program it will continue to that point and pause, then press F8 to move through the code. To continue normally again press F5. To remove a breakpoint click on the dot.

[ganeshmoorthy]

try this

Dim Sql As String
Sql = "Select Max(Val([No])) As MaxNo From Payment"
rs2.Open Sql, D, adOpenKeyset, adLockOptimistic
Text5.Text = Format(Val(rs2!MaxNo & "") + 1, "00000")
rs2.Close

and I found this is of no use

Set rs1 = d.Execute("select no from payment")
Do While Not rs1.EOF
rs1.MoveNext
Loop