none
Help with checkbox? RRS feed

  • Question

  • Hello – I am having a big brain fart right now and am in need of some assistance. I think this may be somewhat simple…I have tow fields (AutoTime and ManualTime) that are visible to users when a user checks a checkbox (TimeOverride). If check (true) then the ManuallTime fields is visible; unchecked (false) the AutoTime field is visible.

     

    The below code works perfectly. Lol…can probably be streamlined, but works great. The problem I am having is the checkbox is checked, it is applying to all records, rather than the one record it should be applying to.

     

    Can anyone possibly get me pointed in the right direction so that the code only applies to one record at a time? It has been a while since I have played with Access (2016) 😉

     

     

    Items in play:

    Checkbox:       TimeOverride

    Field1:               AutoTime

    Field2:               ManualTime

     

    Existing Code:

     

    Private Sub TimeOverride_Click()

       

       If Me!TimeOverride = True Then

        'Show ManualTime field; hide AutoTime field

            Me!ManualTime.Visible = True

            Me!AutoTime.Visible = False

        'Clear and Lock Start/End Fields

            Me!Start.Value = ""

            Me!EndTime.Value = ""

            Me!Start.Locked = True

            Me!Start.Enabled = False

            Me!EndTime.Locked = True

            Me!EndTime.Enabled = False

        Else

        'Show AutoTime field; hide ManualTime field

            Me!Start.Enabled = True

            Me!Start.Locked = False

            Me!EndTime.Enabled = True

            Me!EndTime.Locked = False

        'Show AutoTime field; Hide ManualTime field

            Me!ManualTime.Visible = False

            Me!AutoTime.Visible = True

        End If

     

    End Sub

    Monday, January 28, 2019 11:28 PM

Answers

  • I got it! Finally figured out how to do what I was attempting. My final code/solution is below. Thank for all those who provided assistance. Your time and suggestions are much appreciated.

    Placed the below in Forms Current Event:

    Private Sub Form_Current()

        If Me!SvcTimeOverride.Value = False Then
            Me!Manual_Time.Enabled = False
            Me!SvcTimeFrom.Enabled = True
            Me!SvcTimeTo.Enabled = True
            Me!SvcDate.SetFocus
            Exit Sub
            End If
        If Me!SvcTimeOverride.Value = True Then
            Me!SvcTimeFrom.Enabled = False
            Me!SvcTimeTo.Enabled = False
            Me!Manual_Time.Enabled = True
            Me!Manual_Time.Locked = False
            Me!SvcDate.SetFocus
            Exit Sub
            End If

    End Sub

    Placed the formula in the Total Time fields to auto calculate based on the checkbox selection:

         =IIf([SvcTimeOverride]=True,[Manual_Time],DateDiff("h",[SvcTimeFrom],[SvcTimeTo]))

    All is well. Everything works beautifully. Thanks again to all who assisted.

    Best Regards

    • Marked as answer by rstreets2 Tuesday, March 26, 2019 8:02 PM
    Tuesday, March 26, 2019 8:02 PM

All replies

  • Hi,

    Do you use a UserForm where CheckBox and two fields are?

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, January 29, 2019 6:58 AM
  • Hi,

    Sounds like you're using an unbound checkbox control on a continuous view form. If so, what you are seeing is normal behavior. To apply each checkbox to a specific record, you'll have to use a bound control. You likely don't have a checkbox field in the underlying table, but in this case, you may have to add it.

    Tuesday, January 29, 2019 3:51 PM
  • As theDBguy says, you would need to use a bound checkbox, but even then it would only work in single form view.  In continuous forms view the visibility of the controls would apply to all rows.  You'd also need to repeat the code in the form's Current event procedure.

    In continuous forms view a possible option would be to use conditional formatting to disable the relevant control on the basis of an expression.  Disabling a control without also setting its Locked property to True will grey it out, so while not hidden there would be a visual indication that the control's value is not relevant to the current row.


    Ken Sheridan, Stafford, England

    Tuesday, January 29, 2019 5:35 PM
  • HI theDBguy - Thank you for your assistance. Actually the checkbox is a bound control and is included in the table; as well as are the other fields in question?
    Tuesday, January 29, 2019 6:30 PM
  • Hi Ashidacchi - Thank you for your response. Yes, these fields are contained withing a form.
    Tuesday, January 29, 2019 6:31 PM
  • HI theDBguy - Thank you for your assistance. Actually the checkbox is a bound control and is included in the table; as well as are the other fields in question?
    In that case, if you're using a continuous view form, then perhaps, as Ken was saying, the best approach might be is to try using Conditional Formatting instead.
    Tuesday, January 29, 2019 6:46 PM
  • Hi theDBguy - Thanks again for your response. My apologies for the delay in my response.  I had been pulled off onto another "more important" project...anyhow, I like the conditional formatting suggestion. My only problem is I have not done a lot of Conditional Formatting in Access, mostly Excel.

    I have been trying to play around with this and can't come up with the correct expression. I have actually done this in Excel before, but seems there may be some slight differences between Excel Conditional Formatting and Access Conditional Formatting.

    Might you be able to provide me with a basic formula/expression to show or hide a field using conditional formatting? A basic example should get me on the right track. I should be able to figure out from there.

    I thank you again for your assistance. Most appreciated.

    Saturday, February 16, 2019 12:55 AM
  • I got it! Finally figured out how to do what I was attempting. My final code/solution is below. Thank for all those who provided assistance. Your time and suggestions are much appreciated.

    Placed the below in Forms Current Event:

    Private Sub Form_Current()

        If Me!SvcTimeOverride.Value = False Then
            Me!Manual_Time.Enabled = False
            Me!SvcTimeFrom.Enabled = True
            Me!SvcTimeTo.Enabled = True
            Me!SvcDate.SetFocus
            Exit Sub
            End If
        If Me!SvcTimeOverride.Value = True Then
            Me!SvcTimeFrom.Enabled = False
            Me!SvcTimeTo.Enabled = False
            Me!Manual_Time.Enabled = True
            Me!Manual_Time.Locked = False
            Me!SvcDate.SetFocus
            Exit Sub
            End If

    End Sub

    Placed the formula in the Total Time fields to auto calculate based on the checkbox selection:

         =IIf([SvcTimeOverride]=True,[Manual_Time],DateDiff("h",[SvcTimeFrom],[SvcTimeTo]))

    All is well. Everything works beautifully. Thanks again to all who assisted.

    Best Regards

    • Marked as answer by rstreets2 Tuesday, March 26, 2019 8:02 PM
    Tuesday, March 26, 2019 8:02 PM
  • Hi. Congratulations! Glad to hear you got is sorted out. Good luck with your project.
    Tuesday, March 26, 2019 8:14 PM