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
