Help: How to prevent moving to the new record when sub form is null?

[swingingming]

Hi, in the NorthWind sample database, when clicking on the next
navigation button on the new order record with nothing on the subform
(order details), we got an order with nothing ordered. How can we
prevent this from happening?
Thanks.
ming

[Allen Browne]

Open your subform's table in design view.
Select the foreign key field (the one that relates to the main form's
table.)
In the lower pane, set the Required property to Yes.

This prevents orphan records, but the user gets no message until they have
finished entering their record in the subform. You can also cancel the
subform's Before Insert event procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
End If
End Sub

--
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.

"swingingming" <mi******@gmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...

Hi, in the NorthWind sample database, when clicking on the next
navigation button on the new order record with nothing on the subform
(order details), we got an order with nothing ordered. How can we
prevent this from happening?
Thanks.
ming

[Br]

swingingming <mi******@gmail.com> wrote:

Hi, in the NorthWind sample database, when clicking on the next
navigation button on the new order record with nothing on the subform
(order details), we got an order with nothing ordered. How can we
prevent this from happening?
Thanks.
ming

Access by design will move to a new record at the end of a recordset on
a form. So its not a blank order, but a new order that doesn't exist as
a record until some information is entered (you'll see the little icon
in the left bar change).

You can stop this behaviour by turning off the Allow Additions property
of the form. But then you won't be able to add new records unless you
design a different way to do it.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[swingingming]

Allen, thank you very much for your reply!!
Your solution sounds good, but I already set the foreign key to
required. And the record navigation button I am referring to is the
ones on the main form.
After I think more about it, I wanted to prevent the record on the main
form to stop saving if another weak entity (OrderDetails, foreign keys:
OrderID--from Orders table, and ProductID from Products table) is
empty. This maybe impossible in Access.
Regards,
ming

[swingingming]

Thank you Bradley,
I think I didn't make myself clear. I want to be able to add new
records, but if there's no records on the subform, then I don't want
the records to be saved.
Regards,
ming

[Br]

swingingming <mi******@gmail.com> wrote:

Thank you Bradley,
I think I didn't make myself clear. I want to be able to add new
records, but if there's no records on the subform, then I don't want
the records to be saved.
Regards,
ming

Not sure how you'd do that as the record on the main form is saved when
it looses the focus to the subform.... and you can't add new records in
the subform until a record is saved in the main form anyway.

I probably missed this but why do you want to do it?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[swingingming]

Thank you Bradley,
You are right about the saving when focus is lost on the subform. I
just think it doesn't make a lot of sense that an order without any
ordered items.
Ming

[Allen Browne]

Kind of a chicken'n'egg problem.

You have to have the main record first, before you can create the subform
record. So you cannot make having a subform record a condition for accepting
the main form record.

--
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.

"swingingming" <mi******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Allen, thank you very much for your reply!!
Your solution sounds good, but I already set the foreign key to
required. And the record navigation button I am referring to is the
ones on the main form.
After I think more about it, I wanted to prevent the record on the main
form to stop saving if another weak entity (OrderDetails, foreign keys:
OrderID--from Orders table, and ProductID from Products table) is
empty. This maybe impossible in Access.
Regards,
ming

[Rick Brandt]

swingingming wrote:

Thank you Bradley,
I think I didn't make myself clear. I want to be able to add new
records, but if there's no records on the subform, then I don't want
the records to be saved.
Regards,
ming

One thing you can do is check for "itemless orders" at startup and prompt the
user so they can either be fixed or deleted.

This is another case where a form event that fired "before you leave" a record
would be handy, but there is no such event. The few times I have tried to
simulate one were partially successful. Basically you have to remove all built
in means by which the user can navigate and then supply your own. In the custom
navigation methods you could run a test for an order with no line items and
raise an error and NOT do the navigation in that circumstance. You would need
the same test in the Unload event.

The difficulty is in capturing all of the built in methods for navigation
without giving up a lot of desirable functionality.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42**********************@per-qv1-newsreader-01.iinet.net.au:

Kind of a chicken'n'egg problem.

You have to have the main record first, before you can create the
subform record. So you cannot make having a subform record a
condition for accepting the main form record.

The subject of the thread suggests a different problem to me, one
that should be able to be handled in the subform's OnExit event. In
order to go to a New record, you have to depart the existing record,
and that will force the OnExit of the subform, and in that, you
should be able to cancel that event if there's no child record,
which will in turn prohibit moving to a new record.

Now, the problem is forcing the OnExit event. That ought to be
doable by setting focus to the subform in the form's AfterUpdate
event.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"swingingming" <mi******@gmail.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

I think I didn't make myself clear. I want to be able to add new
records, but if there's no records on the subform, then I don't
want the records to be saved.

You seem to now be describing a problem different from the one
outlined in your subject.

The BeforeUpdate event of the parent form should be usable to check
if your subform has records, but, of course, you have to have a
parent record saved before you can enter child records.

My suggestion would be to make adding records doable only through a
command button, rather than allowing it through form navigation. The
New Record button would then check to see that you've created at
least one child record.

Likewise, you'd want to call the same verification code in whatever
navigation tools you use on the form. This means that you won't be
able to use the default Access navigation buttons unless you do what
I suggested in another message, setting the focus to the subform in
the main form's AfterUpdate event, then using the subform's OnExit
event to trap for no records in the child form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

It probably is possible to force the focus into the subform in
Form_AfterInsert, and then use the Exit event of the subform control to
DLookup() the subform's table for a related record if the main form is not
at a new record.

There are several disadvantages of that approach, e.g.:
- Forcing focus into the subform regardless of how the record is saved in
the main form, and then not letting the user back out until they enter a
subform record would be quite confusing, I think.
- Can't use the subform's record count (it may be filtered), so it means
hitting the table with the DLookup() every time focus moves out of the
subform control.
- Without setting additional flags, can't know when exiting the subform
control whether the entry was as the result of a new main form record.
- If the user wants to kill the main form entry, they can't get out of the
subform to do so without dealing with the message about no related records.
It might be justified in some cases, but on balance I suspect the cure is
probably worse than the problem.

Additionally, there are problems in Access with connecting the subform
events and the main form's AfterUpdate event. I have a documented case where
the code in Form_AfterUpdate works perfectly unless the record is saved by
moving into the subform. However, if the user tabs into the subform (which
triggers the implicit save of the main form's record) then:
- Access moves the form to a different record after Form_AfterUpdate
completes, and
- the Enter event of the subform control fails to fire.

The particular case provided a number field for the user to reorder the
questions in a survey. In Form_AfterUpdate, if this field changed, we
requeried the form in AfterUpdate (to reflect the new sort order), and moved
back to the record. This all worked fine unless the user tabbed into the
subform, in which case Access moved the main form to a different record
after Form_AfterUpdate completed, and the Enter event of the subform never
fired.

While that's not directly related to this issue, I find it really scary when
the events are unreliable, and particularly where Access is moving the
record pointer after the events compete!

--
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.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn********************************@216.196.97 .142...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42**********************@per-qv1-newsreader-01.iinet.net.au:

Kind of a chicken'n'egg problem.

You have to have the main record first, before you can create the
subform record. So you cannot make having a subform record a
condition for accepting the main form record.

The subject of the thread suggests a different problem to me, one
that should be able to be handled in the subform's OnExit event. In
order to go to a New record, you have to depart the existing record,
and that will force the OnExit of the subform, and in that, you
should be able to cancel that event if there's no child record,
which will in turn prohibit moving to a new record.

Now, the problem is forcing the OnExit event. That ought to be
doable by setting focus to the subform in the form's AfterUpdate
event.

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42**********************@per-qv1-newsreader-01.iinet.net.au:

It probably is possible to force the focus into the subform in
Form_AfterInsert, and then use the Exit event of the subform
control to DLookup() the subform's table for a related record if
the main form is not at a new record.

There are several disadvantages of that approach, e.g.:
[]
- Can't use the subform's record count (it may be
filtered), so it means hitting the table with the DLookup() every
time focus moves out of the subform control.

Well, I disagree with that. You can check to see if the form is
filtered (i.e., .FilterOn = True) and *then* lookup the record count
only when it's filtered. Otherwise, you can use the
..Recordsetclone's recordcount.

And, of course, I never use DLookup, but Trevor's tLookup.

As to your other points, after consideration, I agree that my
solution doesn't really work very well. I think the basic problem is
that the requirements are themselves rather flawed. The only way to
implement them is to completely control the Add and Save events, and
to disable default navigation methods in the form and provide your
own. You don't have to go unbound to get the control, you just have
to avoid the default behaviors.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc