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

Partial value from combo box

P: 28
I have a combo box that is bound to column 1 an ID# in a table. Based on the values in the combo box I would like to enable or disable other fields. The problem is, I have many values that are similar such as Multi-Family / Walkup, Multi-Family / Fourplex, Multi-Family /Duplex, etc. Currently, I have code that does this for two values but I don't want a big long if statement and if more types of Multi-Family / are added I don't want to hard code the id #'s. Here is my current code in the afterupdate event.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboType_AfterUpdate()
  2. If Me.cboType = 3 Or Me.cboType = 34 Then
  3.    txtUnits.Enabled = True
  4.    txtPrice_Unit.Enabled = True
  5. Else: txtUnits.Enabled = False
  6.       txtPrice_Unit.Enabled = False
  7. End If
  8. End Sub
Mar 2 '12 #1

✓ answered by Stewart Ross

Your question was previously removed from the forum as you have supplied very little information to go on. However, one useful technique to consider when you want to have criteria associated with particular values is to include these as Yes/No fields in a reference table which you look up when required, rather than hard-coding the decisions in your subs. Makes it easier to maintain in the future as you add or change values, too.

Your requirements appear to be based on type of property, which is stored as text (though you don't tell us what the name of the source table is or the name of the field concerned). I would suggest that you add to the source table that currently is the rowsource for your combo Yes/No fields such as EnableUnits, EnablePricePerUnit and so on. You would then tick or untick these fields for each separate row in the table to represent the action you want to take when that row's property type is selected.

In your combo you would then add the EnableUnits and EnablePricePerUnit fields to the rowsource of the combo, setting the width of the columns concerned to 0 to hide them. You can then use the combo's Column property to retrieve the relevant value and use it directly to enable or disable the textboxes on your form.

Assuming you currently have just two columns in your combo, if you add the EnableUnits field as the third column and the EnablePricePerUnit field as the fourth column your sub then just becomes the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboType_AfterUpdate()
  2.   txtUnits.Enabled = Me.cboType.Column(2)
  3.   txtPrice_Unit.Enabled = Me.cboType.Column(3)
  4. End Sub
The column references are zero based - the first column is actually Column(0), hence why the third column is shown as Column(2) above.

In summary, if you need to avoid hard-coding logic decisions in a subroutine then place the combinations into a source table as boolean fields that can be set to True or False (Yes or No), set them to the appropriate combination when you add the new value, then read it back when needed to set or enable controls on your form as necessary.

I use this technique a lot in my analytical work for a UK Fire Service. For example, fires in the open are grouped according to what was ignited. Grass fires, heath fires, and refuse fires are different forms of what is known as a Secondary Fire, but forests are one form of Primary Fire. Similarly, pumping out, lift releases and lockouts are forms of Special Services. As there are many such text descriptions (as well as text codes which identify them) I have a lookup table that includes Yes/No fields which identify specific groupings that are used for reporting purposes. This allows me to report on simplified groupings - the text descriptions are too many and too irrelevant to report at Board level.

Maintaining the reference table is much easier than trying to hard code IF..THEN or SELECT..CASE decisions in code, and much easier for my assistants to maintain.

-Stewart

Share this Question
Share on Google+
13 Replies


Expert Mod 2.5K+
P: 2,545
Your question was previously removed from the forum as you have supplied very little information to go on. However, one useful technique to consider when you want to have criteria associated with particular values is to include these as Yes/No fields in a reference table which you look up when required, rather than hard-coding the decisions in your subs. Makes it easier to maintain in the future as you add or change values, too.

Your requirements appear to be based on type of property, which is stored as text (though you don't tell us what the name of the source table is or the name of the field concerned). I would suggest that you add to the source table that currently is the rowsource for your combo Yes/No fields such as EnableUnits, EnablePricePerUnit and so on. You would then tick or untick these fields for each separate row in the table to represent the action you want to take when that row's property type is selected.

In your combo you would then add the EnableUnits and EnablePricePerUnit fields to the rowsource of the combo, setting the width of the columns concerned to 0 to hide them. You can then use the combo's Column property to retrieve the relevant value and use it directly to enable or disable the textboxes on your form.

Assuming you currently have just two columns in your combo, if you add the EnableUnits field as the third column and the EnablePricePerUnit field as the fourth column your sub then just becomes the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboType_AfterUpdate()
  2.   txtUnits.Enabled = Me.cboType.Column(2)
  3.   txtPrice_Unit.Enabled = Me.cboType.Column(3)
  4. End Sub
The column references are zero based - the first column is actually Column(0), hence why the third column is shown as Column(2) above.

In summary, if you need to avoid hard-coding logic decisions in a subroutine then place the combinations into a source table as boolean fields that can be set to True or False (Yes or No), set them to the appropriate combination when you add the new value, then read it back when needed to set or enable controls on your form as necessary.

I use this technique a lot in my analytical work for a UK Fire Service. For example, fires in the open are grouped according to what was ignited. Grass fires, heath fires, and refuse fires are different forms of what is known as a Secondary Fire, but forests are one form of Primary Fire. Similarly, pumping out, lift releases and lockouts are forms of Special Services. As there are many such text descriptions (as well as text codes which identify them) I have a lookup table that includes Yes/No fields which identify specific groupings that are used for reporting purposes. This allows me to report on simplified groupings - the text descriptions are too many and too irrelevant to report at Board level.

Maintaining the reference table is much easier than trying to hard code IF..THEN or SELECT..CASE decisions in code, and much easier for my assistants to maintain.

-Stewart
Mar 3 '12 #2

P: 28
Thanks for your reply. My table name is tblBuilding_Type_list, the field are Building_Type_ID (Autonumber) and Building_Type (Text). Your assumptions are correct, I will try this technique. It never occurred to me to try it this way.

Chris
Mar 4 '12 #3

ADezii
Expert 5K+
P: 8,638
Should you still wish to stay with your original approach, not add the extra Fields/Columns, you can make the Code a little more efficient:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboType_AfterUpdate()
  2. Dim blnType As Boolean
  3.  
  4. With Me
  5.   If IsNull(!cboType) Then Exit Sub
  6.     blnType = (!cboType = 3 Or !cboType = 34)
  7.       !txtUnits.Enabled = blnType
  8.       !txtPrice_Unit.Enabled = blnType
  9. End With
  10. End Sub
Mar 4 '12 #4

P: 28
Thanks, but I have many multi-family buildings that I wish to make other fields disabled as well, not to mention if more types of multi-family dwellings that could be added in the future. Stewart's approach will probably work best. Your approach, I may use on another combo box that has similar code to the above combo box. There is only two choices that can change other fields to disabled and they will never change. Your code is more efficient, thank you for that.

Chris
Mar 4 '12 #5

P: 28
I have run into a problem with the code. I now get the error, invalid use of null when I try to add a new record.
Mar 26 '12 #6

NeoPa
Expert Mod 15k+
P: 31,492
Please read Before Posting (VBA or SQL) Code. I know, technically, you haven't actually posted any code with this error message, but I'm confident once you stop and do a little bit of thinking, you'll realise how much of a mistake that was too (I'm guessing you want help with your problem and that won't be easy with no code to look at).

This should provide all the information you need to make a sensible post. Don't waste it.
Mar 27 '12 #7

P: 28
As per Stewart's instructions above, I entered the following code. It is slightly modified for my purposes. The problem is that when I click on my add new record button, the run-time error code 94 pops up. "Invalid use of Null" This code works in the after update event of the cboBuilding_Type but not in the OnCurrent event of the form. I always want these fields disabled if the enabled unit field in the table is set to false.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. txtUnits.Enabled = Me.cboType.Column(2)
  4. txtPrice_Unit.Enabled = Me.cboType.Column(2)
  5.  
  6. End Sub
Mar 27 '12 #8

NeoPa
Expert Mod 15k+
P: 31,492
You missed out the line number, but otherwise did a good job. We can do without the line # for now.

Stewart's concept is based on the two extra (Boolean) fields being added to the ComboBox. Have you done this?

If you have, then I suspect these fields only make sense if populated. Having records with these fields unpopulated doesn't make sense in this scennario, so I would consider the code good, but your data design or contents need fixing.

Does that make sense?
Mar 27 '12 #9

P: 28
Yes, I only added one boolean field to the combobox for my purposes, those two fields will always be disabled if the boolean is true. Your explanation makes sense, so how would I get around this? Use an if statement to check if there is a value in the combo box first?
Mar 27 '12 #10

P: 28
I followed ADezii suggestion and used a with statement and added my code here as follows:

Expand|Select|Wrap|Line Numbers
  1.   With Me
  2.         If IsNull(!cboType) Then Exit Sub
  3.            txtUnits.Enabled = Me.cboType.Column(2)
  4.            txtPrice_Unit.Enabled = Me.cboType.Column(2)
  5.         End With 
This seems to work, do you think I will still have trouble?

Thanks for your help.
Mar 27 '12 #11

NeoPa
Expert Mod 15k+
P: 31,492
You can use Nz() to replace any Null values found if you really want to, but I suspect (not sure mind) you'd be better off ensuring all your records are properly populated, rather than handling when they're not.
Mar 27 '12 #12

NeoPa
Expert Mod 15k+
P: 31,492
I don't see it as a good solution to your problem (ADezii's answer was a separate suggestion), but if you wanted to use a similar approach you should use it as :
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         If IsNull(.cboType.Column(2)) Then Exit Sub
  3.         .txtUnits.Enabled = .cboType.Column(2)
  4.         .txtPrice_Unit.Enabled = .cboType.Column(2)
  5.     End With
NB. Code indenting is very important. Random indenting can make code very much harder to read and follow. If a line after an If statement is indented further then a reader will naturally assume it is within the True part of that If statement.
Mar 27 '12 #13

P: 28
Thanks for your help, I see this as a better solution. Sorry for the indenting, it is correct in my project.
Mar 27 '12 #14

Post your reply

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