Combo Box - Enable = False if no records

[Bonnie]

Hi,

Does anyone know how to test for records in a combobox and, if there are
none, set enabled to false. I would like the user to not fool with the
element if it doesn't apply to the part they have selected.

Here is the code I am using to filter the records in the combo boxes
(cascading).

Me.Text2.RowSource = "Select distinct parts.width " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.width;"

Me.Text4.RowSource = "Select distinct parts.length " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.length;"

Me.Text6.RowSource = "Select distinct parts.height " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.height;"

Thanks in advance.

 

[fredg]

Hi,

Does anyone know how to test for records in a combobox and, if there are
none, set enabled to false. I would like the user to not fool with the
element if it doesn't apply to the part they have selected.

Here is the code I am using to filter the records in the combo boxes
(cascading).

Me.Text2.RowSource = "Select distinct parts.width " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.width;"

Me.Text4.RowSource = "Select distinct parts.length " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.length;"

Me.Text6.RowSource = "Select distinct parts.height " & _
"FROM parts " & _
"WHERE parts.ItemDesc = '" & descrip.Value & "' " & _
"ORDER BY parts.height;"

Thanks in advance.

Code the Form's Current event:
MeText2.Enabled = Me.Text2.ListCount > 0
You also will have to place the same code in whatever combo's
AfterUpdate event changes the Text2 rowsource.

Do the same for the other combo boxes.

You might want to consider using a different name for your combo
boxes. Text is usually associated with a text control. Also it should
be more meaningful to someone who may try to modify your database in
the future. Perhaps something like "cboWidth", etc.

In addition, Width and Height are reserved Access/VBA words and should
not be used as field names.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Bonnie]

Thanks Fred. I actually tried this but maybe I was doing something wrong.
I'll give it another go and change my reserved words.

Appreciate the help.

Bonnie

 

[Bonnie]

Hi Fred,

Not sure why but (there is nothing in this field) - when I check it in the
Immediate Window, the ListCount is 1 when I check:
?FORMS.FORM1.TEXT6.listcount

but also says NULL if I just check: forms!form1!text6

So the control stays enabled.

Any ideas?

Bonnie

Thanks.