Solved

Access 2000 – Sub-sub form referencing Before Update

Posted on 2002-05-16
10
256 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now