Coding combo boxes to be enabled/disabled

[Greg553]

I'm stuck on a simple problem i would bet. I have a option group containing 3 choices, beginner, Intermidiate,adavanced.
I have 10 combo boxes below. what i want is to enable so many boxes based on choice in option group.

so if beginner is choosen 5 combos are enabled, and so on.
I have done it using visible.

EX case 1
me!combo1.visible = true
me!combo2.visible = false
and so on for each case
But if i use enable/disable insted of visible it does not work. any help is all appreciated
Greg

 

[dhookom]

Should be Enabled and Disabled.
I would probably put something in the Tag property of each combo box that would identify if it is enabled based on the value of the option group. Then, some simple code would loop through the combo boxes and set their property appropriately.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Greg553 . . .

Using [blue]dhookom's[/blue] suggestion consider using the following letters in [purple]purple[/purple] ([purple]B[/purple]eginner, [purple]I[/purple]ntermediate, [purple]A[/purple]dvanced) for setting-up the Tag property. As an example .... if you wanted a certain combobox to be visible when intermediate or advanced was selected, you would enter [blue]IA[/blue] in the tag property. When you enter the values [purple]do not enter any quotation marks![/purple]. Next copy/paste the following code to [blue]AfterUpdate[/blue] event of the option group and give it a whirl (you substitute proper names in [purple]purple[/purple]):

[blue]   Dim ltr As String, ctl As Control
   
   Set OG = Me.OptGrp1
   ltr = Choose(Me.[purple][b]YourOptionGroupNameHere[/b][/purple], "B", "I", "A")
   
   For Each ctl In Me.Controls
      If ctl.Tag <> "" Then
         If InStr(ctl.Tag, ltr) > 0 Then
            ctl.Visible = True
         Else
            ctl.Visible = False
         End If
      End If
   Next[/blue]

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

woops! ...

delete the following line:

[blue]Set OG = Me.OptGrp1[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[aaabuhalime]

Hi again,
This is my last question here, I have created a form that holds three fields ID, Name1,address1, two combo boxes(28,30), the field Name1 is a combobox, that holds different names, what I want to do is if i select the name I want one of the two boxes to disappear, or to hide, for example if I select, the name Ed, I want combobox 28 to disappear, This is what I am using, I tried invisible, I got an error, data Not Found. Any help will be appreciated.

Thanks


Private Sub Combo28_AfterUpdate()
Me.Combo28.Visible = (Me.Name1 = "Ahmed")
End Sub

Private Sub Form_Current()
Me.Combo28.Visible = (Me.Name1 = "Ahmed")
End Sub

 

[Katerine]

Greg553:
In addition to what dhookom and TheAceMan1 said, to disable or enable instead of setting visible, just change your code example to:

combo1.Enabled = true
combo2.Enabled = false

(Note: the Me! is unnecessary. And... I'm not even sure because I rarely have reason to use Me, but the few times I have, I've always used "Me." instead of "Me!" in code.)

...However, if you're going to do this, you need to make sure that none of these combo boxes have the focus, or you will get an error. I recommend something like the following code:

txtName.SetFocus 'set focus on something that is NOT any of the combo boxes
combo1.Enabled = (YourOptionGroupNameHere.Value = 1)
combo1.Enabled = (YourOptionGroupNameHere.Value = 2)
combo1.Enabled = (YourOptionGroupNameHere.Value = 3)

"(YourOptionGroupNameHere.Value = 1)" is a quick way to return True or False, depending on whether the condition inside the parentheses is true or false.

HTH!

Katie

 

[dhookom]

Katerine,
I always use Me. when I am referencing a control in VBA. It helps differentiate between memory variables and control names. Being explicit is just good practice.

aaabuhalime should learn how to set breakpoints in code to test/troubleshoot. There is a description here faq705-7148. Please let us know if you have any questions.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

[blue](Note: the [purple]Me[/purple]! is unnecessary. And... )[/blue]

Without the form qualifier [blue]Me[/blue] access doesn't know if your refering to a control, variable or what ... and has to go on the hunt to find out! History has proven its better to use [blue]Me[/blue] where a form control is concerned, than not. In parallel with [blue]dhookom[/blue] ... being explicit is just good practice.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Katerine]

TheAceMan1: That's very interesting - I didn't know that Access needs to go through everything like that. Or, come to think of it, I did know that long ago, but I'd forgotten, so thanks for the reminder.

On a related note: My original point when I said that "Me!" was unnecessary... I didn't say that because I thought it was a bad idea or anything, but rather because Greg553 had written it as "Me!" (with an exclamation mark) whereas, on the occasions when I've explicitly used the "Me" object, I have always used "Me." (with a period). I actually wasn't sure whether "Me!" would even work, or which was better, and didn't really feel like looking into it on my day off, and it definitely wasn't the focus of the post, which was to answer Greg553's question about how to get the code to work with Enabled instead of Visible. So I just wrote the code without it, since I knew that worked, and wrote a little footnote, nothing more.

Anyway, my question is: which is better in VBA - "Me!" or "Me." ?

Katie

 

[TheAceMan1]

Katerine . . .

There's enough written about the use of [blue]Bang[/blue] & [blue]Dot[/blue] operators. In reference to these writings I go along with the following:

[blue]You use the ! and . (dot) operators in an identifier to indicate the type of item that immediately follows.

The ! operator indicates that what follows is a user-defined item (an element of a collection). For example, use the ! operator to refer to an open form, an open report, or a control on an open form or report.

Identifier Refers to
********** **********
Forms![Orders] The open Orders form
Reports![Invoice] The open Invoice report
Forms![Orders]![OrderID] The OrderID control on the open Orders form

The . (dot) operator usually indicates that what follows is an item defined by Microsoft Access. For example, use the . (dot) operator to refer to a property of a form, report, or control.

Note You can also use the . (dot) operator to refer to a field value in an SQL statement, a Visual Basic for Applications method, or a collection. For example, the identifier Forms![Orders].Controls refers to the Controls collection of the Orders form. However, because the Controls collection is the default collection for forms and reports, it's usually not necessary to refer to it explicitly.[/blue]

In the end its simply a preference ... however the importance of me remains.

Bang Vs. Dot in Forms

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Katerine]

