Solved

Access 2000 – Sub-sub form referencing Before Update

Posted on 2002-05-16
10
261 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

728 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