Creating a New Record in Sub Form_Current

[Peter Hallett]

I have a form and sub-form, respectively bound to tables which are linked in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete. This it
does by testing whether a constituent ID field has a non-zero value. If not,
then the current record is virgin and is completed directly by the entry of
the appropriate data. If the record is already complete, however, then,
under certain defined conditions, a new blank record is created and presented
for completion.

The VBA used to add the new record and move to it is:–
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is created.
This enables, disables, hides or displays various command buttons which are,
or are not, made available according to the conditions. The exit button, for
example, is not normally enabled until the data entry to a new record is
complete. This conditional interlocking generally works well. There is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete, the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the preceding
record – the completed one which occasioned the creation of the new record.
The command button interlocks are thus set correctly for the new record and
then immediately reset for the conditions applying to the previous record –
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of which
is elegant. The first is to create a Boolean variable, at module level,
called, in my case, ‘FirstPass’. This is set True in Sub Form_Open and it is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach any new
records using an append query and then to navigate to them. It is not a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current. Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[Allen Browne]

Peter, any attempt to use Form_Current to force the entry of a new record is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a new
record, and so the foreign key is null.) As soon as the user tabs out of a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you need.
Perhaps you want a related record created whenever a new record is added in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete. This
it
does by testing whether a constituent ID field has a non-zero value. If
not,
then the current record is virgin and is completed directly by the entry
of
the appropriate data. If the record is already complete, however, then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit button,
for
example, is not normally enabled until the data entry to a new record is
complete. This conditional interlocking generally works well. There is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete, the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach any
new
records using an append query and then to navigate to them. It is not a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current. Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[Peter Hallett]

Allen,

Many thanks for the response. It has reinforced my impression that any
attempt to do things in ways that do not suit Access generally results in
failure. The best thing is to adopt a different approach before you dig a
big hole for yourself. I had, and still have, a nasty feeling about this one
and, although I have yet to find an appropriate solution, I am grateful to
you for confirming that I have little to gain by pursuing the previous path.
I find it disappointing, however, that, although Access provides the command
DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the
present context, to create a new record and go to it. It would have been
gratifying had it done what it says on the label!

There appears to be a little residual misunderstanding. Perhaps I could
clear this by restating the problem in somewhat different terms. A customer
(main form) seeks to buy a new flight voucher (sub-form). On opening, the
sub-form therefore needs to present a virgin record for completion. However,
where customers have already purchased vouchers, these are listed in the
table to which the sub-form is bound. In these cases the sub-form therefore
opens, displaying the first of the existing voucher records.

Of the possible solutions I mentioned earlier, one is to use the sub-form
navigation buttons to create and select the required new record, but this is
clumsy. It involves an additional, and actually unnecessary, manual
operation. Since the ‘new voucher’ option has already been selected, it is
obvious that none of the existing records should be presented for amendment.
On the other hand, it is informative for the navigation bar to show that the
new record is, for example, no. 8 of 8, and also convenient to be able to
scroll back through the existing voucher records to check their details
before issuing a new voucher. It would not therefore be sensible to set the
sub-form SQL so as to exclude existing records. Nor, for the same reason,
would it be advisable to use an unbound form.

A second option is to use an append query to concatenate a new record to the
sub-form table before opening the form. It would then be necessary to issue
a GoTo Last Record command when the sub-form opens. Is that, however, going
to lead to similar trouble in Sub Form_Current?

As to your final suggestion, the above should make clear that the problem is
not one of adding a new sub-form record when a new main form record is
created. Instead, the need is to add a new sub-form record to the complement
associated with an existing main form record and to navigate directly and
automatically to this record. If you have any further thoughts, I would be
grateful to share them.

--
Peter Hallett

Peter, any attempt to use Form_Current to force the entry of a new record is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a new
record, and so the foreign key is null.) As soon as the user tabs out of a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you need.
Perhaps you want a related record created whenever a new record is added in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete. This
it
does by testing whether a constituent ID field has a non-zero value. If
not,
then the current record is virgin and is completed directly by the entry
of
the appropriate data. If the record is already complete, however, then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit button,
for
example, is not normally enabled until the data entry to a new record is
complete. This conditional interlocking generally works well. There is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete, the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach any
new
records using an append query and then to navigate to them. It is not a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current. Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[tina]

