Advise

[Michael Lam]

I have created a simple database for other staffs to keyin a form with an
autonumber generate field

There is a scenario that many different staffs keyin the form nearly the
same time
Is there any way to ensure that all the Reference number generate is unique
(no duplicate)

THX

 

[Tim Ferguson]

Is there any way to ensure that all the Reference number generate is
unique (no duplicate)

Use an Autonumber.


Tim F

 

[Michael Lam]

thx , I have use the Autonumber already

But I when test the form , It generate the number as soon as I start keyin
the info. Is there any way to make the autonumber field generate number
after click the Save button ?

THX
"Tim Ferguson" <[email protected]> ¦b¶l¥ó
¤¤¼¶¼g...

Is there any way to ensure that all the Reference number generate is
unique (no duplicate)

Use an Autonumber.


Tim F

 

[Larry Linson]

thx , I have use the Autonumber already

But I when test the form , It generate the number as soon as I start keyin
the info. Is there any way to make the autonumber field generate number
after click the Save button ?

No. That is not the way AutoNumber works, unless you are using an unbound
form and writing the information from code to a table that has an Autonumber
field. I do not, as a general rule, think it a good idea to use unbound
forms for data, because you have to reimplement functionality that is
included in (and massively tested) Access.

Larry Linson
Microsoft Access MVP

 

[Michael Lam]

Dear Larry

Is there any way to avoid such situation cos that access program will be
accessed by totally three offices (one local and two connected by T1 line )

THx

Michael Lam
"Larry Linson" <[email protected]> ¦b¶l¥ó
¤¤¼¶¼g...

thx , I have use the Autonumber already

But I when test the form , It generate the number as soon as I start keyin
the info. Is there any way to make the autonumber field generate number
after click the Save button ?

No. That is not the way AutoNumber works, unless you are using an unbound
form and writing the information from code to a table that has an Autonumber
field. I do not, as a general rule, think it a good idea to use unbound
forms for data, because you have to reimplement functionality that is
included in (and massively tested) Access.

Larry Linson
Microsoft Access MVP

 

[Tim Ferguson]

Is there any way to avoid such situation cos that access program will
be accessed by totally three offices (one local and two connected by
T1 line )

What situation? The whole point of the way that Autonumbers work is to
guarantee uniqueness regardless of how many users are working at a time. In
fact, the multi-user scenario makes custom autonumbers much harder to
implement.

I don't really understand why you don't like having a number issued when
the user starts to create a record -- what difference does it make as long
as it's different from everyone else's?

I am assuming that you are using a proper split mdb architecture, with each
user having their own copy of the Front End interface and all linked to the
same Back End on the server.


Hope that helps


Tim F

 

[GP03D]

thx for your advise
I think you misunderstand my point of view
I am not try to make the program different from other cos I am a new comer
to access
What I think is that possibility of all users clicking save at the same time
is much rare than the all users start keyin the data
If wrong, please correct

 

[Aleksander]

Hi!

You dont have to use unbound form.

Just set 1 field.name in your table ("Staff" ?)to as somebody wrote
"StuffId"

Its props should be Number ,long integer,No duplicates.

Have form with this table as RecordSource bounded.

Make a textbox "StuffId" and set its ControlSource to "StuffId" field.

In DefaultValue property of textbox "StuffId"

write:

=DMax("StuffId";"Stuff")+1

Where "StuffId" is a field.name and "Stuff" is a table.name

By seting textbox Lock property to "Yes and

Enabled property to "No"

you will enshure that user can not change it.

When a user make a new record ,

DefaultValue property of textbox "StuffId",

will write increased number by 1 - automatickly,

when a user updated record.

I prefer this method because it only works,

when a user is updating record.

In this way you get right number and

not like autonumber which increases number by 1 too,

but is not smart enough to decrease number when

you delete record.

By the way I use lot of unbounded forms in my databases,

using Access as a interface , programming them with

VisualBasic.

Best regards

Aleksander

 

[Rick Brandt]

Hi!

You dont have to use unbound form.

Just set 1 field.name in your table ("Staff" ?)to as somebody wrote
"StuffId"

Its props should be Number ,long integer,No duplicates.

Have form with this table as RecordSource bounded.

Make a textbox "StuffId" and set its ControlSource to "StuffId" field.

In DefaultValue property of textbox "StuffId"

write:

=DMax("StuffId";"Stuff")+1

Sorry, but DMax() + 1 (as a default value) will not work with multiple users.
The default value is assigned as soon as you navigate to the new record position
(before you even start typing). If 5 users all hit "Go To New" they will all
get the same default value if none of them have saved in the interim. In fact,
you can navigate to the new record position and then leave the form sitting
there indefinitely. While sitting there (with your DMax() + 1 default) hundreds
of other records could be entered and saved by other users and your default
value will not update to reflect those other insertions. When you do finally
get around to entering a record and saving the value will be a duplicate.

The best time to use DMax() + 1 is in the BeforeUpdate event as the new value is
calculated milliseconds before the record is committed to the table making the
window of opportunity for duplicates for multiple users as small as possible.
However; as long as there are no requirements that make the use of AutoNumber
undesirable, it should be even more reliable in preventing duplicates.

 

[Larry Linson]

