Return index number

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

I have an unbound form. When I save the record I want to
have the index number returned to a text box on the form.
Of course if I close the form and re-open it to the same
record I can then get the index number but I want to use
it as soon as the record is saved, not when I come back
in. Does anyone know how I can get the number rturned
immediately after save?
 
Hi,
By Index number do you mean an Autonumber?
After your save record code runs, you can get the new
index number with the DMax function and display the result
in a text box.
Although if this is a multi user app, that method may not be
reliable.
 
Yes, Autonumber. I've thought about this method but it
will be multiuser and so I can't do it that way. Any
other ideas?
 
sam said:
Yes, Autonumber. I've thought about this method but it
will be multiuser and so I can't do it that way. Any
other ideas?
Click to expand...


To generate a number series in a multi-user environment, here's a
solution I use all the time.

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
 
Back
Top