Create Serial Number in Tabular Form

[Guest]

Hi,

I created tabular form that has serial number column. I need to make it as
serial number generated. For example first row will have #1, and then for
second row will have #2.

Please help. Thank You.

 

[Al Camp]

Buthy,
In your table design, make the SerNo field a LongInteger, Key field,
Indexed No Dupes.
On your tabular form, maje the DefaultValue for the SerNo field...
=DMax("[SerNo]","tblYourTable") + 1
Disable and lock the field from editing.
hth
Al Camp

 

[Guest]

Try this for the default value of the control (text box):
=Nz(DMax("[FieldName]","TableName"))+1
To format it as "001", "002", etc. you could add 000 to the Format property
for the control.

 

[Rick Brandt]

Since the OP has specified that he is using a tabular (continuous) form,
then using DMax as a default value will not work. If you try it you will
see the following behavior...

Open form with 1 existing row, ID = 1. Below that the "new row" position
will have the default value of 2 for ID (so far so good). As soon as the
user dirties the new row another new row will appear beneath it and since
the record being worked on is not yet part of the saved recordset the ID
will also default to 2 in the "new new" row. When he dirties the new new
row yet another new row will appear with a default ID of 3. Of course when
the second new row is saved a duplicate key error will be raised forcing the
user to manually change the value to a 3 and the problem repeats itself for
all new insertions.

On a continuous form the only DMax() + 1 strategy that will work is to
assign the value in the form's BeforeUpdate event. Even on a single view
form using DMax() + 1 as a default or assigning it in any other form event
cannot work reliably in a multi-user environment because two users entering
records at the same time will be grabbing the same values and colliding with
each other.

Try this for the default value of the control (text box):
=Nz(DMax("[FieldName]","TableName"))+1
To format it as "001", "002", etc. you could add 000 to the Format
property for the control.

Hi,

I created tabular form that has serial number column. I need to make
it as serial number generated. For example first row will have #1,
and then for second row will have #2.

Please help. Thank You.

 

[Guest]

I see. I didn't try it on a continuous form. This leads me to a related
sort of question. I am similarly assigning a number (although in a single
form), but am doing so in the OnCurrent event. This lets the user see the
number before entering other information, which is necessary for several
reasons. One of these reasons is that after completing a section of the form
the user will use a command button to send an e-mail to the next person
advising them that the form needs attention.
To explain further, the report is a recommendation. The person who makes
the recommendation can click a button to send an e-mail to the manager
informing him that RPT 05-01 has been filed. 05-01 is the DMax number, but
is not the primary key because I feared difficulty in the event that two
people attempt to make a recommendation at the same time. It is not very
likely, but is certainly possible. The manager would then assign somebody to
respond. After the recommendation has been made and the user exits (and thus
saves the record) any further action will not affect the number.
Perhaps the button to send the e-mail, or maybe the After Update event for
the recommendation text box, could contain code that saves the record or
offers the opportunity to undo? It would have to contain some sort of
message in case the number they thought was assigned needs to change. I need
to make this work with as little user intervention and training as is
possible. It needs to be self-explanatory, if for no other reason than that
some people may only use the database once or twice a year, and cannot be
expected to remember details of how to work with it.

Since the OP has specified that he is using a tabular (continuous) form,
then using DMax as a default value will not work. If you try it you will
see the following behavior...

Open form with 1 existing row, ID = 1. Below that the "new row" position
will have the default value of 2 for ID (so far so good). As soon as the
user dirties the new row another new row will appear beneath it and since
the record being worked on is not yet part of the saved recordset the ID
will also default to 2 in the "new new" row. When he dirties the new new
row yet another new row will appear with a default ID of 3. Of course when
the second new row is saved a duplicate key error will be raised forcing the
user to manually change the value to a 3 and the problem repeats itself for
all new insertions.

On a continuous form the only DMax() + 1 strategy that will work is to
assign the value in the form's BeforeUpdate event. Even on a single view
form using DMax() + 1 as a default or assigning it in any other form event
cannot work reliably in a multi-user environment because two users entering
records at the same time will be grabbing the same values and colliding with
each other.

Try this for the default value of the control (text box):
=Nz(DMax("[FieldName]","TableName"))+1
To format it as "001", "002", etc. you could add 000 to the Format
property for the control.

Hi,

I created tabular form that has serial number column. I need to make
it as serial number generated. For example first row will have #1,
and then for second row will have #2.

Please help. Thank You.

 

[Rick Brandt]

I see. I didn't try it on a continuous form. This leads me to a
related sort of question. I am similarly assigning a number
(although in a single form), but am doing so in the OnCurrent event.
This lets the user see the number before entering other information,
which is necessary for several reasons. One of these reasons is that
after completing a section of the form the user will use a command
button to send an e-mail to the next person advising them that the
form needs attention.

Again, the current event is not usable for this if you have multiple users.
A user can navigate to a a new record (your number would appear) and then go
to lunch leaving the form and that number sitting there. Upon returning and
actually entering a record his number could not only have been used by
someone else, but could now be a dozen numbers off from where it should be.

You MUST either use a coding scheme that includes something that identifies
the user creating the record as part of the key or use a strategy where the
time between number assignment and saving to the database is as short as
possible. The BeforeUpdate event provides the shortest interval.

 

[Guest]

I need the number to be assigned by the time the use either prints out the
form or sends an e-mail to the manager advising him that a new recommendation
has been started. There is a command button to prepare an e-mail
(SendObject) and another to print the form. The Click event of either button
could save the record and assign a number, couldn't it? Is there a chance
another user could be doing the same thing at the same time and collide with
the first user? For what it's worth this database is not used all that
often, maybe only about twenty to thirty times a year, but it is similar to
another database (or another portion of this database) that will be used more
often (up to maybe ten or fifteen times per month), and is more complex in
several ways. I am trying to work out the kinks in a relatively simple
project before tackling the other project.
Maybe I'm looking at this the wrong way, and the command button should save
the record, at which point the form's Before Update event, containing the
code to assign the number, would run. Since the number needs to be assigned
before the form is printed or the e-mail is sent it seems that the command
button is a good place for the code, but I don't understand events and in
what order they run as well as I would like. It's a question of the best
place to put the code.
I think that I could figure out a way of assigning a provisional number (in
an unbound text box or something) if need be, then replacing it when the real
number is assigned. If the real number is different from the provisional one
the user could be notified. Maybe it won't come to that, but I need to be
prepared.
If it would help, a field other than the assigned number could be the
primary key.