Solved

MS Access 2010 subforms. If not visible are they still enabled?

Posted on 2014-01-15
8
636 Views
Last Modified: 2014-01-16
Hi,

I have a main form with 2 x subforms in it, linked by Client_ID.
The main form contains Client Data. (datasource=tblClients) The subforms enable different payment charges to be made (both subforms have the same datasource i.e. tblDebitNotes)

When the main form is first opened I have set the two subforms NOT to be visible.

I have a combo control the user can select which payment type they wish to apply (single payment or Pro Rata payment) and this in turn then shows the correct subform for that payment to allow the user to enter details into.

My question is:
If a subform is NOT visible is it still enabled?

I ask this because i need to validate that data has been entered into whichever subform is visible prior to saving the main form (the validation fields are different on each subform).

There may a scenario when a user may select Single Payment, start completing subform1, change their mind and then select Pro Rata payment instead, which will hide subform1 and then show subform2.

Would the data in subform1 have been saved or discarded when the combo control was changed and would i need to validate for this when i save the main form?

Many thanks
0
Comment
Question by:andrewpiconnect
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 84
Comment Utility
Yes, when the user moves from the Subform back to the Parent form, the data in that subform would be saved automatically. If you don't want that data saved, you must discard it via code.

I'd check when the user selects a value in the combo, and ask them what they want to do:

Sub YourCombo_AfterUpdate()
  If YourCombo.Column(0) = "ProRata" Then
     '/ is the "non-prorata" subform dirty?
     If Me.SubformControl1.Form.Dirty Then
      If Msgbox("Delete the Single Payment data?", vbYesNo) = vbYes Then
        ' delete the data here
      Else
         ' user chose not to delete the data, so we stay with the ProRata data, and don't show the other subform
       End If
     End If
     
  Else ' single payment - do the same thing as above, but reference the ProRate subform to see if it's dirty
  End If
End sub

Open in new window

I've made assumptions regarding the values in YourCombo, so you'd have to change the sample above to insure that is looks at the correct values.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Scott,

As you stated, as soon as they move out of the subform and back to the main form, to select the other value in the combo, the data in that subform is going to be saved, so the If statement:

If Me.SubformControl1.Form.Dirty Then

in the combo afterupdate is always going to be false.

I would include something in the subforms Form_BeforeUpdate event to test to determine whether the process was complete and if not, then post a message asking the user if they want to cancel that input.
0
 

Author Comment

by:andrewpiconnect
Comment Utility
Thanks guys,

Either subform will go to a new record when loaded so could i put a check in BeforeUpdate event of the combo control on the main form like this:

Sub MyCombo_BeforeUpdate()

    If Me.subform1.Form.Visible = true Then
     
      If Nz(Me.subform1.Form!DebitID,"")="" Then
        'form is empty, allow switching of the subform to be displayed
      Else
          If Msgbox("Delete the Single Payment data?", vbYesNo) = vbYes Then
            ' delete the data here
          Else
             ' user chose not to delete the data, so we stay with the ProRata data, and don't show the other subform
          End If
      End If
   
    ElseIf Me.subform2.Form.Visible = true
      ' do the same checks to see whether the Pro Rata subform is dirty
    End If

End Sub
0
 
LVL 84
Comment Utility
Thanks for catching my logic flaw Dale :)

Dale's right, of course - once you move out of the Subform, the data is saved and therefore the Form's Dirty property will always return False.

You could include logic in each Subform's Before_Update event to determine if it's a valid record, and then have the main form check that if the user selects a new value in that combo. For example, add a hidden textbox on the mainform named "txIsValid", and then in the subform:

Sub Before_Update(Cancel As Integer)
  '/ first  determine if the data is valid. I've assumed you have a function named IsValid in the subform's code module that returns True if the data is valid.
  If IsValid Then
    Me.Parent.txIsValid = "True"
  Else
    Me.Parent.txIsValid = "False"
  End If
End Sub

Now, in the combo, check that value if the user chooses a different Payment type:

If Me.txIsValid = "True" Then
  If Msgbox("Do you want to remove the current Payment data?", vbYesNo") = vbYes Then
     '/ delete the subform record here, and take the user to the new payment subform
  Else
     '/ don't do anything, since you can't have both types
  End If
End If
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
>MS Access 2010 subforms. If not visible are they still enabled?

It is executed, but you can make no modifications in that subform.
In essence it is like enabled but locked.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
I generally don't keep two subforms open for this type of process.  Instead, when the user selects one of the options in the combo box, I use the AfterUpdate event to set the SourceObject property of the subform control to the appropriate subform.

Alternately, you could create a popup form (two actually) and instead of using the subform to enter the data, use the popup, set its window mode to dialog and provide Cancel/Save buttons on that form as the only ways to close the form.  With it in dialog mode, the users cannot move off the form back to the main form unless they cancel or save the changes on the popup.
0
 
LVL 84
Comment Utility
Did you use the popup method described by Dale? Just curious, since I initially answered your question and gave you information on workarounds.
0
 

Author Comment

by:andrewpiconnect
Comment Utility
Hi Scott,

Yep i used Dale's solution as although you gave me a workaround that was usable, it was Dale's solution that was more suitable for my requirements and easier to maintain going forward, taking into account, i run audit tables after each subform update, as well as validating different options on each subform and the main form, the vba validation started to become more and more complex.

Thanks again
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now