you could add code to move to a new record in the subform *when the subform
control is entered*. so when you move a particular record in the main form,
the existing subform records will show; as soon as the cursor enters the
subform, it jumps to a new record - and the existing records are still
available for review. add the following code to the Enter event of the
subform control (on the main form), as

Private Sub SubformControlName_Enter()

With Me!SubformControlName.Form
DoCmd.RunCommand acCmdRecordsGoToNew
End With

End Sub

substitute the correct name of the subform control, of course.

hth

Allen,

Many thanks for the response. It has reinforced my impression that any
attempt to do things in ways that do not suit Access generally results in
failure. The best thing is to adopt a different approach before you dig a
big hole for yourself. I had, and still have, a nasty feeling about this one
and, although I have yet to find an appropriate solution, I am grateful to
you for confirming that I have little to gain by pursuing the previous path.
I find it disappointing, however, that, although Access provides the command
DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the
present context, to create a new record and go to it. It would have been
gratifying had it done what it says on the label!

There appears to be a little residual misunderstanding. Perhaps I could
clear this by restating the problem in somewhat different terms. A customer
(main form) seeks to buy a new flight voucher (sub-form). On opening, the
sub-form therefore needs to present a virgin record for completion. However,
where customers have already purchased vouchers, these are listed in the
table to which the sub-form is bound. In these cases the sub-form therefore
opens, displaying the first of the existing voucher records.

Of the possible solutions I mentioned earlier, one is to use the sub-form
navigation buttons to create and select the required new record, but this is
clumsy. It involves an additional, and actually unnecessary, manual
operation. Since the 'new voucher' option has already been selected, it is
obvious that none of the existing records should be presented for amendment.
On the other hand, it is informative for the navigation bar to show that the
new record is, for example, no. 8 of 8, and also convenient to be able to
scroll back through the existing voucher records to check their details
before issuing a new voucher. It would not therefore be sensible to set the
sub-form SQL so as to exclude existing records. Nor, for the same reason,
would it be advisable to use an unbound form.

A second option is to use an append query to concatenate a new record to the
sub-form table before opening the form. It would then be necessary to issue
a GoTo Last Record command when the sub-form opens. Is that, however, going
to lead to similar trouble in Sub Form_Current?

As to your final suggestion, the above should make clear that the problem is
not one of adding a new sub-form record when a new main form record is
created. Instead, the need is to add a new sub-form record to the complement
associated with an existing main form record and to navigate directly and
automatically to this record. If you have any further thoughts, I would be
grateful to share them.

--
Peter Hallett

Peter, any attempt to use Form_Current to force the entry of a new record is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a new
record, and so the foreign key is null.) As soon as the user tabs out of a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you need.
Perhaps you want a related record created whenever a new record is added in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete. This
it
does by testing whether a constituent ID field has a non-zero value. If
not,
then the current record is virgin and is completed directly by the entry
of
the appropriate data. If the record is already complete, however, then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit button,
for
example, is not normally enabled until the data entry to a new record is
complete. This conditional interlocking generally works well. There is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete, the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach any
new
records using an append query and then to navigate to them. It is not a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current. Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[Allen Browne]

A couple of options:

1. As Tina says you can take the person to the new record with:
With Me.[NameOfSubformHere]
.SetFocus
.Form![NameOfFirstContorlInSubformHere].SetFocus
RunCommand acCmdRecordsGotoNew
End With
The subform's nav. buttons will show "8 of 8" as you desire, but the
existing records will probably scroll up out of view, so the new record is
the only one the user can see, and the cursor is in the first field ready to
enter data.

There is no problem with creating spurious records if the user does scroll
up (to previous records) and down again (to the new record) because you are
not using Form_Current.

2. If you do want to add a new record to the subform completely
programmatically, you could AddNew to the RecordsetClone of the subform, and
then set its Bookmark to that of the new record in the clone set. Again,
this will work fine as long as you are not doing it in Form_Current.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for the response. It has reinforced my impression that any
attempt to do things in ways that do not suit Access generally results in
failure. The best thing is to adopt a different approach before you dig a
big hole for yourself. I had, and still have, a nasty feeling about this
one
and, although I have yet to find an appropriate solution, I am grateful to
you for confirming that I have little to gain by pursuing the previous
path.
I find it disappointing, however, that, although Access provides the
command
DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the
present context, to create a new record and go to it. It would have been
gratifying had it done what it says on the label!

