Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Cut Down Amount of Code!

    This one should be simple, but I am not great at VB, so here goes.

    I have a form with 35 fields on it. Each field has a button on it to unlock the field to edit the data, then a second button to relock. There is a need to lock any open fields during this edit process. So, I can write code for each button for all 35 fields to begin with such as -

    Tote1.enabled = False
    Tote2.enabled = False

    then unlock only the field that button is intended to allow to edit.

    Tote1.enabled = True

    That's a lot of code and a lot of buttons (70) I need to add this to. How can I write a module or sub routine that the button could call at the beginning to lock all fields, then only unlock the one needing to be edited? VB help doesn't get me very far....

    Hoping to only write this one time and use on multiple buttons.

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If they're numbered, you could use a For/Next loop:

    For x = 1 to 35

    and refer to the controls:

    Me("Tote" & x).Enabled = True

    You could also make it a function, pass the number of the control to be enabled, and use If/Then logic to enable that one and disable all others.
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a routine used for enabling/disabling buttons on a form. It should be easy to adapt it to what you need. For other types of controls, replace acCommandButton with the other control type (acTextBox, acComboBox, etc.)
    Code:
    Private Function SetButtonsActive(State As Boolean)
    
        Dim ctl As Control
        
        Me.Text_Dummy.SetFocus ' Cannot disable the active control
        For Each ctl In Me.Controls
            If ctl.ControlType = acCommandButton Then ctl.Enabled = State
        Next
    
    End Function
    Have a nice day!

  4. #4
    Join Date
    Dec 2005
    Posts
    121
    Paul,
    Tried your method into the code, but forgot the number is at the beginning and not the end. I set it up as below, but it doesn't like the criteria for the field name (actually 1Tote, 2Tote, etc., which I have to show in brackets)

    If Forms![Start]![PasswordLP] = InputBox("Enter LP Password to edit entry!", "Edit Previous Tote Entered") Then
    For x = 1 To 35
    Me([x & "Tote"]).Enabled = False
    Next x
    [2Tote].Enabled = True
    [2Seal1].Enabled = True
    [2Seal2].Enabled = True
    [2Tote].SetFocus
    Command143.Visible = False
    Command171.Visible = True
    Else
    MsgBox "Wrong Password!"
    End If

    Suggestions?

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    Never mind.......figured out the brackets needed to go and it worked fine! Thanks for the tip!

    Also Sinndho, thanks to you as well, I have your version in use at another point where it works best. Saved me a lot of time.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not clear on what you're saying is wrong. I just tested and this worked fine:

    Me(x & "text").Enabled = False

    Can you post the db?
    Paul

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ah! Slow fingers; glad you got it sorted out.
    Paul

  8. #8
    Join Date
    Dec 2005
    Posts
    121
    Paul,
    In trying to go the reverse with this process, I need to use If/Then to look thru the form to find the first seal field with null value to identify as the next set of fields to enable to add data. I was using ElseIf, but it doesn't like the Next statement, should this be different?

    For x = 1 To 35
    If IsNull(x & "Seal2") = True Then
    Me(x & "Tote").Enabled = True
    Me(x & "Seal1").Enabled = True
    Me(x & "Seal2").Enabled = True
    Me(x & "Tote").SetFocus
    elseif Next x [2Tote].Enabled = False
    [2Seal1].Enabled = False
    [2Seal2].Enabled = False
    Command143.Visible = True
    Command143.SetFocus
    Command171.Visible = False

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You certainly don't want them like that. I think you want:

    For...
    If...
    'do what you want if that's true
    Else
    'do what you want if it isn't
    End If
    Next
    Paul

  10. #10
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    (As an aside)
    I personally don't think I'd run with 70 command buttons that essentially have opposing functions, I think I would look at using 35 toggle buttons (on/off....lock/unlock) to save on form space, coding, and brain drain.

    Sam, just a random thought
    Good, fast, cheap...Pick 2.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to me it has the whiff of suspect physical design of the db/tables
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm a fan of using the .Tag property for arbitrary grouping of controls. Come up with your own spiffy categorization system and stuff it in the tag property, then rifle through the controls and check the tag to see if it's what you want.

    That said, the names of the controls you've disclosed leads me to agree with healdem.


    Do you have a 30 column wide table with yes/no fields? There's a better way to do that.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Dec 2005
    Posts
    121
    We did learn our lesson from that when the tables were initially set up (not putting the number before the name of the field, but should have been after). That was a learning curve, but now too deep in existing forms and reports to change the structure of the tables.

    We will know better next time, that is for sure! Thanks for all the comments, I do have it working now.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There shouldn't have been any numbers in those fields at all. In fact, those fields shouldn't have existed in the first place. But I digress as you have it working now...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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