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.