Record Canceled

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Form and a SubForm,,but when I tried to enter a new record and it's
canceled at middle of the process the AutoNumber in next record jump to next
number,,

example: I open the form to enter a new record..this is 42, then all process
in canceled with Scape key,,the when I open the form again appear the
autonumber = 43 , but 42 was never captured,,
can some help me to correct this..


ldiaz
 
ldiaz said:
I have a Form and a SubForm,,but when I tried to enter a new record
and it's canceled at middle of the process the AutoNumber in next
record jump to next number,,

example: I open the form to enter a new record..this is 42, then all
process in canceled with Scape key,,the when I open the form again
appear the autonumber = 43 , but 42 was never captured,,
can some help me to correct this..
Click to expand...

There is no way to "correct" it, so long as you are using an autonumber
field, because it isn't broken. That's the way autonumbers work.
Autonumbers exist to give a unique key to a record, but you aren't
supposed to rely on their content. There is absolutely no guarantee
that autonumbers will be generated and stored in a consecutive sequence
with no gaps. For that matter, a gap in the sequence would also be
created if you went back after the fact and deleted a record. That gap
wouldn't be filled in by the next record to be created, and you probably
wouldn't want it to be.

If you really care about the content of the field, don't use
autonumbers. Instead, generate your own number, using your own
numbering scheme, in the form's BeforeUpdate event. In a single-user
application, this can be as simple as getting the current DMax() of the
field and adding 1 to it. In a multi-user application, it's usual to
have a "next number" table where you store the next number to be used,
and the process of assigning a number to a record involves opening and
locking that table, getting and incrementing the number, updating the
table, and closing it to release the lock.
 
Hi Dirk,could you reply with an example of that you talk please..


I think is the best way to have autonumber with consecutives numbers..

Thank s Ldiaz
 
ldiaz said:
Hi Dirk,could you reply with an example of that you talk please..


I think is the best way to have autonumber with consecutives numbers..
Click to expand...

A little googling would turn up examples like these:

--------- begin single-user solution ------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!ID = Nz(DMax("ID", "MyTable"), 0) + 1

End Sub
--------- end single-user solution ------------

--------- begin multi-user solution ------------
'*** NOTE: this is air code, may not be exactly correct,
' and includes no error-handling. Ideally, you would include
' some logic to automatically retry if tblNextNumber is locked
' at the moment you try to open and lock it.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblNextNumber", , dbDenyRead)
With rs
If .EOF Then
Me!ID = 1
.AddNew
!NextNumber = 2
Else
Me!ID = !NextNumber
.Edit
!NextNumber = !NextNumber + 1
End If
.Update
.Close
End With
Set rs = Nothing

End Sub
--------- end multi-user solution ------------
 
Back
Top