By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Help with autonumber/dmax()

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
"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 discussion thread is closed

Replies have been disabled for this discussion.