CODEWITHSUNDEEP
vbams-accessms-access-2013
Travis Patterson
Travis Patterson

Reputation: 93

Enable/Disable Fields with certain criteria MS Access

I have a form in MS Access that I'm trying to create for insurance claims. I have all the fields that I need to be filled in but what I'd like to be able to do is enable or disable those fields depending on certain actions of the users. So the flow of the form is like this: I have a Frame at the top with two radio buttons, one for a single-claim incident and one for a multi-claim incident. If the user clicks the single-claim button everything continues with no problem. If the user clicks the multi-claim incident button, a combo box appears to the side with a dropdown list of MultiClaim_Incident_ID numbers that they need to select from. What I'm trying to do is if the user selects the Multi-Claim Incident button AND does not select an Incident ID number from the dropdown down list (i.e. leaves it at default value) then the rest of the form is disabled until corrected as well as clear all the fields...

It seems like it should be pretty straightforward but I can't seem to get it to work, I'm not sure if my logic is flawed or what. Here's an abridged version of my VBA code:

Private Sub Form_Load()
    Me.SM_Frame.Value = 1
    Me.MultiClaim_Drpdwn.Value = Null
End Sub

Private Sub SM_Frame_AfterUpdate()
    If SM_Frame.Value = 1 Then
        Me.MultiClaim_Incident_ID_Label.Visible = False
        Me.MultiClaim_Drpdwn.Visible = False
    ElseIf SM_Frame.Value = 2 Then
        Me.MultiClaim_Incident_ID_Label.Visible = True
        Me.MultiClaim_Drpdwn.Visible = True
    ElseIf SM_Frame.Value = 2 & MultiClaim_Drpdwn.Value = Null Then
        Me.Incident_Date = Null
        Me.Incident_Date.Enabled = False
        Me.Claimant_Name.Value = ""
        Me.Claimant_Name.Enabled = False
    //PATTERN CONTINUES FOR REST OF FIELDS// 
        MsgBox ("CLEAR EVERYTHING!!")
    ElseIf SM_Frame.Value = 1 Then
        Me.Incident_Date.Value = ""
        Me.Incident_Date.Enabled = True
        Me.Claimant_Name.Value = ""
        Me.Claimant_Name.Enabled = True
    //PATTERN CONTINUES FOR REST OF FIELDS//
        MsgBox ("Everything can continue as is")
    End If
End Sub

Upvotes: 2

Views: 3313

Answers (2)

Oliver
Oliver

Reputation: 3255

Let me just say that getting sequences like these right is NOT straightforward AT ALL! So don't feel bad about not getting it right on the first try. I have to create things like that about once a month and still need a lot of tries until it works in all situations.

Try to seperate to concerns:

You already have SM_Frame_AfterUpdate(). Do in it what you must to handle changing from Value 1 to 2, namely making the Combobox visible, but STOP there. You dont know what will happen to the fields with information from SM_Frame alone, you need to wait for MultiClaim_Drpdwn. Also, do what is needed to go from 2 to 1, namely hide the Combobox.

Next, create an AfterUpdate-handler MultiClaim_Drpdwn_AfterUpdate(). Use THAT to deal with the fields (enable/disable, set empty) according to its value.

Once you have that in place, you only have some edge cases remaining. For example, you want the fields to behave like MultiClaim_Drpdwn_AfterUpdate() states right after you change the SM_Frame. Thats easy once you understand that you can just happily CALL MultiClaim_Drpdwn_AfterUpdate() from within SM_Frame_AfterUpdate(), best done at the very end. These eventhandlers are still just normal functions, already public and available for anyone. This will make things chain nicely when you come from the radiobutton or not when you come from the Combobox.

Upvotes: 1

ypicot
ypicot

Reputation: 124

In an "elseif" series, once a condition is true, the rest is ignored.

So, your

ElseIf SM_Frame.Value = 2 & MultiClaim_Drpdwn.Value = Null Then

is never reached, because you've got

ElseIf SM_Frame.Value = 2 Then

before.

In the same idea, the "ElseIf SM_Frame.Value = 1 Then" after the MsgBox is totally useless, because it's hidden by "If SM_Frame.Value = 1 Then"

Try to use the step-by-step debug mode to see that.

Upvotes: 1

Related Questions