Subform record validation

[Dorian]

I have a main form for a Case and a subform for entering/updating Payments
relating to the case. I want to disallow saving any payment record where the
payment amount is zero.
I'd prefer that the subform could not be exited where a payment record has a
zero amount. The user must have the option of entering an amount or deleting
the offending payment record.
I've tried various combinations of code in various events and nothing seems
to wok. How is this normally done?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jeanette Cunningham]

The only 2 events on the subform that allow you to cancel are before update
and unload.

Setting the before update's cancel to true when the payment is zero should
stop user from closing the main form without either adding a payment or
running your provided delete routine , *if the subform is dirty*.
If for some reason that won't work, then use the unload event to do a
similar thing.

Something simple like

Private Sub BeforeUpdate(Cancel As Integer)
If isNull(Me.[PaymentControlName]) Then
Cancel = True
End If

should work to stop the record being saved with an empty payment if the
subform is dirty when user clicks close on the parent form.

When user clicks on the main form, the subform's before update event will
fire if the subform is dirty.
However if the subform is not dirty but the payment is still blank, the
before update event will not fire and you probably need to use the unload
event instead.

If you want to delete the record with an empty payment, you will need to
write some more code.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Dorian]

Thanks for the advice. I was hoping for some solution without having to first
save the record and then delete it, but is seems impossible. Anyway I have it
working now.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

The only 2 events on the subform that allow you to cancel are before update
and unload.

Setting the before update's cancel to true when the payment is zero should
stop user from closing the main form without either adding a payment or
running your provided delete routine , *if the subform is dirty*.
If for some reason that won't work, then use the unload event to do a
similar thing.

Something simple like

Private Sub BeforeUpdate(Cancel As Integer)
If isNull(Me.[PaymentControlName]) Then
Cancel = True
End If

should work to stop the record being saved with an empty payment if the
subform is dirty when user clicks close on the parent form.

When user clicks on the main form, the subform's before update event will
fire if the subform is dirty.
However if the subform is not dirty but the payment is still blank, the
before update event will not fire and you probably need to use the unload
event instead.

If you want to delete the record with an empty payment, you will need to
write some more code.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form for a Case and a subform for entering/updating Payments
relating to the case. I want to disallow saving any payment record where
the
payment amount is zero.
I'd prefer that the subform could not be exited where a payment record has
a
zero amount. The user must have the option of entering an amount or
deleting
the offending payment record.
I've tried various combinations of code in various events and nothing
seems
to wok. How is this normally done?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".

.