Re: Auto Number

Hello Mike,
I found a similar problem and made a procedure to increment the primary key
each time a record is entered by 1.
This is attached to the data entry form.
The field refno is my primary key and needs to be incremented by 1 each time
a new record is entered. This reference number is then issued to the staff
member. It is very important that each number is sequential and independant
of the record number.

Since I have used this procedure I have had no further problems.
I hope this helps.

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert

'this procedure increments the refno field
'autoincrement not used so that we do not rely on record numbers
Me.refno = Nz(DMax("refno", "tblDailyLog")) + 1

Exit_Form_BeforeInsert:
Exit Sub

Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert

End Sub


"Mike N." <MikeN451@xxxxxxxxxxxxx> wrote in message
news:%1VGe.10143$sf6.8385@xxxxxxxxxxx
> Hello-
> I have a database that uses an auto number field type that goes out of
> sync periodically. My customer gets a "cannot add record, number already
> in use" error message. I dump the records into a new table starting at
> auto number 1, and sequentially up to about 20,000. Everything is fine
> for a month or two, then the auto number field goes berserk again. Do you
> have any ideas what the client may be doing to cause this problem, or is
> it something in Access 2000? How many records can you add to an Access
> 2000 table before needing a better database?
> Thanks again for your input.
> Mike from Moriches
>


.

Relevant Pages

  • Re: Form Save
    ... You calculate the new value for this Primary Key in the Before Update event ... they get the same calculation. ... Auto Number ... Increment number ...
    (microsoft.public.access.modulesdaovba)
  • Re: Form Save
    ... Yes, that is how Access works, the autonumber primary key is not created ... Auto Number ... Case Number (defaults to the combination of Date and Increment Number) ... I've tried a number of Docmd.Save or Requiry or macro saves, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Uniqueidentifier
    ... This is a primary key. ... insert into autonum values ... >> If you mean a primary key you could use an auto increment number. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Identity Key problem
    ... In lot cases the primary key will be a value that is used just to keep the ... If so I would drop the identity increment and use a control table that ... > where I validate with VBA coding). ... > prevent the the autonumber getting incremented for invalid record entries. ...
    (microsoft.public.access.forms)
  • Re: Form Save
    ... Increment Number is a required field with no duplicates ... they get the same calculation. ... Auto Number ... Increment number ...
    (microsoft.public.access.modulesdaovba)