Creating unique numbering syst when "adding record" is selected

[Guest]

I created a command button in a form. My intensions are that when the button
is clicked, I would like it to create a new record using the next sequential
number.

For example the first number is: 818471. When a new record is added, the
next number is 818472. Clicked again, 818473 should appear.

In my table I added the first record using the number 818471. When I then
go to the form, the record it there. Great. When I click on the button, the
next number appears, 818472. I enter the text and all seems well until I
click the button again for the next record. This is when my problems begin.
An error appears indicating that the number has already been used. It is
again trying to use the number 818472. I don't know why this is happening.

With help I added the following VB statement to the button. Not sure if
this is correct or needs tweeking:

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click
Dim db As Database
Dim rs As Recordset
Dim intID As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("EpicPolicyNumber")
intID = 0

rs.MoveLast
intID = rs.Fields("Epic Policy Number")
rs.Close

DoCmd.GoToRecord , , acNewRec
Me.EpicPolicyNumber = intID + 1

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub


Any help would be greatly appreciated. Thank you!

 

[aaron.kempf]

use Access Data Projects; MDB is for retards and dumb monkeys


-Aaron

 

[aaron.kempf]

use Access Data Projects; MDB is for retards and dumb monkeys

I mean seriously; if a feature like 'autonumber' isn't working
correctly?

THEN GO ELSEWHERE.


-Aaron

 

[Allan Murphy]

This may help

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click

dim db as dao.database
dim rst as dao.recordset
dim next_policy_number as long

next_policy_number=0
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT fieldname as maxpolicy" & _
" FROM table name" & _
" ORDER BY table name.fieldname DESC")

next_policy_number = rst!maxpolicy + 1

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub

 

[aaron.kempf]

DONT USE DAO FOR ANYTHING

****ing retards

why dont we go back to Windows 95 while we're at it??

EAT SHIT DAO ****TARDS

 

[Guest]

Thanks Allen for your help.

This specific code did not work, however, when chatting with a co-worker I
discovered that by tweeking what I had, it worked. By adding dbOpenDynaset
it worked! Shown below is the code I used. Thanks again!

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim db As Database
Dim rs As Recordset
Dim intID As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("EpicPolicyNumber", dbOpenDynaset)
intID = 0

rs.MoveLast
intID = rs.Fields("Epic Policy Number")
rs.Close

DoCmd.GoToRecord , , acNewRec
Me.EpicPolicyNumber = intID + 1

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click

End Sub