Recordset.AddNew in subform causes Autonumber to Skip

[dch3]

I'm thinking that I already understand why, but Im curious if I'm on target...

I'm using the following line to explicity add a new record to the recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going from 846
to 847 it goes to 848. I'm assuming that the specific reason is tied to the
fact that when you actually add a new record and Access assigns a new
Autonumber that Access actually executes the equivalent of .AddNew itself.
Hence when you're in table view, you see a new row appear with (AUTO NUMBER)
in the field. By explicity adding a new one, I'm causing Access to skip over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab through
the fields and then to a new record. Toss in there that its a new record on a
subform and I couldn't figure out how to use DoCmd.Goto on a subform.

 

[bcap]

If you set the focus to the subform first, and omit the optional ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

 

[dch3]

The subform exists on a form with page breaks that is designed to work as a
wizard. The subform is on one page, the event which results in a new record
is on another. If I change the focus, the Wizard will flip back to the
subform and then back to the other page.

If you set the focus to the subform first, and omit the optional ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

I'm thinking that I already understand why, but Im curious if I'm on
target...

I'm using the following line to explicity add a new record to the
recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going from
846
to 847 it goes to 848. I'm assuming that the specific reason is tied to
the
fact that when you actually add a new record and Access assigns a new
Autonumber that Access actually executes the equivalent of .AddNew itself.
Hence when you're in table view, you see a new row appear with (AUTO
NUMBER)
in the field. By explicity adding a new one, I'm causing Access to skip
over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab through
the fields and then to a new record. Toss in there that its a new record
on a
subform and I couldn't figure out how to use DoCmd.Goto on a subform.

 

[bcap]

Well you didn't say that before, did you...

I guess you could set the filter for the subform to something that no
records meet (primary key = Null, for instance), so then it MUST be on a new
record. Add your record, then remove the filter.

Or, use DAO to insert the record directly into the table, and then requery
the subform.

The one thing I wouldn't do is to operate directly on the form's Recordset.
That way madness lies, in my experience.

The subform exists on a form with page breaks that is designed to work as
a
wizard. The subform is on one page, the event which results in a new
record
is on another. If I change the focus, the Wizard will flip back to the
subform and then back to the other page.

If you set the focus to the subform first, and omit the optional
ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

I'm thinking that I already understand why, but Im curious if I'm on
target...

I'm using the following line to explicity add a new record to the
recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going from
846
to 847 it goes to 848. I'm assuming that the specific reason is tied to
the
fact that when you actually add a new record and Access assigns a new
Autonumber that Access actually executes the equivalent of .AddNew
itself.
Hence when you're in table view, you see a new row appear with (AUTO
NUMBER)
in the field. By explicity adding a new one, I'm causing Access to skip
over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab
through
the fields and then to a new record. Toss in there that its a new
record
on a
subform and I couldn't figure out how to use DoCmd.Goto on a subform.

 

[dch3]

Funny because when I googled Recordset Addnew method problems, the only thing
that popped up was ADO related issues.

Well you didn't say that before, did you...

I guess you could set the filter for the subform to something that no
records meet (primary key = Null, for instance), so then it MUST be on a new
record. Add your record, then remove the filter.

Or, use DAO to insert the record directly into the table, and then requery
the subform.

The one thing I wouldn't do is to operate directly on the form's Recordset.
That way madness lies, in my experience.

The subform exists on a form with page breaks that is designed to work as
a
wizard. The subform is on one page, the event which results in a new
record
is on another. If I change the focus, the Wizard will flip back to the
subform and then back to the other page.

If you set the focus to the subform first, and omit the optional
ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

I'm thinking that I already understand why, but Im curious if I'm on
target...

I'm using the following line to explicity add a new record to the
recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going from
846
to 847 it goes to 848. I'm assuming that the specific reason is tied to
the
fact that when you actually add a new record and Access assigns a new
Autonumber that Access actually executes the equivalent of .AddNew
itself.
Hence when you're in table view, you see a new row appear with (AUTO
NUMBER)
in the field. By explicity adding a new one, I'm causing Access to skip
over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab
through
the fields and then to a new record. Toss in there that its a new
record
on a
subform and I couldn't figure out how to use DoCmd.Goto on a subform.

 

