Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Efficient events on forms (2000/XP)

    (Same database as my earlier posting by the way)

    I've inherited a database that needs the data entry form tidying up.

    The values chosen in a number of combo boxes have a knock on effect on others, for example:

    Combo box with choice or 8 values (including "none") - When the forms opens for a new record, "none" is the default value, six other combos are disabled with their values set to "N/A". When the combo is updated to a value other than "none" the dependent combos should be enabled (their values left at "N/A" until changed by the user)
    If the combo is changed back to "none" any dependent combos should be disbaled again and any chosen values reset to "N/A".

    When the form is re-opened the dependent combos should be in the appropriate state (according to whether the parent says "none" or something else)

    As you can see there will be a lot of repetitive code just for this set of controls. There are a large number of similarly dependent control groups like this so I'm wondering what is the most efficient way to do it:

    case statements?
    if statements?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient events on forms (2000/XP)

    You can often create a generalized routine to handle various cases, perhaps passing a control or control name, and/or a flag as arguments. See for example <post#=360863>post 360863</post#> and replies, and also your other recent thread. Depending on the exact situation, Select Case or If Then Else statements might be useful, but it's difficult to give specific advice.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    Its the passing of the control I'm not grasping.

    Private Sub ChangeDependentControls(ctl As Control)

    With ctl
    If .Enabled = True Then
    .Enabled = False
    .Value = "N/A"
    Else
    .Enabled = True
    End If
    End With

    End Sub

    I'm trying ChangeDependentControls(cboSet2) in the calling procedure but this tries to pass the value of the control (which makes sense) but my mind has gone blank and I can't think how to pass the control name

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient events on forms (2000/XP)

    When you pass a control, you pass everything about it, including its value, its parent form or report, and all its properties and methods, including Name. However, when you pass a control, you don't have to worry about the name unless you want to because your routine already knows that it's *this* particular control that you're currently dealing with, regardless of its name.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    So should the above code have worked?

    I'm getting a run time error (424 - Object required) when I use

    Private Sub cboType_AfterUpdate()
    ChangeDependentControls(cboSet2)
    End sub

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient events on forms (2000/XP)

    Is cboSet2 the name of a control on that particular form or subform? If so it should have worked, but you can reduce Access confusion by referencing the control as Me!cboSet2. That makes it perfectly clear that you are passing a control object. If the control isn't on the current form or subform, you have to fully reference the control as Forms!FormName!cboSet2.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Efficient events on forms (2000/XP)

    It seems to be you are making this much harder than it is. All you need is a procedure that changes all the other combo boxes based on whether or not that main combo box has a non-"none" value. You sub would be something like this (I'm guessing at your combo box names):

    Private Sub SetComboEnabled()
    cbo1.Enabled = (cboMain <> "none")
    cbo2.Enabled = (cboMain <> "none")
    ' etc.
    End Sub

    In your form's current event, you call this sub. You also call it in the AfterUpdate event of cboMain. The default value for cboMain is "none", the default value for the others is "N/A".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    It was actually cboSet2Dip (but my typo was in the posting, not my code)

    This is copied directly from vba but I still get the same error

    Private Sub cboSet2Type_AfterUpdate()
    ChangeDependentControls (Me!cboSet2Dip)
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    Thanks Mark, I'll try that but I'd like to know why the previous code is throwing an error.

    The situation isn't quite a straightforward as I may have suggested, and I may need to handle things differently if the dependent combos have none "N/A" values, perhaps warning the the user they are about to effectively wipe their data (not sure about this yet)

    Also "N/A" is not actually on the item list and is only a valid value if the main combo if set to "None".
    This is something I am unable to change and it may have impllications in someone else's code.

    So I was thinking this

    Private Sub SetComboEnabled()
    cbo1.Enabled = (cboMain <> "none")
    cbo2.Enabled = (cboMain <> "none")
    ' etc.
    End Sub

    Is likely to have to end up more like this

    Private Sub SetComboEnabled()
    If Me!cboMain = "None" Then
    cbo1.Enabled = False
    cbo1.Value = "N/A"
    cbo2.enabled = false
    cbo2.value = "N/A"
    etc
    Else
    cbo1.Enabled = True
    cbo2.enabled = True
    etc
    End if

    which isn't which as neat

    I don't really want to keep setting enabled to true if they are already true but haven't figured th ebest way to deal with that either. But I think I need to get the previous value.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient events on forms (2000/XP)

    The problem with

    ChangeDependentControls (Me!cboSet2Dip)

    is in the brackets ( ). You are calling ChangeDependentControls as a procedure, not as a function. The brackets tell VBA to evaluate the value of Me!cboSet2Dip, instead of passing it as a control object. It should work OK if you use

    ChangeDependentControls Me!cboSet2Dip

    or, if you prefer, call it like this:

    Call ChangeDependentControls(Me!cboSet2Dip)

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    Hans, you beat me to it!

    I just realised that and have banged my head against the wall a few times for good measure <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient events on forms (2000/XP)

    You could try something like this:

    Private Sub SetComboEnabled()
    Dim f As Boolean
    Dim i As Long
    f = (Me!cboMain <> "None")
    For i = 1 To 8
    Me.Controls("cbo" & i).Enabled = f
    If f = False Then
    Me.Controls("cbo" & i).Value = "N/A"
    End If
    Next i
    End Sub

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient events on forms (2000/XP)

    I'm going to try to add the code I want to the main combo before update event so the user has the chance to cancel. Looking at values for combo controls I can see .value and .old value. I've looked at how these change and it seems that 'old value' doesn't change until you save the record - is this correct?

    How can I check what the previous choice was in the combo (say if the user if particulary indecisive)?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient events on forms (2000/XP)

    You'd have to store the selected value in a variable, for example:

    Private strPreviousItem As String

    Private Sub cboMain_AfterUpdate()
    strPreviousItem = cboMain
    End Sub

    Private Sub cboMain_Enter()
    strPreviousItem = cboMain
    End Sub

    Private Sub cboMain_BeforeUpdate(Cancel As Boolean)
    If MsgBox("Do you want to change from " & strPreviousItem & " to " & cboMain & "?", _
    vbQuestion + vbYesNo, "Annoying question") = vbNo Then
    Cancel = True
    cboMain.Undo
    End If
    End Sub

    I would hate to be asked this every time!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •