Problem resolved
Thanks RuralGuy and Ghudson.
My problem has been resolved. I was planning to use autonumeber field for generating 5 digit Voucher No. in a accounting database. As per user requirement I have to keep the provision for deletion of Voucher No. And it was user's requirement that after deletion there must not be any gap between numbers.
Instead of autonumber field I have decided to use normal field (Text or integer). I have generated autoincremental Number by folowing functin:
Private Sub AutoNumber()
Dim rs As DAO.Recordset
Dim db As Database
Dim VrNo As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Main", dbOpenSnapshot)
If rs.BOF = True Then
VrNo = rs.RecordCount + 1
Me.txtVrNo = VrNo
Me.txtVoucherNo = "Pymt " & String(5 - Len(VrNo), "0") & VrNo
Me.txtVoucherDate = "01/04/2005"
Else
rs.MoveLast
VrNo = rs.RecordCount + 1
Me.txtVrNo = VrNo
Me.txtVoucherNo = "Pymt " & String(5 - Len(VrNo), "0") & VrNo
Me.txtVoucherDate = rs.Fields("Date")
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
And after delete command I have added the following :
Private Sub Renumber()
Dim db As Database
Dim rs As DAO.Recordset
Dim VrNo As Integer
VrNo = 1
Set db = CurrentDb
Set rs = db.OpenRecordset("Main", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs!VrNo = VrNo
rs!VoucherNo = "Pymt " & String(5 - Len(VrNo), "0") & VrNo
rs.Update
VrNo = VrNo + 1
rs.MoveNext
Loop
rs.Requery
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
This is working well at this time. Any way thanks for your reply.
Regards
Rahulgty
Rahulgty
