Autonumber ID not yet visible when starting new record

[AA Arens]

When I start a new record, the <autonumbering> ID in the field is not
updated yet, only when I fill in the first field of that record. How to
solver it?

Bart
Access 2003

[darkroomdevil via AccessMonster.com]

AA Arens wrote:

When I start a new record, the <autonumbering> ID in the field is not
updated yet, only when I fill in the first field of that record. How to
solver it?

Bart
Access 2003

This is normal behavior for Access, I don't think there is any way to change
this directly. Access does not want a value in a field when it is not yet a
record ....

There are two quick things you can do, although I am not sure why this is a
problem unless it is confusing the users, in the control on the form that
shows the autonumber field you could put ...

If you want it to be blank
=IIf(IsNull(AutoNumberID),Null,AutoNumberField)

You could also put this in the control if you need to know the next number
(untested)
=IIf(IsNull(AutoNumberField),DMax("AutoNumberID"," tblTable")+1,AutoNumberID)

Replace AutoNumberID and tblTable with the real names of the field and table

Hope this helps,
Roger

--
Message posted via http://www.accessmonster.com

[salad]

AA Arens wrote:

When I start a new record, the <autonumbering> ID in the field is not
updated yet, only when I fill in the first field of that record. How to
solver it?

Bart
Access 2003

If you want the autonumber filled in when you go to a new record put in
a default value either in the table or the form. For example, you might
have a field called DateTimeStamp. Enter
=Now()
in the table default property.

[Allen Browne]

It makes sense that the autonumber field is not assigned until you start to
add a new record.

You do NOT want to add a new record just because you moved to the new record
in the form. That would cause all sorts of problems such as concurrency
issues (if lots of users are adding records), new records that end up being
saved with all the fields blank, lots of aborted autonumber values, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AA Arens" <ba***********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

When I start a new record, the <autonumbering> ID in the field is not
updated yet, only when I fill in the first field of that record. How to
solver it?

Bart
Access 2003

[Rick Brandt]

salad wrote:

AA Arens wrote:

When I start a new record, the <autonumbering> ID in the field is
not updated yet, only when I fill in the first field of that
record. How to solver it?

Bart
Access 2003

If you want the autonumber filled in when you go to a new record put
in a default value either in the table or the form. For example, you
might have a field called DateTimeStamp. Enter
=Now()
in the table default property.

Having a default value assigned would not cause the AutoNumber to show up any
sooner than it otherwise would.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[salad]

Rick Brandt wrote:

salad wrote:

AA Arens wrote:

When I start a new record, the <autonumbering> ID in the field is
not updated yet, only when I fill in the first field of that
record. How to solver it?

Bart
Access 2003

If you want the autonumber filled in when you go to a new record put
in a default value either in the table or the form. For example, you
might have a field called DateTimeStamp. Enter
=Now()
in the table default property.

Having a default value assigned would not cause the AutoNumber to show up any
sooner than it otherwise would.

Brain freeze. You are correct. The person could do something like this
in the OnCurrent event
Private Sub Form_Current()
If Me.NewRecord Then Me.DateTimeStamp = Now()
End Sub

[AA Arens]

For one form, I indeed use Date. So, that's solved by the trick. But at
another, no date or time and instead of that I want it jumps to a combo
and want it fills an empty string. I cannot use "", however.
I use: If Me.NewRecord Then Me.CompanyName = "".

[Allen Browne]

It makes no sense to assign a zero-length string to the company name field
if you have moved to a new record.

Assuming that CompanyName is a bound field, and that this code is in the
form's Current event, this is very bad practice. In fact, you don't want to
even allow a zero length string into the field of your table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AA Arens" <ba***********@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

For one form, I indeed use Date. So, that's solved by the trick. But at
another, no date or time and instead of that I want it jumps to a combo
and want it fills an empty string. I cannot use "", however.
I use: If Me.NewRecord Then Me.CompanyName = "".

[AA Arens]

The practice is not so clean, that's correct. However after starting a
new record, the person has to fill it in anyway. But If Me.NewRecord
Then Me.CompanyName = "" gives an error.
Related to that, when I push the button "Go to Next Record" (standard
button, made with the toolbox-buttonbar, I can even push it beyond the
last record, and thus creating new records. How to solve this
behaviour? (While keeping my wish as started this topic). "Go to Last
Record" button works.

[Allen Browne]

Assigning a zero-length string makes no sense to me.

The bound control will be Null at the new record until the user fills it in.
Surely that's what is desired without any code or unnecessary dirtying of
the new record.

If you remove the code, your other problems will probably disappear too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"AA Arens" <ba***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

The practice is not so clean, that's correct. However after starting a
new record, the person has to fill it in anyway. But If Me.NewRecord
Then Me.CompanyName = "" gives an error.
Related to that, when I push the button "Go to Next Record" (standard
button, made with the toolbox-buttonbar, I can even push it beyond the
last record, and thus creating new records. How to solve this
behaviour? (While keeping my wish as started this topic). "Go to Last
Record" button works.