Help with set recordcount autonumbering

[Brian Goldhammer via AccessMonster.com]

I have the following function set so when a user opens a form to input new
data, a field I have called "DatabaseRecord" is automatically set off of
total records in the table +1. The problem I have is that when two users try
to input data at the same time, the same record number is being pulled for
both entries. Is there a way to set that once a record is open (or being
used), that the function can go to the next available record #?

Thanks,
Brian
------------------------------------------------------------------------------
----------------------------------------------------------------
Function MyCountRecords() As Long

'Establish all of the necessary items to use
Dim dbs As Database
Dim rstOrders As Recordset
Dim lngTotal As Long
Dim Total As Long

Set dbs = CurrentDb
Set rstOrders = dbs.OpenRecordset("Tbl_Orders")
rstOrders.MoveLast
Total = rstOrders.RecordCount
Total = Total + 1
MyCountRecords = Total

End Function

 

[Alex Dybenko]

Hi,
i suggest you to use form's BeforeUpdate event to run this function, then
you will get a record count just before saving new record

 

[David C. Holley]

Why aren't you using the AUTONUMBER functionality that's buildt into
Access?

 

[Brian Goldhammer via AccessMonster.com]

David,

I was working with a lot of old data (92000 records) going into a new
database and some of the old record numbers weren't all numerical. I did
however use some append queries to get everything numerical and transferred
into a new table where the AUTONUMBER is now working beautifully.

Thanks for your advice,
Brian

Why aren't you using the AUTONUMBER functionality that's buildt into
Access?

I have the following function set so when a user opens a form to input new
data, a field I have called "DatabaseRecord" is automatically set off of

[quoted text clipped - 25 lines]

 

[David C. Holley]

Old data will do it to you everytime.

David,

I was working with a lot of old data (92000 records) going into a new
database and some of the old record numbers weren't all numerical. I did
however use some append queries to get everything numerical and transferred
into a new table where the AUTONUMBER is now working beautifully.

Thanks for your advice,
Brian

Why aren't you using the AUTONUMBER functionality that's buildt into
Access?

I have the following function set so when a user opens a form to input new
data, a field I have called "DatabaseRecord" is automatically set off of

[quoted text clipped - 25 lines]