Changing Row Source of Combo Box based upon Check Box selection

[Bcanfield83]

Hi Folks,

I'm looking to modify the Row Source of a couple Combo Boxes on a form based upon whether or not a check box is True or False.
Is there a way to do this through one of the Combo Box properties? (e.g.: obviously "AfterUpdate" wouldn't work since the Row Source needs to be modified prior to the Combo Box being clicked).
Currently, I have it in the AfterUpdate Property on the Check Box control but curious if there's another way to incorporate it.

Private Sub chkRCActiveYN_AfterUpdate()


Dim strSQL As String


strSQL = "SELECT SEC_ROLE_LISTING.RLS_ID, SEC_ROLE_LISTING.RLS_NAME " & _
 " FROM SEC_ROLE_LISTING " & _
 " WHERE SEC_ROLE_LISTING.RLS_ENABLED_YN = 'Y'" & _
 " ORDER BY SEC_ROLE_LISTING.RLS_NAME"


If Me!chkRCActiveYN = True Then


    Me!cboNETRoleCompare1.RowSource = strSQL
    Me!cboNETRoleCompare2.RowSource = strSQL


End If


End Sub

[Micron]

I would use your current approach, partly because altering combo row source at the point of click seems like a micro second too close to when it's needed to be known and isn't necessary to leave it to that point.

[Bcanfield83]

Thanks! It does seem to be working fine, so "if it ain't broke.."
In the past, I've taken a different approach of creating 2 distinct combo boxes - each with a different row source, and then use the check box control to either hide or show the appropriate one (e.g.: if box is checked, set cbo1 visible property to "True" and cbo2 visible property to "False"; if box is unchecked, then do the reverse.)