[bcap]

1. Where did I mention problems with Recordset.AddNew? What I'm talking
about is problems with directly manipulating a form's recordset (one of
which you have already encountered, otherwise you wouldn't be here).

2. I'm speak only from my own experience. You of course must do as you see
fit.

Funny because when I googled Recordset Addnew method problems, the only
thing
that popped up was ADO related issues.

Well you didn't say that before, did you...

I guess you could set the filter for the subform to something that no
records meet (primary key = Null, for instance), so then it MUST be on a
new
record. Add your record, then remove the filter.

Or, use DAO to insert the record directly into the table, and then
requery
the subform.

The one thing I wouldn't do is to operate directly on the form's
Recordset.
That way madness lies, in my experience.

The subform exists on a form with page breaks that is designed to work
as
a
wizard. The subform is on one page, the event which results in a new
record
is on another. If I change the focus, the Wizard will flip back to the
subform and then back to the other page.

:

If you set the focus to the subform first, and omit the optional
ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

I'm thinking that I already understand why, but Im curious if I'm on
target...

I'm using the following line to explicity add a new record to the
recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going
from
846
to 847 it goes to 848. I'm assuming that the specific reason is tied
to
the
fact that when you actually add a new record and Access assigns a
new
Autonumber that Access actually executes the equivalent of .AddNew
itself.
Hence when you're in table view, you see a new row appear with (AUTO
NUMBER)
in the field. By explicity adding a new one, I'm causing Access to
skip
over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab
through
the fields and then to a new record. Toss in there that its a new
record
on a
subform and I couldn't figure out how to use DoCmd.Goto on a
subform.

 

[dch3]

The full syntax of the statement at hand being
[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

I took you're response as applying to it. Since techncially it is operating

1. Where did I mention problems with Recordset.AddNew? What I'm talking
about is problems with directly manipulating a form's recordset (one of
which you have already encountered, otherwise you wouldn't be here).

2. I'm speak only from my own experience. You of course must do as you see
fit.

Funny because when I googled Recordset Addnew method problems, the only
thing
that popped up was ADO related issues.

Well you didn't say that before, did you...

I guess you could set the filter for the subform to something that no
records meet (primary key = Null, for instance), so then it MUST be on a
new
record. Add your record, then remove the filter.

Or, use DAO to insert the record directly into the table, and then
requery
the subform.

The one thing I wouldn't do is to operate directly on the form's
Recordset.
That way madness lies, in my experience.

The subform exists on a form with page breaks that is designed to work
as
a
wizard. The subform is on one page, the event which results in a new
record
is on another. If I change the focus, the Wizard will flip back to the
subform and then back to the other page.

:

If you set the focus to the subform first, and omit the optional
ObjectName
argument from DoCmd.GoToRecord, it will operate on the subform.

I'm thinking that I already understand why, but Im curious if I'm on
target...

I'm using the following line to explicity add a new record to the
recordset
of a subform.


[Forms]![frmTrailerActivityUnloadsWizard]![subfrmTrailerActivityUnloadsDetail].Form.Recordset.AddNew

What I've noticed is that the Autonumber skips so instead of going
from
846
to 847 it goes to 848. I'm assuming that the specific reason is tied
to
the
fact that when you actually add a new record and Access assigns a
new
Autonumber that Access actually executes the equivalent of .AddNew
itself.
Hence when you're in table view, you see a new row appear with (AUTO
NUMBER)
in the field. By explicity adding a new one, I'm causing Access to
skip
over
the record that it anticipates adding itself.

MVPS - The code is a part of a wizard and I needed the ability to
automatically add a new record without relaying on the user to tab
through
the fields and then to a new record. Toss in there that its a new
record
on a
subform and I couldn't figure out how to use DoCmd.Goto on a
subform.