Cascading Combo Box - grey out if no options

[garywood84]

I have a form with two cascading combo boxes. The options available in the second box depend on what is selected from the first.

Some of the selections which can be made in the first box do not have any options to be selected in the second box and so it currently just pops down empty.

Is there a way that I can make the second box "greyed out" if there are no options for it?

Thanks,

Gary

 

[Banana]

Assuming the second combobox's rowsource is changed dynamically based on the first combobox's selection, you can use rowsource's query recordcount property to check if there are any records and set Enabled to false if recordcount property returns 0.

HTH.

 

[garywood84]

Banana,

Thanks for this. The second combo box does have the row source defined by the After Event options for the first box and so what you suggest sounds like the solution I need.

However, I've very new to Access/VBA coding and so don't know where to start with writing the appropriate code.

Could you possibly post the code for me, or advise me where I can find out how to do it?

Many thanks,

Gary

 

[Banana]

Either check the helpfile on the recordcount property for hints or post your code for AfterUpdate Event so we can be sure to give you correct edits.

 

[garywood84]

Banana,

Here is the code I'm currently using:

Private Sub cboProgramme_AfterUpdate()
On Error Resume Next
cboOption.RowSource = "Select SYS_ProgOpt.Option " & _
"FROM SYS_ProgOpt " & _
"WHERE SYS_ProgOpt.Programme = '" & cboProgramme.Value & "' " & _
"ORDER BY SYS_ProgOpt.Option;"
End Sub


Many thanks,

Gary

 

[Banana]

Seeing your code, I now realize that recordcount isn't what you need. You need to use ListCount property.

Private Sub cboProgramme_AfterUpdate()
On Error Resume Next
cboOption.RowSource = "Select SYS_ProgOpt.Option " & _
"FROM SYS_ProgOpt " & _
"WHERE SYS_ProgOpt.Programme = '" & cboProgramme.Value & "' " & _
"ORDER BY SYS_ProgOpt.Option;"

If Me.cboOption.ListCount = 0 Then
    Me.cboOption.Enabled=False
Else
   Me.cboOption.Enabled=True
End If

End Sub

 

[garywood84]

Many thanks Banana, that's solved the problem prefectly.

 

[garywood84]

I've just discovered a problem with the way this is working and wonder if anyone can help?

The second combo box sets itself to be greyed out after a value has been selected in the first box. This means that when the form is opened to display a record, even if the first box contains a value with no options, the Options combo box is still available (but clicking it doesn't show any items).

Similarly, when you add a new record, the combo box is active by default, turning itself off when the first box has been used to make a selection.

How can I set the box to always be greyed out if the first box contains a value with no options or as default when a new record is created?

Thanks,

Gary

 

[RV]

Gary,

code should be put in two events.
One being the After update event of your first combo box.
The other one being the On Current event of the form.

RV

 

[Banana]

Alternatively, whenever I have code that is going to be used in more than one event, I use Call function to keep my code in one place and makes it easier to maintain.

Example:

Private Sub Something_AfterUpdate

Call ComboBoxChecker

End Sub

Private Sub Form_OnCurrent 

Call ComboboxChecker

End Sub

Public Sub ComboboxChecker

'Execute your code here.

End Sub

 

[garywood84]

Thanks guys, adding the code to the On Current option for the form has made this work perfectly.

Banana - your suggestion of calling the code makes for a very neat solution. However, can you tell me how I write the "public" bit to be called? Where do I type it?

Gary

 

[Banana]

Anywhere you want. It just has to be between the subs (or even at start/end of module if you prefer),just not within/inside any existing sub. You also get to name whatever sub you want to be. Just use the same name for calling and for the sub.

Hope that makes sense.

 

[garywood84]

Banana,

When I click on the On Current box in Access and then click the button with ... on, Visual Basic opens up and I can see the code. It is separate from other code relating to the form by a line across the code page. So, what I can't figure out is how to create a new "section" on the page, so it's not between the "lines" separating out the other bits of code.

I hope this makes sense - it's difficult to describe without showing you what I mean!!

Gary

 

[Banana]

Ah, but if you type in a "Public Sub" just above the "Private Sub Whatever" and below the line, a new line will appear after you finish typing in the Public Sub line.

Try it.