By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Enable a combo box when an specific item is selected in a combo box

P: 1
I have a form in a database that will be recording attributes of achaeological artifacts. These particular artifacts are stone tools which are being recorded in a form called Lithics. Within the Lithics form is the Group and SpecimenClass combo boxes. In the "Group" box the options are Chipped Stone Tool, Ground Stone Tool, Burnt Stone, and Unmodified Rock. I would like if the SpecimenClass box is enabled only when the Chipped Stone Tool option in the Group box is selected. Hopefully that makes sense. Your help would be much appreciated.
Aug 27 '12 #1
Share this Question
Share on Google+
4 Replies

Seth Schrock
Expert 2.5K+
P: 2,941
You would need to create a function that checks the value of the combo box using an If...Then... statement. You would then call this function in both the form's On_Current event and the Group combo box's After_Update event. The function would look something like this:

Expand|Select|Wrap|Line Numbers
  1. Function GroupCheck()
  2. If Me.Group = "Chipped Stone Tool" Then
  3.      Me.SpecimenClass.enabled = True
  4. Else
  5.      Me.SpecimenClass.enable = False
  6. End If
The above code is what you would use if the combo box Group is bound to a text field. Normally, you would have it bound to a Primary Key field that would be a number, in which case you would replace line 2 with:
Expand|Select|Wrap|Line Numbers
  1. If Me.Group = 10 Then 'or whatever number Chipped Stone Tool is linked to
To call this function, you would just put the function's name in the event.
Aug 28 '12 #2

Expert Mod 15k+
P: 31,494
I wouldn't use If ... Then personally, nor a Function procedure, but otherwise I'd go along with Seth's good advice.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Call GroupCheck()
  3. End Sub
  5. Private Sub cboGroup_AfterUpdate()
  6.     Call GroupCheck()
  7. End Sub
  9. Private Sub GroupCheck()
  10.     With Me
  11.         .cboSpecimenClass.Enabled = (.cboGroup = "Chipped Stone Tool")
  12.     End With
  13. End Sub
I echo Seth's comment about using an ID in place of the text for this. Only you know your design at this stage so I'll leave you to make the necessary changes.
Aug 28 '12 #3

Expert Mod 2.5K+
P: 3,284

Since I am always trying to learn new things, may I ask about a section of your code?

Expand|Select|Wrap|Line Numbers
  1. Private Sub GroupCheck()
  2.     With Me
  3.         .cboSpecimenClass.Enabled = (.cboGroup = "Chipped Stone Tool")
  4.     End With
  5. End Sub
Is there a particular purpose for using the With statement here? Because I know that it operate identically to this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub GroupCheck()
  2.     Me.cboSpecimenClass.Enabled = (.cboGroup = "Chipped Stone Tool")
  3. End Sub
I guess I just want to understand the principle behind it.

Aug 30 '12 #4

Expert Mod 15k+
P: 31,494
That's close Twinny, but not exactly true. Typically, I don't use With unless it replaces multiple copies of the object reference (In this case Me.)

Expand|Select|Wrap|Line Numbers
  1. Private Sub GroupCheck()
  2.     Me.cboSpecimenClass.Enabled = (Me.cboGroup = "Chipped Stone Tool")
  3. End Sub
With represents an efficiency, as well as a clarity, in code. The efficiency is mainly over complicated object references (as the reference is worked out and saved at the start point in a special location) rather than a simple object name reference, but I use it where I can as it helps to read the code when you know all of the . references are to the same object. It also enables lines with more than two references to Me to take up less space so they are more likely to be readable without having to scroll (In fact I make sure none of my code needs scrolling on an 80-char page in order to be read). Obviously, it saves more space with longer object references. It all goes for easier reading of code, therefore easier maintenance, which is very important.
Aug 30 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.