DMax in form's Default Value property

[RonL]

Beginner's question please. I have a form bound to tblMyTable. A numeric field is myNumber. For each new record (datasheet view), I want the text box control for that field to increment to the next number higher than the highest number entered so far (which is not necessarily the latest number). So in the Default Value property, I put:

=DMax(Nz("myNumber",0),"tblMyTable")+1

Doesn't seem to work. Let's say I have a bunch of records with myNum values progressing from 1-7 and an additional record - entered earlier - with myNumber=23. In the VBA immediate window, ?DMax(Nz("myNumber",0),"tblMyTable") gives 7. Why not 23?

There's an additional problem in that a new record appears in datasheet view before the current one is completely filled out. The new record has the (incorrect) entry for myNumber already there, even though I haven't tabbed to it yet. Even if the first problem were not occurring, it would still be incorrect (I presume), since the current record hasn't been entered in the table yet. (The data are transferred to the table from the form when you move to a new record, right?)

So general question: what is proper way to increment a bound numerical controller with a next highest number that has never been used at the point of new record creation, even if there is discontinuity in the existing sequence? (I'm aware of autonumber, but don't want to use it. I want user to be able to override the default value.)

Using Access 2010 under Win7x64. Many thanks, -Ron

[June7]

If myNumber is truly a number datatype then DMax should return 23. If it is actually a text datatype then DMax will return 7.

Why do you need this instead of autonumber field?

[RonL]

If myNumber is truly a number datatype then DMax should return 23. If it is actually a text datatype then DMax will return 7.

OMG, I forgot that I had defined it as a text field. Thank you so much.

Why do you need this instead of autonumber field?

I'm not sure whether that would permit the override capability that I want for the user, or how it would affect the possibility that user may delete a record then want to insert one later with the same number for that field that was used originally. (The field is not for id purposes; it identifies an event. (Which btw relates to why I was toying with it as a text field.))

At any rate, I think I now understand that when you define a default value, it appears in the "next" record even before the current record has been finished (datasheet data entry). That could confuse my user. Any way to prevent that? Otherwise, I think I'll look for a way in code. What's the appropriate event? OnGotFocus for the control? I'd like the user to tab through each control, even if data entry is automated for one or two of them, so the user can override. Also, I wouldn't want it triggered for an existing record, so I guess it would have to be conditioned on the field being null. Better suggestions welcome....

-Ron

[June7]

I wouldn't use DefaultValue property for this if the database has multiple simultaneous users.

Custom unique identifier is a common topic in forum. A search should return many threads.

Review:
https://www.accessforums.net/access/...ers-21361.html
https://www.accessforums.net/access/...lue-28208.html

[Missinglinq]

There's an additional problem in that a new record appears in datasheet view before the current one is completely filled out. The new record has the (incorrect) entry for myNumber already there, even though I haven't tabbed to it yet. Even if the first problem were not occurring, it would still be incorrect (I presume), since the current record hasn't been entered in the table yet. (The data are transferred to the table from the form when you move to a new record, right?)

The 'datasheet' you're referring to, in a Split Form, is simply a display device. It is only reflecting what is being displayed in the non-split portion of the Form, it does not necessarily reflect a Record that has been saved, as yet.

And, yes, the data is normally saved when moving to a New/Other Record, closing the Form or closing Access itself.

Linq ;0)>

[RonL]

The 'datasheet' you're referring to, in a Split Form, is simply a display device. It is only reflecting what is being displayed in the non-split portion of the Form, it does not necessarily reflect a Record that has been saved, as yet.

Thanks. BTW, I've got the default view set to datasheet. Since you mention it - and I know I've seen split forms before - but darned if I can find the splitter bar with my cursor on this one. And I have the "split forms" properties set to allow for a split. ... Ah wait, I see I need to set the default view to split form, then in form view I see the split. Hey, thanks! The target user is accustomed to spread sheets, though, so I want to stick with the datasheet view as long as it will accommodate the functionality I have in mind. -Ron