displaying last record

[Junior]

On a form [Form1] bound to [table1].
How can i display the last record in ([Table1].[controlnumber]) on a textbox
or list box on [Form1] say [txtlast]
I want to display the last controlnumber in [table1] so the user will know
the last control number used and
can then enter the next control number in a different textbox
I thought about using a subform but want to know if there is an
easier/better way.

 

[John Mishefske]

If the "last" controlnumber is the one with th maximum value, then you
could give your text box a controlsource expression like

=DMax("controlnumber", "Table1")

You could also set the textbox for the *new* number to

=DMax("controlnumber", "Table1") + 1

but this wouldn't work in a multi-user environment since more than one user
could get the same number.

The common solution is to create a table to track the max number. The lock the
table while retrieving the number and incrementing it and saving it back to
the table - then release the lock.

 

[Dirk Goldgar]

You could also set the textbox for the *new* number to

=DMax("controlnumber", "Table1") + 1

but this wouldn't work in a multi-user environment since more than
one user could get the same number.

True, plus that wouldn't work for storing the controlnumber in the
table. Instead, one would have to use a bound control and assign the
result of the DMax expression via code at some point -- the BeforeInsert
event would work if this is a single-user database.

The common solution is to create a table to track the max number. The
lock the table while retrieving the number and incrementing it and
saving it back to the table - then release the lock.

This is the best solution I know of for a multiuser database.