Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-15
8
Medium Priority
?
678 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 85
ID: 39781954
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 49

Expert Comment

by:Dale Fye
ID: 39781974
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
ID: 39782053
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85
ID: 39782271
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
 
LVL 31

Expert Comment

by:hnasr
ID: 39782727
>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 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39782832
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 85
ID: 39785051
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
ID: 39785144
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

886 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