Auto Number in a Number Field 
Author Message
 Auto Number in a Number Field

Any ideas?

I want an autonumber for customer ID. I don't want it to contain more than
5-6 digits. And I need it to be in a number (not autonumber) field for
linking purposes.

I wrote the following VBA code:

Option Compare Database

Option Explicit
Public gintAge As Integer

Private Sub ProIDButton_Click()
    gintAge = gintAge + 1
    Me.ProID = gintAge & Now()
End Sub

Which works great except for when you close your database. When you reopen
the database the counter goes back to 1. I don't want the counter to ever
reset.

Any help would be appreiciated,

Julie



Wed, 23 Jul 2003 05:54:09 GMT  
 Auto Number in a Number Field
Another approach I've seen and used is to do a simple lookup of the maximum
value of the customer ID in the table, and add 1 to that.  I've seen it
handled by using the DMax() function, and by building a very small query
that returns the Max value of the ID field.  Either of these approaches will
give you a sequential ID number, without reset (unless, of course, you
delete the last customer row, then add another one in.  In this case, you
will re-use the same ID -- I hope you have established table relationships
and enforce referential integrity, elst you'll have a serious headache in
this scenario).

Good luck

Jeff


Quote:
> Any ideas?

> I want an autonumber for customer ID. I don't want it to contain more than
> 5-6 digits. And I need it to be in a number (not autonumber) field for
> linking purposes.

> I wrote the following VBA code:

> Option Compare Database

> Option Explicit
> Public gintAge As Integer

> Private Sub ProIDButton_Click()
>     gintAge = gintAge + 1
>     Me.ProID = gintAge & Now()
> End Sub

> Which works great except for when you close your database. When you reopen
> the database the counter goes back to 1. I don't want the counter to ever
> reset.

> Any help would be appreiciated,

> Julie



Wed, 23 Jul 2003 04:09:13 GMT  
 Auto Number in a Number Field
On all of our databases, we have a table called tcontrol. I would put a
field in this table called LastProID. Every time you change or update ProID,
put that value in LastProID. Before you change or update ProID, get the last
value from the tcontrol table. We've done this because we have other
information we want in the control table as well.

I think either way will work just as well. You might get a performance
benefit from a control table as it will always have only one record.

John


Quote:
> Another approach I've seen and used is to do a simple lookup of the
maximum
> value of the customer ID in the table, and add 1 to that.  I've seen it
> handled by using the DMax() function, and by building a very small query
> that returns the Max value of the ID field.  Either of these approaches
will
> give you a sequential ID number, without reset (unless, of course, you
> delete the last customer row, then add another one in.  In this case, you
> will re-use the same ID -- I hope you have established table relationships
> and enforce referential integrity, elst you'll have a serious headache in
> this scenario).

> Good luck

> Jeff



> > Any ideas?

> > I want an autonumber for customer ID. I don't want it to contain more
than
> > 5-6 digits. And I need it to be in a number (not autonumber) field for
> > linking purposes.

> > I wrote the following VBA code:

> > Option Compare Database

> > Option Explicit
> > Public gintAge As Integer

> > Private Sub ProIDButton_Click()
> >     gintAge = gintAge + 1
> >     Me.ProID = gintAge & Now()
> > End Sub

> > Which works great except for when you close your database. When you
reopen
> > the database the counter goes back to 1. I don't want the counter to
ever
> > reset.

> > Any help would be appreiciated,

> > Julie



Wed, 23 Jul 2003 11:22:23 GMT  
 Auto Number in a Number Field
John,

Someone else told me to make a table also. Majority Rules. So here I am
trying the make this work and I'm not doing so well. You make it sound so
easy in you paragraph, but how do I do it?

Thanks for your help,

Julie


Quote:
> On all of our databases, we have a table called tcontrol. I would put a
> field in this table called LastProID. Every time you change or update
ProID,
> put that value in LastProID. Before you change or update ProID, get the
last
> value from the tcontrol table. We've done this because we have other
> information we want in the control table as well.

> I think either way will work just as well. You might get a performance
> benefit from a control table as it will always have only one record.

> John



> > Another approach I've seen and used is to do a simple lookup of the
> maximum
> > value of the customer ID in the table, and add 1 to that.  I've seen it
> > handled by using the DMax() function, and by building a very small query
> > that returns the Max value of the ID field.  Either of these approaches
> will
> > give you a sequential ID number, without reset (unless, of course, you
> > delete the last customer row, then add another one in.  In this case,
you
> > will re-use the same ID -- I hope you have established table
relationships
> > and enforce referential integrity, elst you'll have a serious headache
in
> > this scenario).

> > Good luck

> > Jeff



> > > Any ideas?

> > > I want an autonumber for customer ID. I don't want it to contain more
> than
> > > 5-6 digits. And I need it to be in a number (not autonumber) field for
> > > linking purposes.

> > > I wrote the following VBA code:

> > > Option Compare Database

> > > Option Explicit
> > > Public gintAge As Integer

> > > Private Sub ProIDButton_Click()
> > >     gintAge = gintAge + 1
> > >     Me.ProID = gintAge & Now()
> > > End Sub

> > > Which works great except for when you close your database. When you
> reopen
> > > the database the counter goes back to 1. I don't want the counter to
> ever
> > > reset.

> > > Any help would be appreiciated,

> > > Julie



Sat, 26 Jul 2003 05:29:40 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Turn off auto numbering but keep the numbers

2. Change manual section numbering to auto numbering

3. How to identify Auto Number Field via DAO

4. How do I reset an Auto Number field ?

5. Acc97:Obtaining Auto Number field value after adding a record

6. Changing an Auto Number Field

7. Creating Custom Auto Number Fields

8. Auto-number/counter-fields - More annoying than usefull?

9. Selecting recordsets based on Auto-Number fields

10. Ignore Auto-number fields from Access?

11. Auto Number Field in ACCESS table

12. Access Auto Number and date fields???

 

 
Powered by phpBB® Forum Software