Solved

Access 2000 – Sub-sub form referencing Before Update

Posted on 2002-05-16
10
260 Views
Last Modified: 2012-05-04
Access 2000:

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)
'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd
   
    ‘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, _
            "", _
            "", 0)

        Me!Forms!frmClients!fsubCourtDetails!txtCaseCost.SetFocus
        Exit Sub
    End If
   
    'ErrorHandlerStart
PROC_EXIT:
    Exit Sub
   
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
    'ErrorHandlerEnd

End Sub


Cheers,

Dave
0
Comment
Question by:DWB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7014422
You might try the approach of locking the inner sub-form's fields UNTIL the necessary data has been entered in the outer-sub-form (that way a user CAN'T enter data before it would be valid to do so).  This applies for Maini-Form to subform1 relationship, as well as to the Sub-form1 to sub-form2 relationship.  SubForm 1's fields are LOCKED until the required entry is made on the main form (and by implication, sub-form 2 is ALSO locked at that point).  Then Sub-form 2 remains locked until the required field is entered on sub-form1.

See if you can work out that scenario.
0
 

Author Comment

by:DWB
ID: 7014637
Ok, I’ve reviewed all my Access Books, I’ve applied, removed, and reapplied Record Locking options, and I’ve even scoured through some other Access site, and here I am with my tail between my legs :-(

Hints are welcome :-)
0
 
LVL 1

Expert Comment

by:dynamictiger
ID: 7014922
I use visible.  subform 1 Property visible is set to false, until all required fields are complete in main form.  This should work for your set up.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:DWB
ID: 7015053
I won’t be able to try it until tomorrow afternoon, as I have to head out for a bit.  I'll tell you how I make out tomorrow.

Cheers,

Dave
0
 

Author Comment

by:DWB
ID: 7015260
I finished early :-)

I tried several variations all without success, the last version seen below:


Private Sub txtCaseCost_AfterUpdate()
   
    If IsNull(Me!fsubCourtDetails!txtCaseCost) Or Me!fsubCourtDetails!txtCaseCost = 0 Then
        Me!fsubCourtDetails!sfsubPayDetails.Visible = False
    Else
        Me!fsubCourtDetails!sfsubPayDetails.Visible = True
        End If

End Sub



Can you please provide some further guidance?

On the main form I only require one field to be filled, and the sub-form requires two fields filled, prior to opening the sub-forms, sub form.

Example:

Main form:       frmClients          txtSurname

SubForm:     fsubCourtDetails     cboCaseTypeID    and   txtCaseCost

SubSubFrom:     sfsubPayDetails     cboMethodPaid  and  txtAmountPaid
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7015425
DWB - not RECORD locking, but actually set the .LOCKED property of the controls.  When a control (TextBox for instance) has .Locked = True, then THAT control CANNOT be changed by the user typing into the control .  A Locked ComboBox can be scrolled (to review the entries), but an entry cannot be selected.  Look up the Locked property in the Access Help System (NOT RECORD LOCKING)

Arthur Wood
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 50 total points
ID: 7015429
in design mode:

  fsubCoutDetails
     cboCaseTypeID.Locked = True
     txtCaseCost.Locked = True

  sfsubPayDetails
     cboMethodPaid.Locked = True
     txtAmountPaid.Locked = True

then in the main form:
    Private Sub txtSurname_BeforeUpdate(Cancel as Integer)
       Me!fsubCourtDetails!cboCaseTypeID.Locked = False
    End Sub

in fsubCourtDetails:
    Private Sub cboCaseTypeID_Click()
       txtCaseCost.Locked = False
    end Sub
 
    Private Sub txtCaseCost_BeforeUpdate(Cancel as Integer)
       Me!fsubCourtDetails!sfsubPayDetails.cboMethodPaid.Locked = False
    End Sub
   
and finally in sfsubPayDetails:

    Private Sub cboMethodPaid_Click()
       txtAmountPaid.Locked = False
    end Sub

That way the user MUST work down through the "proper" sequence to enter the Amount Paid.

Arthur Wood
 
0
 

Author Comment

by:DWB
ID: 7017022
Arthur,

Sorry for any delay, I was out all day and just returned.

I just tried the code you provided, however I'm sure I must have done something wrong.  All is in the locations you indicated accept:

 fsubCoutDetails
    cboCaseTypeID.Locked = True
    txtCaseCost.Locked = True

 sfsubPayDetails
    cboMethodPaid.Locked = True
    txtAmountPaid.Locked = True

I was not sure where to place this code, so I entered it into the respective sub-forms On Open Event and I believe that’s where my problem may be.

I appreciate your patience with me, and if once again you would be so kind as to lead me by the hand, I would be grateful.

Dave.
0
 

Author Comment

by:DWB
ID: 7028058
I'm still unclear as to how to correctly apply the following within the sub-forms indicated:

fsubCoutDetails
   cboCaseTypeID.Locked = True
   txtCaseCost.Locked = True

sfsubPayDetails
   cboMethodPaid.Locked = True
   txtAmountPaid.Locked = True

Cheers,

Dave
0
 

Author Comment

by:DWB
ID: 7029771
Arthur,

I'm afraid I ran out of time on this issue, however, since you provided a possible solution, I’ll issue you the points: it’s just unfortunate that my skill level left me in a frustrating position.

Theirs always a tomorrow and I’m sure I’ll need yours or another expert’s guidance in the future.

Keep the faith,

Dave
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question