Eva said:
This is probably impossible but...
A user here needs to type rent invoices and wants a
sequential invoice number generated by Access. Auto-
numbering works great for this, however, a rent could have
more than one invoice if there are separate charges with
separate account numbers for one rent. Ideally, she wants
the invoice number to stay the same until a new "Line 1"
record is created. So, invoices with Line 1, Line 2, Line
3 would all have the same invoice number that was
generated by Access. Any way you can think of to pull
this rabbit out of a hat?
Using Autonumber for any human-meaningful purpose is not a good thing
to do!
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.
This is a well tried method that I use all the time and uses the same
strategy that is used in many accounting systems. 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 main records.
Further, as you will have several child records attached to a single
main record, you should set up two tables with a one-to-many
relationship and referential integrity. You will then need a
main-form/sub-form structure with master/child field linking.
hth
Hugh