The purpose of AutoNumbers is to ensure uniqueness -- they are for use
internal to your database, for joining related tables and the like. They are
not for display to a user as "meaningful data". Even if you think that the
users will not be disturbed by a break in a
normally-monotonically-ascending-sequence, it is likely that they will...
and contact you or the Help Desk, asking "what happened to the missing IDs?"

You can "fetch" the next number from a shared table, from within a
transaction in which you also update it, I'd think. I've never tried a split
Access database over anything as slow as T-1 -- when our users were on a
WAN, we used a true client-server configuration. Split Access-Jet pumps a
lot of data over the network to be used on anything as slow as a shared 1.5
MB pipe. The slowest network I've used with split Access-Jet was an old 4
MBPS LAN, and you could certainly tell the difference between that and the
10 MBPS and 100 MBPs networks in the same office.

After you implement and test, you may decide that an Access client
application running against SQL Server (the free MSDE that comes with Access
is fine if you don't have too many users... it is deliberately slowed when
more than 5 concurrent internal "batch processes" run, but usually can
handle user audiences in the twenties without undue delays). You'll have to
do some more learning... just as a well-designed, well-implemented
single-user standalone database isn't necessarily and automatically a
well-designed, well-implemented multiuser database, it is also true that a
well-designed, well-implemented multiuser database isn't necessarily and
automatically a well-designed, well-implemented client-server database,
either. Both are likely to require some replanning, redesign, and revision.

Larry Linson
Microsoft Access MVP

 

[Michael Lam]

Dear larry

thx for advise . In my case , dont split database do some help? (actual it
is a simple program with single table only)

Off topic question
If want to become familiar with Access or (client /server application). Any
further official training course suggested or just read book , design ,
test .....etc
I have achieved MOUS Acess 2000 already

THX
"Larry Linson" <[email protected]> ¦b¶l¥ó
¤¤¼¶¼g...
The purpose of AutoNumbers is to ensure uniqueness -- they are for use
internal to your database, for joining related tables and the like. They are
not for display to a user as "meaningful data". Even if you think that the
users will not be disturbed by a break in a
normally-monotonically-ascending-sequence, it is likely that they will...
and contact you or the Help Desk, asking "what happened to the missing IDs?"

You can "fetch" the next number from a shared table, from within a
transaction in which you also update it, I'd think. I've never tried a split
Access database over anything as slow as T-1 -- when our users were on a
WAN, we used a true client-server configuration. Split Access-Jet pumps a
lot of data over the network to be used on anything as slow as a shared 1.5
MB pipe. The slowest network I've used with split Access-Jet was an old 4
MBPS LAN, and you could certainly tell the difference between that and the
10 MBPS and 100 MBPs networks in the same office.

After you implement and test, you may decide that an Access client
application running against SQL Server (the free MSDE that comes with Access
is fine if you don't have too many users... it is deliberately slowed when
more than 5 concurrent internal "batch processes" run, but usually can
handle user audiences in the twenties without undue delays). You'll have to
do some more learning... just as a well-designed, well-implemented
single-user standalone database isn't necessarily and automatically a
well-designed, well-implemented multiuser database, it is also true that a
well-designed, well-implemented multiuser database isn't necessarily and
automatically a well-designed, well-implemented client-server database,
either. Both are likely to require some replanning, redesign, and revision.

Larry Linson
Microsoft Access MVP

 

[Aleksander]

Hi !

Sorry I was not thinking about multiuser.

I was thinking about replacing autonumber with
some alternatives.
One have to learn all the time.

I use BeforeUpdate event myself.

Do you mean that

Privat Sub Form_BeforeUpdate

Me!StuffId=DMax("StuffId","Stuff")+1

End Sub

as alternative to autonumber will not do the job ?

Multiuser logs to database at different time in milliseconds too.

The autonumber can be 1 or 51 if multiuser type something wrong and then
Escaped
from saving record even there exist just 2 records in table.

Best regards

Aleksander

 

[Tim Ferguson]

What I think is that possibility of all users clicking save at the
same time is much rare than the all users start keyin the data

This is exactly the reason why autonumbers are a good solution when many
people are using the database at the same time. Unless you take special
precautions, most do-it-yourself numbers have the risk of allocating the
same number twice when two users click "at the same time". On the other
hand, Access autonumbers will be allocated-and-used immediately so there is
no chance of this sort of clash.

In another part of this thread, I think I understood you to say that you
had not split the database, and all users were sharing the same mdb file.
If this is true, then you are at a very serious risk of corrupting the
database and losing the data. First of all, make sure you have a really
good backup strategy. Second of all, take steps to split the application
into front-end interface mdbs and a back-end one with the data. This will
not affect how the autonumbers are allocated and you will be much safer.

Hope that helps


Tim F

 

[Rick Brandt]

Hi !

Sorry I was not thinking about multiuser.

I was thinking about replacing autonumber with
some alternatives.
One have to learn all the time.

I use BeforeUpdate event myself.

Do you mean that

Privat Sub Form_BeforeUpdate

Me!StuffId=DMax("StuffId","Stuff")+1

End Sub

as alternative to autonumber will not do the job ?

Actually I was making the opposite assertion. BeforeUpdate _will_ work to
a fairly high level of concurrency. It is the use of DMax()+1 *as a
default value* that will not work.