TheAceMan1,
Thanks for the information, and for taking the time to answer my question.

I also wanted to quickly apologize, since I neglected to do so yesterday, for implying something in my footnote that, while technically correct, was not good advice. I'm sorry I didn't properly look into the Me keyword before posting.

Katie

 

[TheAceMan1]

Katerine . . .

No one is perfect, remembers every detail and knows it all at all times. Do not feather yourself ... everything is just fine!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Greg553]

Hey everyone, Thanks for all the replies...Great info..And also thanks for all the extra questions and answers...All ways learning..
Greg

 

[MajP]

I always use Me. when I am referencing a control in VBA. It helps differentiate between memory variables and control names.

Without the form qualifier Me access doesn't know if your refering to a control, variable or what ... and has to go on the hunt to find out!

The above is misleading. The me keyword does not differentiate between controls and memory variables. It can be used to differentiate a local variable and a property or method of the form class. A control is simply one of many properties. Also it cannot be used to differentiate module scope variables and controls. The Me keyword does not tell you it is a control, it tells you it is a property/method which very likely could be a variable.
You should use me (and I would strongly suggest dot) so that you get intellisense and avoid lots of errors. This is reason enough to use it. You should use it for readability to make it clear it is a property or method. Once the code compiles there is no issue with having to "hunt" to find it out. If you are using Me to differentiate between properties and local variables then you have bigger problems, and that is naming convention. If you are using some type of proper naming convention then you should not have controls with the same names as local variables.


Here are some examples. On the form there are controls "txtBxOne" and "txtBxTwo". In a standard module there is a public variable "txtBxTwo"

Public someVariable As Integer

Private Sub Command8_Click()
  Dim someVariable As String
  Dim txtBxOne As String
  Dim someMethod As String
  
  'class variable
  Me.someVariable = 10
  'local variable
  someVariable = "ABC"
  MsgBox Me.someVariable & " " & someVariable

  'local variable
  txtBxOne = "ABC"
  'class property
  Me.txtBxOne = "123"
  MsgBox txtBxOne & " " & Me.txtBxOne

  'local variable
   someMethod = "DEF"
  'me is required in this case to call the method
   Me.someMethod
   
   'Set the public module scope variable have to use the module identifier
   Module1.txtBxTwo = "zzz'"
   'Impossible to differentiate the two with the me keyword
   MsgBox Me.txtBxTwo & " " & txtBxTwo
   'Only can differentiate with a full module identifier
   MsgBox txtBxTwo & " " & Module1.txtBxTwo
End Sub

Public Sub someMethod()
  MsgBox "Hello World"
End Sub

The Me keyword provides a way to refer to the specific instance of a class in which the code is currently executing.

Which is better me. or me!

If specifically talking about controls on a form it is "Me.", for the simple reason you get intellisense and can avoid lots of coding errors.

 

[dhookom]

I should have stated

I always use Me. when I am referencing a control in VBA. It helps me differentiate between memory variables and control names.

Duane
Hook'D on Access
MS Access MVP