Karl said:
You need to use a table with an AUTONUM field.
If you table already has data it will be different. Here
is how you do it if there is no data --
Build a query with no tables. Make the query an append
query to your table.
On the FIELD line type X:4050
On the Append To line select the autonumber field.
Run. It will append one record with that number. All new
records will be sequential after that.
If you have records already then copy to temp file. Empty
the table and modify for the autonumer field. Use an
append query to add your old data just so long that none
of the numbers are greater than your 4050. Then do the
query above to start at 4050.
-----Original Message-----
I have a field that is [PO Number]that starts withj the
number 4050, I want that when I go to the next form,
automatically the [PO Number] changes to 4051, and the
next 4052 and so on.
Thanks in advance
.
I'm sorry to disagree, Karl, but Autonumber is definitelt NOT for
document numbering!
Autonumber is designed only to provide a unique identifier for records
in a table. This is used internally by Access in building relationships
between tables according to your design. The actual values that
Autonumber delivers are unimportant and should never be exposed to
users or have any external meaning at all. Depending on the design and
use of the database, Autonumber will develop gaps, become
random and go negative.
For any number series that you want to control or have available
externally, you must design your own. There are several schemes
available depending on what you want to do.
Here is a scheme that may help you, Fransisco. It works for both
single and multi-user scenarii.
You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other Table.
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb
'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing
This code is designed for Access 97. If you are using a later version,
you will need to use DAO.Recordset and DAO.Database and make sure you
have the appropriate reference set for the DAO Library.
The incrementing number is returned to you in the variable
lngNextNumber.
This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you will
need to store the number value in your Table holding your records.
If, in your system, you need other unrelated number series, just add
another Field to tblSeries and manage it in a similar manner.
hth
Hugh
