Solved

Why is the code not working to enable and disable command buttons?

Posted on 2014-12-17
15
222 Views
Last Modified: 2015-01-01
I have 30 command buttons on a form and I need to activate or deactivae the each command button depending on the check box selection. I am not a coder but I found some code online and it worked fine for one command button but not for more than one. The code that does not work is:
Private Sub Form_Load()
   If Ctl2 - HumanEngineering = True Then
        Me.Command104.Enabled = True
    ElseIf 2 - HumanEngineering = False Then
         Me.Command104.Enabled = False
    End If



   If Ctl2 - Procedures = True Then
        Me.Command105.Enabled = True
    ElseIf Ctl2 - Procedures = False Then
         Me.Command105.Enabled = False
    End If



   If Ctl2_Work_Direction = True Then
        Me.Command106.Enabled = True
    ElseIf Ctl2_Work_Direction = False Then
         Me.Command106.Enabled = False
    End If
End Sub
0
Comment
Question by:cssc1
  • 3
  • 3
  • 3
  • +4
15 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 84 total points
ID: 40505398
enclose your control names in bracket [] and placed "me. "  in front

if me.[Ctl2 - HumanEngineering] = True Then
        Me.Command104.Enabled = True
    ElseIf me.[2 - HumanEngineering] = False Then
         Me.Command104.Enabled = False
    End If


do that for all your controls
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 84 total points
ID: 40505406
Are Ctl2 - Procedures and Ctl2 - HumanEngineering the names of controls on your form?

If so, you need to use square brackets:  [Ctl2 - HumanEngineering] or better yet, rename them avoiding spaces and special characters.

For example:

chkHumanEngineering (assuming  a checkbox)
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 84 total points
ID: 40505417
Also, check the actual names of the controls.

Should Ctl2 - HumanEngineering  actually be Ctl2_HumanEngineering  (with an underscore, similar to your last control, Ctl2_Work_Direction)  ?

Edit:

You should also declare Option Explicit at the top of your code, under Option Compare Database, to avoid problems with typos, unrecognized control names, etc.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 40505458
Also, the code is redundant.  It tests the same condition twice.  Trim it down to:
 If [Ctl2 - HumanEngineering] = True Then
         Me.Command104.Enabled = True
     Else
          Me.Command104.Enabled = False
     End If

Open in new window

And I agree fully with the others who suggested renaming the controls.  Do it NOW before you put any code behind them.  There is nothing worse for someone looking at your code to see controls/fields with meaningless names.  Perhaps today you remember what command104 is but in a month, you won't.  Stop the problem immediately by renaming the control.  Once you put code behind the controls, you can still change the names but it is a two step process.  Change the control name, then search for the old control name in code and replace all instances.  Access does not help you by propagating name changes into code.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 40505471
I have 30 command buttons on a form and I need to activate or deactivate the each command button depending on the check box selection.
Yikes, but OK, I'll let it rest.
I'd probably go with a lot fewer command buttons and change what they do with the checkboxes...but you said
I am not a coder
Checkboxes can exist in three ways.  Checked, unchecked and no value
So, the code can be much simpler, just 30 lines
Me.SomeCommandButton.Enabled = Nz(Me.SomeCheckBoxControl.value , False)
...
one line for each checkbox-commandbutton pair
Sub out the bold text for the actual names of the controls.
Likely, you'll want similar code in each checkbox's AfterUpdate event, to toggle the buttons.
One line in each AfterUpdate event
0
 

Author Comment

by:cssc1
ID: 40505525
Please see attached image.

What did I do wrong?
CodeError.jpg
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 84 total points
ID: 40505532
are you reading the error message?

it is  very clear
you need END IF for the first two  IF statement
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 40505555
You are missing end if's as the message states.

Clean up the code to remove the unnecessary elsif's to make it easier to read.  You will end up with thirty statements in the form of:

If Me.something = True Then
    Me.cmd.Enabled = True
