Open Form Last Record

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

How do you open a Form {Data Entry only) to show the next avaiable
number in ID field when it opens. I'm not using autonuber. I want the
form o be Data Entry ready but showing the next available number so that
the customer doesn't have to look for the next number.
Thanks
DS
 
On the load property of the form write the following

me.ID = dmax("ID", "MyTable") + 1
 
Hi Ofer,

To do this in access 2000, would a person r click on the corner of the form
in the design view and then go to the properties and find the "on load"
event and then click on the three dots to go into the code builder and type
that in between the Private Sub and End Sub space?

Now, does that say The ID on the current form = the highest ID number in the
table the form is bound to and then add 1 to that number?

Thanks,
Linda
 
I think that will need to be in something other than the Load event, and it
needs to contain a way of checking to see if there is already a value in the
field. If multiple users could be adding records at the same time you could
use the Before Update event. If there is to be a single user only you could
use the Current event. You could set the default value of the field to 0,
then add something like this to the appropriate event:

If Me.ID = 0 Then
Me.ID = Nz(DMax("ID", "MyTable") + 1)
End If

The Nz will assure that the first record is 1 rather than 0. Without
checking for the 0 (the default value) the number will increment by 1 when
you scroll backward through the records, or at any time that you go to a new
or existing record.

There are other valid approaches to this, such as using the control's
Default Value, but this one should work.
 
I just posted, and realized that while my approach mirrors something I have
been working on it may not be a response to the exact question that was
asked. My response had to do with assigning an incremented value to the
field each time a new record is started.
 
You implement it just as you said, this code is good only if you open the
form with add record, and you add a record only once.
so it meen that when the form is open the ID code doesn't have any value,
with the code you insert the value, the next max value
 
Back
Top