Access 2000 – Sub-sub form referencing Before Update
Posted on 2002-05-16
I have 3 forms (Main form, which has a Sub form – The Sub form, has a sub form)
Forms: frmClients (Main Form)
fsubCourtDetails (Sub form 1)
sfsubPayDetails (Sub form 2, within Sub form 1)
Setup: frmClients = Main data entry, i.e. Persons name(s) Address etc…
fsubCourtDetails = Case Type, Court Dates, and Amount Charged for case
sfsubPayDetails = Paid By, Date Paid, and Amount Paid
I’m setting up some very basic field validations to ensure certain fields have data, and I do this in each form/sub-form(s) Before Update event procedure, which normally works well. However, some users are entering data into the main form, such as the client’s name, then are bypassing sub-form 2, and trying to enter data directly into sub-sub form 3.
So if a user tries to enter an amount paid in “sfsubPayDetails” without having an entry in fsubCourtDetails in wont save the data because of the referential integrating. Don’t ask me why they would enter the amount paid before the amount charged, but they are testing out the database, and this is one of there issues. So I’m trying to apply my standard approach, but I’m continually getting errors to the effect that Access cannot find the control or similar messages.
However rather then using the sub-sub-forms Before Update, I thought I could apply the code below to the specific fields Before Update.
Private Sub txtAmountPaid_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
‘Check that a Case Cost Amount has been entered before proceeding.
If IsNull(Me!Forms!frmClients!fsubCourtDetails!txtCaseCost) Or Me!Forms!frmClients!fsubCourtDetails! txtCaseCost = 0 Then
Call fMsgBox("ENTRY REQUIRED", _
"You must enter an amount for the cost of the case, before proceeding.", _
-1, vbBlue, 0, _
"Please enter an amount now.", _
-1, vbBlack, 0, _