Else
    Me.cmd.Enabled = False
End If

OR - you could be slick and confuse people with 30 of these -- no If's at all:

Me.cmd1.Enabled = Me.Chk1
Me.cmd2.Enabled = Me.chk2
....
BUT - this will probably fail if Chk is null.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 40505564
What did I do wrong?
You decided to stick with a complex and unnecessary bunch of If ElseIf End blocks and got the syntax wrong

Me.Command104.Enabled = Nz(Me.[Ctl2 - HumanEngineering] , false)
Me.Command105.Enabled = Nz(Me.[Ctl2 - 2-Work Direction] , false)
Me.Command106.Enabled = Nz(Me.[Ctl2 - 2-Procedures] , false)

And your controls REALLY need a better naming scheme or you have very odd logic occurring:
   If Me.[Ctl2 - HumanEngineering ]= True Then
         Me.Command104.Enabled = True
     ElseIf me.[2 - HumanEngineering] = False Then
          Me.Command104.Enabled = False
     End If

The bolded text is NOT the same.
Are you really checking two controls.?
if Me.[Ctl2 - HumanEngineering] = true enable the command104 and we don't give a damn about [2 - HumanEngineering]

And if Me.[Ctl2 - HumanEngineering] = False, then if [2 - HumanEngineering] = False we'll disable command104
On the other hand Me.[Ctl2 - HumanEngineering] = False, then if [2 - HumanEngineering] = true, we won't do anything at all because we haven't coded for that occurrence.

That will likely not work out well!
0
 

Author Comment

by:cssc1
ID: 40505658
Please see error.
I think I did things correct????
Error2.jpg
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 40505676
This code doesn't belong in the Load event.  Move it to the Current event so it will run for each record.  The Load event only runs once when the form is loaded.  Code like this needs to run every time the form moves to a new record.  

I don't see anything wrong with the expression itself so try moving it to a more appropriate event.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 40505678
The code is not finding a control named Ctl2 - HumanEngineering and therefore throws an error.

I do not mean to offend, but looking at your images, it really does appear that you are creating an unmaintainable horror.  There are two critical things to creating MS Access applications that won't cause your successor to curse your name.  One is understanding and correctly modeling your data and creating proper tables and relationships
http://phlonx.com/resources/nf3/
The other is adopting a naming convention and rigorously following your interpretation of it.
http://en.wikipedia.org/wiki/Leszynski_naming_convention

There are things we adopt in naming conventions that make our lives easier -- no spaces in names, and no use of Reserved Words, no underscores because those require a SHIFT to type, and CapitalingEachWordInADescription so that things remain legible.  We also NEVER EVER leave the default 'ControlTypeXXX' names like Combo66 or Command105 survive into production objects or code.

I can see that retrofitting your app will be an immense amount of work, and I can guess the man-hours involved.

I am still begging you to do it.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 41 total points
ID: 40506665
Anytime I see an error message refer to an object as "|1", I think of corruption, or perhaps issues with the installation of Access. Those are "tokens" or "placeholders" in the error message, I believe, and for some reason Access cannot determine the name of the control/object which threw that error.

Follow the advice of the other Experts, as they have you on the right track to fix your CODE issue, but you might also be concerned with that little anomaly, and make sure your Windows and Office installations are fully up to date.

You might also create a new, blank database and import everything to that new database.

And please do consider adopting a naming convention, as Nick has suggested. I personally have no issue with underscores, but I do a lot of development with SQL Server-based systems, and everyone seems to use the underscore for column names, so it's no big deal to me. The main focus with a naming convention is to adopt one that makes sense to you, and to use it regularly. Whether you use conventions like an underscore is purely a matter of personal choice.
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 41 total points
ID: 40510780
Hi,

depending on the way you created the checkboxes you don't need Nz. A checkbox can have three states, 0, -1 and NULL. In opposite a Yes/No field in the table (if it is an Access table) can never be NULL and so all checkboxes are created with the property "Triple State" (see the "Data" tab) set to false by default. If you do not explicitly set that to True NULL can never be a value here. If you bound the checkbox to a Yes/No field the TripleState property is ignored as this can never be NULL.

