Howto stop adding new records

[Harry van Rijn]

I have a form with an ORDER recordset and
a subform with an ORDER_DETAIL recordset.
The 2 recordsets are related by ORDER_ID.
Now I can add as much new ORDER records as possible,
WITHOUT any ORDER_DETAIL in it. How can I control this
behaviour so that after 1 new record is added, new additions
are blocked?

I tried to catch the BeforeUpdate event of the ORDER_DETAIL,
and checked here the number of records in the ORDER_DETAIL.
If there are 0 records, I should state: me.undo But when I start
with an empty ORDER table, it seems impossible to add new orders
this way.

How can I solve this ?

 

[Van T. Dinh]

What you proposed is not going to work anyway. There is a
feature called "AutoSave" that will automatically save
the "One" Record on the MainForm as soon as the Focus is
being transferred to the SubForm and once it is saved, you
cannot "undo" the Record using Me.Undo.

Note that in a One-to-Many relationship with R.I.
enforced, you need to create the "One" Record FIRST before
the "Many" Records. The AutoSave feature is specifically
designed for this to ensure that the "One" Record exists
before the "Many" Records are added.

I don't follow why you want to prevent adding Records
after the first one. Do you meant you want only ONE
Record in the Order Table???

HTH
Van T. Dinh
MVP (Access)

 

[Harry van Rijn]

There are in fact 3 'rings'. The outer is the mainform linked to
suppliers, here I select a supplier from a combo. Then a subform with
orders comes into picture (linked by supplier_id) and finally within
this subform an order-details subform appears (linked by order_id).

So what happen when I start the main form : a default supplier is
selected and when there are no orders for him, the order subform with
supplier data appears with an empty order-detail subform.

Here comes my problem: both the order and the order-detail have
navigation buttons (VCR controls). When in the orders I select 'add new
record' (*), then I can check in the orders BeforeUpdate() if this order
has order-details. That's fine. So I can say: cancel the add operation
when there are no details, because one should first fill in the details,
otherwise 'we' don't save the order-details AND also not the order.
However as soon as I click the order-detail subform, somewhere in the
middle, the me.undo (executed by BeforeUpdate) clears the order subform
and then I can't go further.

What I want is this, when a I create a new order, together with an empty
order-detail, then if I select 'add another new order record', this
should be blocked.

 

[Van T. Dinh]

That's what I wrote in my last post: the AutoSave is activated BEFORE the
Focus goes to the Order-Detail Subform. And for the AutoSave to be
executed, the Form_BeforeUpdate Event occurs. If you check for Order-Detail
Records and set Me.Undo if there is no Order-Detail Records, you can never
enter Records for Order or Order-Detail.

Just think: before Order-Detail Records can be created, you need to Order
Record to have been existed. In your case, you put additional condition
that Order Record cannot be created unless there is at least one
Order-Detail Record *already* exists. It is definitely a case of the dog
chasing its tail here.

Basically, the Parent / "One" Record MUST exist BEFORE the Child / "Many"
Records can be created in a One-to-Many relationship with Referential
Integrity enforced.

Also, IMHO, nested Subforms are simply too complicated for the users and I
NEVER used nested Subforms as they confuse the average users (since users
don't have to understand or even know about relationships and Referential
Integrity). In fact, my guess is that you are not sure about R.I. either
since if you understand R.I., you wouldn't set the condition you did.
Perhaps, a quick read of a Relational Database Design Theory would be of
great benefit to you.

If you want to do the way you suggest, you will probably need to use
temporary Tables and then check at the end and write to the permanent Tables
once all the criteria are satisfied. An alternative method is to use
unbound Form / Subform and handle all updates by code. Either way, there
will be a lot of VBA coding to do and there are complications as well.

 

[Harry van Rijn]

I see that I was not clear enough. You are right that there must be
first a parent before you can create childs. That's also what I want,
but only ONCE. I. e. It is perfect when at the selection of a supplier
there comes up 1 order form (the parent) with an empty order-detail
subform (the childs). But if I don't use something like BeforeUpdate
where I check for the existence of order-detail records) then you can
add as many new order records - all with empty order-details - as you
want. I want to limit the addition of new orders to only one, and permit
to create a next new one, only when the current one has 1 or more
order-detail records. This current becomes the 'previous' and so on.
Your suggestion of creating first a temp thing, validate it etc. is of
course better, but it also needs extra programming. I want to use as
much as possible to use the built-in features of msaccess.
Isn't it possible to disable the 'new record' button (in the navigation
VCR) after an empty new record is created, and give free again after
conditions are met (1 parent with 1* childs)?
Yo see in design mode the navigation VCR is invisible.

 

[Jeff Wilkin]

Hi Harry,

from your main form, you can check the record count of your subform control
and set the navigation buttons to false if the record count < 1. Then in the
Form_AfterUpdate event of you subform control, you can reset them to true.
The caveat is that all of the buttons completely disappear everytime you go
to a new record on the main form, so until your subform control has a
recordcount >= 1, you are stuck on the current new record of your Parent
form. Personally, I'd create my own nav buttons which can then be
individually enabled or disabled based upon the record count.

Hope this helps
Jeff

****** Parent Form ********

Private Sub Form_Current()

If Me.NameOfSubFormControl.Form.RecordsetClone.RecordCount < 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

End Sub

****** Subform Control ********

Private Sub Form_AfterUpdate()

If Me.Parent.NavigationButtons = False Then
Me.Parent.NavigationButtons = True
End If

End Sub

 

[Van T. Dinh]

You can't use the Form_BeforeUpdate Event of the "Order" Subform as I
explained previously.

I don't use the VCR buttons but you may be able to use the Form_Current
Event or the "Order" SubForm to check whether it is on a New Record and if
it is, set the AllowAdditions to False (possible???). After that, use the
Form_AfterInsert Event of the "OrderDetail" Sub-SubForm to set the
AllowAdditions back to True.

There may be potential problems with the above approach e.g.:

* User can simply close the Form after creating the "Order" Record and you
still have an "Order" Record without "OrderDetail" Records. When he/she
re-opens the Form, he/she can simply enter another "Order" Record.

* If the User enters a new "Order" Record (AllowAdditions = False), enters
an "OrderDetail" Record (AllowAdditions is back to True and the "New" VCR
button is enabled) then deletes the "OrderDetail" Record so the count is
back to zero but the "New" VCR button is enabled and therefore can create
another new "Order" Record while you don't want him/her to be able. This
can be overcome using the Form_AfterDelConfirm ... However, you can see
that it is getting complicated, messy and the flow of logic starts looking
like spaghetti cooked for an hour ...