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_BeforeInse rt(Cancel As Integer)
If Me.Parent.NewRe cord 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.

"swingingmi ng" <mi******@gmail .com> wrote in message
news:11******** ************@g4 3g2000cwa.googl egroups.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.

"swingingmi ng" <mi******@gmail .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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*********@Se eSig.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