473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help with autonumber/dmax()

Ron
Hi All,

Okay, here's the deal: Access2000/WinXP. Have a database that's split,
FE/BE with multiple users having FE and one of those users also has BE
(still split though...). Have a form on FE for adding/editing transactions.
Using TransactionID control as autonumber/primary key for tblTransactions.

A while back, when I mentioned here that I was showing that control to my
users as the transaction number, someone (can't remember who and it doesn't
matter anyway) suggested
that was a bad idea--that I really shouldn't show the autonumber because it
can go whacky (negative numbers/skipping numbers, etc). Sounded okay to me,
as long as there is an alternative. Someone suggested I put another control
that my users DO see and put =DMax("Transnum","tblTransactions")+1 as it's
default. Of course, that'd work great for a single user, but I need
multi...so I have a function that goes:

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me!TRANSNUM = DMax("Transnum", "tblTransactions") + 1
IncrementField = acDataErrContinue
End If
End Function

that's tripped by an On Form error. This doesn't seem to work well.

User A goes into transaction screen, clicks 'add' and begins entering stuff.
But before exiting the form, answers phone. Meanwhile, user B goes into
form, clicks "add", enters stuff and exits form. User A gets off phone,
exits form and that record isn't saved. The record user B made is fine, but
A doesn't get one. If someone goes into form and adds, exits, that one is
fine...all the transnums are in great order, no dupes, but there are 1,
sometimes 2 transactionID numbers missing.

Again, transactionID is primary key, autonumber. This transnum control is
indexed, no dupes and not "required". If I change transnum to allow dupes,
it all works fine (except there are duplicate transnums, which I can't
have).

Where have I messed up, besides the obvious that maybe I missed my calling
as a ditch digger?

Is there another way to arrange a unique number, other than autonumbered
primary key, for a multi-user setup?

Did I mess the whole thing up by having both a autonumber primary key and
this transnum field? If so, what can I do to fix things so that I can avoid
being fired????? ::grin:: (but maybe not grinning to big...)

TIA
ron

May 10 '07 #1
3 5795
Ron wrote:
Hi All,

Okay, here's the deal: Access2000/WinXP. Have a database that's
split, FE/BE with multiple users having FE and one of those users
also has BE (still split though...). Have a form on FE for
adding/editing transactions. Using TransactionID control as
autonumber/primary key for tblTransactions.
A while back, when I mentioned here that I was showing that control
to my users as the transaction number, someone (can't remember who
and it doesn't matter anyway) suggested
that was a bad idea--that I really shouldn't show the autonumber
because it can go whacky (negative numbers/skipping numbers, etc). Sounded
okay to me, as long as there is an alternative. Someone
suggested I put another control that my users DO see and put
=DMax("Transnum","tblTransactions")+1 as it's default. Of course,
that'd work great for a single user, but I need multi...so I have a
function that goes:
Function IncrementField(DataErr)
If DataErr = 3022 Then
Me!TRANSNUM = DMax("Transnum", "tblTransactions") + 1
IncrementField = acDataErrContinue
End If
End Function

that's tripped by an On Form error. This doesn't seem to work well.

User A goes into transaction screen, clicks 'add' and begins entering
stuff. But before exiting the form, answers phone. Meanwhile, user B
goes into form, clicks "add", enters stuff and exits form. User A
gets off phone, exits form and that record isn't saved. The record
user B made is fine, but A doesn't get one. If someone goes into
form and adds, exits, that one is fine...all the transnums are in
great order, no dupes, but there are 1, sometimes 2 transactionID
numbers missing.
Again, transactionID is primary key, autonumber. This transnum
control is indexed, no dupes and not "required". If I change
transnum to allow dupes, it all works fine (except there are
duplicate transnums, which I can't have).

Where have I messed up, besides the obvious that maybe I missed my
calling as a ditch digger?

Is there another way to arrange a unique number, other than
autonumbered primary key, for a multi-user setup?

Did I mess the whole thing up by having both a autonumber primary key
and this transnum field? If so, what can I do to fix things so that
I can avoid being fired????? ::grin:: (but maybe not grinning to
big...)
Instead of using the DefaultValue property, assign the value in the BeforeUpdate
event of the form...

If Me.NewRecord Then
Me!TRANSNUM = DMax("Transnum", "tblTransactions") + 1
End If

BeforeUpdate is the only event that ends with a write to disk so the risk of
collisions is very small.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 10 '07 #2
Ron

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:Kr****************@newssvr22.news.prodigy.net ...
Ron wrote:
>Hi All,

Okay, here's the deal: Access2000/WinXP. Have a database that's
split, FE/BE with multiple users having FE and one of those users
also has BE (still split though...). Have a form on FE for
adding/editing transactions. Using TransactionID control as
autonumber/primary key for tblTransactions.
A while back, when I mentioned here that I was showing that control
to my users as the transaction number, someone (can't remember who
and it doesn't matter anyway) suggested
that was a bad idea--that I really shouldn't show the autonumber
because it can go whacky (negative numbers/skipping numbers, etc).
Sounded okay to me, as long as there is an alternative. Someone
suggested I put another control that my users DO see and put
=DMax("Transnum","tblTransactions")+1 as it's default. Of course,
that'd work great for a single user, but I need multi...so I have a
function that goes:
Function IncrementField(DataErr)
If DataErr = 3022 Then
Me!TRANSNUM = DMax("Transnum", "tblTransactions") + 1
IncrementField = acDataErrContinue
End If
End Function

that's tripped by an On Form error. This doesn't seem to work well.

User A goes into transaction screen, clicks 'add' and begins entering
stuff. But before exiting the form, answers phone. Meanwhile, user B
goes into form, clicks "add", enters stuff and exits form. User A
gets off phone, exits form and that record isn't saved. The record
user B made is fine, but A doesn't get one. If someone goes into
form and adds, exits, that one is fine...all the transnums are in
great order, no dupes, but there are 1, sometimes 2 transactionID
numbers missing.
Again, transactionID is primary key, autonumber. This transnum
control is indexed, no dupes and not "required". If I change
transnum to allow dupes, it all works fine (except there are
duplicate transnums, which I can't have).

Where have I messed up, besides the obvious that maybe I missed my
calling as a ditch digger?

Is there another way to arrange a unique number, other than
autonumbered primary key, for a multi-user setup?

Did I mess the whole thing up by having both a autonumber primary key
and this transnum field? If so, what can I do to fix things so that
I can avoid being fired????? ::grin:: (but maybe not grinning to
big...)

Instead of using the DefaultValue property, assign the value in the
BeforeUpdate event of the form...

If Me.NewRecord Then
Me!TRANSNUM = DMax("Transnum", "tblTransactions") + 1
End If

BeforeUpdate is the only event that ends with a write to disk so the risk
of collisions is very small.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Rick,

Thanks for this. In my limited testing, it seems to work fine where the
other way didn't. I've turned it over to the input people, so we'll see how
it works under stress--they type 90 words a minutes and add transactions
like you wouldn't believe. If I have further problems I'll rattle again.

Oh, one follow up question. I need to keep that same on form error and
function, right? As I understood it, it was still a needed item.

Thanks again,
ron
May 11 '07 #3
"Ron" <ro********************@verizon.netwrote in message
news:ng31i.39$Gm.13@trnddc04...
Hi Rick,

Thanks for this. In my limited testing, it seems to work fine where the other
way didn't. I've turned it over to the input people, so we'll see how it
works under stress--they type 90 words a minutes and add transactions like you
wouldn't believe. If I have further problems I'll rattle again.

Oh, one follow up question. I need to keep that same on form error and
function, right? As I understood it, it was still a needed item.

Thanks again,
ron
I've never used it, but if your insertion rate is high you might. At any rate
it's not hurting anything to leave it there (I don't think).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 12 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Lapchien | last post by:
I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I...
0
by: Andy_HR | last post by:
1. question when i have custom autonumber field... like Nz(DMax("ClanID", "tblClanovi")+1 how can i make it try again if someone else pressed the save button and that namber is already taken,..??...
22
by: Dariusz Kuliñski / TaKeDa | last post by:
I guess that was asked milion times, but I don't have good luck finding working answer on google. Most of the answers tell what to do, but not how. My situation is that I want to have ID in...
5
by: Apple | last post by:
May anyone can teach me how to assign a autonumber, I want to create a number that is starting with year(auto change to year 2006) + autonumber (eg. 2005-0001, 2005-0002)
8
by: mathilda | last post by:
I have a situation where I need to put back in a deleted record to a table whose primary key is an Autonumber. And of course the autonumber has to match what it was before. Any way of saving my...
15
by: NomoreSpam4Me | last post by:
Hi there i have a little problem with my invoice. Here it is: i have a main menu with buttons, one of my button is "Create new invoice", when click on it a form pop up so i can enter my...
2
by: junkaccount | last post by:
Hello, I currently have a field named QuoteNumber in a table named Quotes. The field is set as autonumber and is used to assign sequential numbers as users enter information in the table through...
6
by: Wayne | last post by:
I'm using the following SQL statement to find the next highest autonumber value in a table where "CDUGActID is the autonumber field in the "CDUGActuals" table: SELECT CDUGActuals.CDUGActID,...
3
by: rhaazy | last post by:
I want to know in Access if it is possible to have a table with an auto number that behaves in the following fashion. Group ID A 1 A 2 A 3 A ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.