Partial value from combo box

[rovral]

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.

Private Sub cboType_AfterUpdate()
If Me.cboType = 3 Or Me.cboType = 34 Then
   txtUnits.Enabled = True
   txtPrice_Unit.Enabled = True
Else: txtUnits.Enabled = False
      txtPrice_Unit.Enabled = False
End If
End Sub

[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, EnablePricePerU nit 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 EnablePricePerU nit 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 EnablePricePerU nit field as the fourth column your sub then just becomes the following:

Private Sub cboType_AfterUpdate()
  txtUnits.Enabled = Me.cboType.Column(2)
  txtPrice_Unit.Enabled = Me.cboType.Column(3)
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

[rovral]

Thanks for your reply. My table name is tblBuilding_Typ e_list, the field are Building_Type_I D (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

[ADezii]

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:

Private Sub cboType_AfterUpdate()
Dim blnType As Boolean

With Me
  If IsNull(!cboType) Then Exit Sub
    blnType = (!cboType = 3 Or !cboType = 34)
      !txtUnits.Enabled = blnType
      !txtPrice_Unit.Enabled = blnType
End With
End Sub

[rovral]

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

[rovral]

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.

[NeoPa]

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.

[rovral]

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_Typ e 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.

Private Sub Form_Current()

txtUnits.Enabled = Me.cboType.Column(2)
txtPrice_Unit.Enabled = Me.cboType.Column(2)

End Sub

[NeoPa]

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?

[rovral]

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?

[rovral]

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

  With Me
        If IsNull(!cboType) Then Exit Sub
           txtUnits.Enabled = Me.cboType.Column(2)
           txtPrice_Unit.Enabled = Me.cboType.Column(2)
        End With

This seems to work, do you think I will still have trouble?

Thanks for your help.

[NeoPa]

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.

[NeoPa]

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 :

    With Me
        If IsNull(.cboType.Column(2)) Then Exit Sub
        .txtUnits.Enabled = .cboType.Column(2)
        .txtPrice_Unit.Enabled = .cboType.Column(2)
    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.

[rovral]

Thanks for your help, I see this as a better solution. Sorry for the indenting, it is correct in my project.