Additionally, beneath the things the others already said about naming conventions and so on: Whenever you need to write a code more than once try to find an easier version, either by using a sub/function or by using loops to do all the things at once.

When you have 30 command buttons and 30 checkboxes this is clearly a job for a loop so to make that possible you simply need to rename your controls. Moreover, if you test a boolean value you can leave out the "=True", instead you can write:

If MyBooleanValue Then

Open in new window

Or if you want to test False:
If Not MyBooleanValue Then

Open in new window


The "Enabled" property is already a boolean value so you can assign the test value to this property without any test, using either "Not" or direct assign. As they are equal here you can directly assign the test value to the property:

Me.Command104.Enabled = me.[Ctl2 - HumanEngineering]

Open in new window


But nevertheless you would need one line for each control pair so it would end in 30 lines of code here - that is hard to maintain. So as I said above, rename the controls in a readable and "loopable" way:

Dim i As Long
Dim strNumber As String
For i = 1 to 30
    strNumber = Right("00" & i,2)   ' creates "01","02","03"....
    Me.Controls("Command" & strNumber).Enabled = Me.Controls("Checkbox" & strNumber)
Next

Open in new window


That's all you need to set all 30 buttons depending on all 30 checkboxes, you only need to rename all pairs of controls to "Command01"/"Checkbox01", "Command02"/"Checkbox02" and so on. In this special case it is really OK to have a generic name and not a specific one which describes the function of each control because the meaning of each CommandButton and Checkbox can be seen in the assigned field and the caption of the button etc.

But if you really want to have a specific name for each one you should change the name of each control in a way that could be used in a loop. So let's take your field names "HumanEngineering" and a field name "Procedures" and "Work Direction" in the underlying query of the form. First of all: Avoid any spaces and special characters in any object name as these should never be seen by the user so there is never a need to name that like a caption. Any control has a way to use a label or caption which can be anything you want - but not the object name. So "WorkDirection" is OK, "Work Direction" or "X/L" etc. should never be an object name. Let's assume you renamed your wrong object names using only simple charactes and letters without spaces and your field is now renamed to "WorkDirection". Then you would need to rename the controls, which should normally never be the same name as the underlying field names. For example, use a prefix "cmd" for the button control names and a prefix "chk" for the checkboxes. Now you would rename the control pairs like this:

cmdHumanEngineering / chkHumanEngineering
cmdProcedures / chkProcedures
cmdWorkDirection / chkWorkDirection
...
and so on.

Now you can use that again in a loop if you used "HumanEngineering", "Procedures" and "WorkDirection" as the underlying field names:

Dim ctl As Access.Control
Dim strNumber As String
For Each ctl In Me
    If TypeOfControl Is Access.CheckBox Then
        If ctl.ControlSource <> "" And Left(ctl.Name,3) = "chk" Then
            Me.Controls("cmd" & ctl.ControlSource).Enabled = ctl.Value
        End If
    End If
Next

Open in new window


That would loop through all controls of the form, checks if the found control is a checkbox and if yes, check if the checkbox is bound to a field and also it's name starts with "chk" and if that's also the case, assign it's value to the Enabled property of the command button which have the same name as the field name of the checkbox + a leading "cmd". If you have more checkboxes on your form, simply add a unique prefix for the desired checkboxes, like using "chk" to the 30 checkboxes and "check" (or anything else you want) as prefix for all other checkboxes which should not be used in this test.

You see, both is possible: Using a numeric and generic style of naming or an individual naming (which is a little bit more work in renaming the controls, but easier to maintain later if you ever need to change the form). You only need to have some clever names instead of just names which cannot be handled in a loop like yours.

Cheers,

Christian
0
 

Author Closing Comment

by:cssc1
ID: 40527107
Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now