There appears to be a little residual misunderstanding. Perhaps I could
clear this by restating the problem in somewhat different terms. A
customer
(main form) seeks to buy a new flight voucher (sub-form). On opening, the
sub-form therefore needs to present a virgin record for completion.
However,
where customers have already purchased vouchers, these are listed in the
table to which the sub-form is bound. In these cases the sub-form
therefore
opens, displaying the first of the existing voucher records.

Of the possible solutions I mentioned earlier, one is to use the sub-form
navigation buttons to create and select the required new record, but this
is
clumsy. It involves an additional, and actually unnecessary, manual
operation. Since the 'new voucher' option has already been selected, it
is
obvious that none of the existing records should be presented for
amendment.
On the other hand, it is informative for the navigation bar to show that
the
new record is, for example, no. 8 of 8, and also convenient to be able to
scroll back through the existing voucher records to check their details
before issuing a new voucher. It would not therefore be sensible to set
the
sub-form SQL so as to exclude existing records. Nor, for the same reason,
would it be advisable to use an unbound form.

A second option is to use an append query to concatenate a new record to
the
sub-form table before opening the form. It would then be necessary to
issue
a GoTo Last Record command when the sub-form opens. Is that, however,
going
to lead to similar trouble in Sub Form_Current?

As to your final suggestion, the above should make clear that the problem
is
not one of adding a new sub-form record when a new main form record is
created. Instead, the need is to add a new sub-form record to the
complement
associated with an existing main form record and to navigate directly and
automatically to this record. If you have any further thoughts, I would
be
grateful to share them.

--
Peter Hallett

Peter, any attempt to use Form_Current to force the entry of a new record
is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future
versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a
new
record, and so the foreign key is null.) As soon as the user tabs out of
a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you
need.
Perhaps you want a related record created whenever a new record is added
in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are
linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete.
This
it
does by testing whether a constituent ID field has a non-zero value.
If
not,
then the current record is virgin and is completed directly by the
entry
of
the appropriate data. If the record is already complete, however,
then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is
created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit
button,
for
example, is not normally enabled until the data entry to a new record
is
complete. This conditional interlocking generally works well. There
is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete,
the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the
preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module
level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and
it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach
any
new
records using an append query and then to navigate to them. It is not
a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current.
Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[Peter Hallett]

Many thanks to you and Tina for the responses, which should certainly provide
a solution. I am sorry that it has taken me so long to reply, or to rate
your contributions, but my Internet connection has been unusable for over a
week. Apparently, a squirrel was not only using my telephone line for
transport but was also having his breakfast from the insulation. British
Telecom eventually had to rig a temporary cable to bypass the damage - and
that does not happen overnight. I am now quickly taking advantage of the
restored service before the squirrel decides to avail himself of the new food
supply.
--
Peter Hallett

A couple of options:

1. As Tina says you can take the person to the new record with:
With Me.[NameOfSubformHere]
.SetFocus
.Form![NameOfFirstContorlInSubformHere].SetFocus
RunCommand acCmdRecordsGotoNew
End With
The subform's nav. buttons will show "8 of 8" as you desire, but the
existing records will probably scroll up out of view, so the new record is
the only one the user can see, and the cursor is in the first field ready to
enter data.

There is no problem with creating spurious records if the user does scroll
up (to previous records) and down again (to the new record) because you are
not using Form_Current.

2. If you do want to add a new record to the subform completely
programmatically, you could AddNew to the RecordsetClone of the subform, and
then set its Bookmark to that of the new record in the clone set. Again,
this will work fine as long as you are not doing it in Form_Current.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for the response. It has reinforced my impression that any
attempt to do things in ways that do not suit Access generally results in
failure. The best thing is to adopt a different approach before you dig a
big hole for yourself. I had, and still have, a nasty feeling about this
one
and, although I have yet to find an appropriate solution, I am grateful to
you for confirming that I have little to gain by pursuing the previous
path.
I find it disappointing, however, that, although Access provides the
command
DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the
present context, to create a new record and go to it. It would have been
gratifying had it done what it says on the label!

There appears to be a little residual misunderstanding. Perhaps I could
clear this by restating the problem in somewhat different terms. A
customer
(main form) seeks to buy a new flight voucher (sub-form). On opening, the
sub-form therefore needs to present a virgin record for completion.
However,
where customers have already purchased vouchers, these are listed in the
table to which the sub-form is bound. In these cases the sub-form
therefore
opens, displaying the first of the existing voucher records.

Of the possible solutions I mentioned earlier, one is to use the sub-form
navigation buttons to create and select the required new record, but this
is
clumsy. It involves an additional, and actually unnecessary, manual
operation. Since the 'new voucher' option has already been selected, it
is
obvious that none of the existing records should be presented for
amendment.
On the other hand, it is informative for the navigation bar to show that
the
new record is, for example, no. 8 of 8, and also convenient to be able to
scroll back through the existing voucher records to check their details
before issuing a new voucher. It would not therefore be sensible to set
the
sub-form SQL so as to exclude existing records. Nor, for the same reason,
would it be advisable to use an unbound form.

A second option is to use an append query to concatenate a new record to
the
sub-form table before opening the form. It would then be necessary to
issue
a GoTo Last Record command when the sub-form opens. Is that, however,
going
to lead to similar trouble in Sub Form_Current?

As to your final suggestion, the above should make clear that the problem
is
not one of adding a new sub-form record when a new main form record is
created. Instead, the need is to add a new sub-form record to the
complement
associated with an existing main form record and to navigate directly and
automatically to this record. If you have any further thoughts, I would
be
grateful to share them.

--
Peter Hallett

Peter, any attempt to use Form_Current to force the entry of a new record
is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future
versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a
new
record, and so the foreign key is null.) As soon as the user tabs out of
a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you
need.
Perhaps you want a related record created whenever a new record is added
in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are
linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete.
This
it
does by testing whether a constituent ID field has a non-zero value.
If
not,
then the current record is virgin and is completed directly by the
entry
of
the appropriate data. If the record is already complete, however,
then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is
created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit
button,
for
example, is not normally enabled until the data entry to a new record
is
complete. This conditional interlocking generally works well. There
is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete,
the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the
preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module
level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and
it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach
any
new
records using an append query and then to navigate to them. It is not
a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current.
Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?

 

[tina]

I am now quickly taking advantage of the

restored service before the squirrel decides to avail himself of the new food
supply.

lol <g>

Many thanks to you and Tina for the responses, which should certainly provide
a solution. I am sorry that it has taken me so long to reply, or to rate
your contributions, but my Internet connection has been unusable for over a
week. Apparently, a squirrel was not only using my telephone line for
transport but was also having his breakfast from the insulation. British
Telecom eventually had to rig a temporary cable to bypass the damage - and
that does not happen overnight. I am now quickly taking advantage of the
restored service before the squirrel decides to avail himself of the new food
supply.
--
Peter Hallett

A couple of options:

1. As Tina says you can take the person to the new record with:
With Me.[NameOfSubformHere]
.SetFocus
.Form![NameOfFirstContorlInSubformHere].SetFocus
RunCommand acCmdRecordsGotoNew
End With
The subform's nav. buttons will show "8 of 8" as you desire, but the
existing records will probably scroll up out of view, so the new record is
the only one the user can see, and the cursor is in the first field ready to
enter data.

There is no problem with creating spurious records if the user does scroll
up (to previous records) and down again (to the new record) because you are
not using Form_Current.

2. If you do want to add a new record to the subform completely
programmatically, you could AddNew to the RecordsetClone of the subform, and
then set its Bookmark to that of the new record in the clone set. Again,
this will work fine as long as you are not doing it in Form_Current.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for the response. It has reinforced my impression that any
attempt to do things in ways that do not suit Access generally results in
failure. The best thing is to adopt a different approach before you dig a
big hole for yourself. I had, and still have, a nasty feeling about this
one
and, although I have yet to find an appropriate solution, I am grateful to
you for confirming that I have little to gain by pursuing the previous
path.
I find it disappointing, however, that, although Access provides the
command
DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the
present context, to create a new record and go to it. It would have been
gratifying had it done what it says on the label!

There appears to be a little residual misunderstanding. Perhaps I could
clear this by restating the problem in somewhat different terms. A
customer
(main form) seeks to buy a new flight voucher (sub-form). On opening, the
sub-form therefore needs to present a virgin record for completion.
However,
where customers have already purchased vouchers, these are listed in the
table to which the sub-form is bound. In these cases the sub-form
therefore
opens, displaying the first of the existing voucher records.

Of the possible solutions I mentioned earlier, one is to use the sub-form
navigation buttons to create and select the required new record, but this
is
clumsy. It involves an additional, and actually unnecessary, manual
operation. Since the 'new voucher' option has already been selected, it
is
obvious that none of the existing records should be presented for
amendment.
On the other hand, it is informative for the navigation bar to show that
the
new record is, for example, no. 8 of 8, and also convenient to be able to
scroll back through the existing voucher records to check their details
before issuing a new voucher. It would not therefore be sensible to set
the
sub-form SQL so as to exclude existing records. Nor, for the same reason,
would it be advisable to use an unbound form.

A second option is to use an append query to concatenate a new record to
the
sub-form table before opening the form. It would then be necessary to
issue
a GoTo Last Record command when the sub-form opens. Is that, however,
going
to lead to similar trouble in Sub Form_Current?

As to your final suggestion, the above should make clear that the problem
is
not one of adding a new sub-form record when a new main form record is
created. Instead, the need is to add a new sub-form record to the
complement
associated with an existing main form record and to navigate directly and
automatically to this record. If you have any further thoughts, I would
be
grateful to share them.

--
Peter Hallett


:

Peter, any attempt to use Form_Current to force the entry of a new record
is
flawed.

As you found, this event can fire muliple times for one record, and this
behavior changes depending on the version of Access you are using. That
means even if you did succeed in creating a workaround, you are still
creating a maintenance nightmare that is likely to break in future
versions.

More importantly, forcing the entry as soon as the user arrives at a new
record is highly undesirable. It creates blank records (where nothing was
entered.) It creates orphan records (where the parent form is also at a
new
record, and so the foreign key is null.) As soon as the user tabs out of
a
new record, it creates another one. And even if you code around all those
issues, it will create concurrency issues for you - error messages like
"Access stopped the operation because you and another user were ..." In
summary, any code that dirties a record in Form_Current is asking for
trouble, and it makes no sense to force the user into the entry of a new
record just because the visited the end of the records.

There has to be a better approach to achieve whatever it is that you
need.
Perhaps you want a related record created whenever a new record is added
in
the main form? If so, could the main form's AfterInsert event do that for
you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form and sub-form, respectively bound to tables which are
linked
in
a standard one-many relationship. In contrast to the main form,
AllowAdditions is set True for the sub-form. On opening, the latter is
required to determine whether the first record is already complete.
This
it
does by testing whether a constituent ID field has a non-zero value.
If
not,
then the current record is virgin and is completed directly by the
entry
of
the appropriate data. If the record is already complete, however,
then,
under certain defined conditions, a new blank record is created and
presented
for completion.

The VBA used to add the new record and move to it is:-
If (Me![ID] > 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec
([Condition] is a Boolean field, set appropriately elsewhere.)

This works, but only in Sub Form_Current. No new record is created if
this
code is run in Sub Form_Open or Sub Form_Load.

Further code is run, in Sub Form_Current, after the new record is
created.
This enables, disables, hides or displays various command buttons which
are,
or are not, made available according to the conditions. The exit
button,
for
example, is not normally enabled until the data entry to a new record
is
complete. This conditional interlocking generally works well. There
is,
however, a considerable difficulty created by the way in which Sub
Form_Current operates. When the first record is found to be complete,
the
above VBA creates and displays a new record as required. The button
interlocking code next runs but, unbidden, then re-runs for the
preceding
record - the completed one which occasioned the creation of the new
record.
The command button interlocks are thus set correctly for the new record
and
then immediately reset for the conditions applying to the previous
record -
in most cases inappropriately.

I have only been able to find two ways of preventing this, neither of
which
is elegant. The first is to create a Boolean variable, at module
level,
called, in my case, 'FirstPass'. This is set True in Sub Form_Open and
it
is
this value upon which entry to the button interlocking code in Sub
Form_Current is made conditional. The first action of the interlocking
code
is then to set FirstPass False, preventing this code from being re-run.

The second method is not to use the GoToRecord command but to attach
any
new
records using an append query and then to navigate to them. It is not
a
pretty technique either.

Clearly, I do not fully understand the operation of Sub Form_Current.
Can
anyone enlighten me and perhaps suggest a more workmanlike method of
achieving my ends?