How do I change the rowsource property depending on a tick box

[jacq]

Can I change the rowsource property on a combobox from one table to another depending on whether a tick box is checked. And if I can, how do I select the boundcolumn?

 

[raskew]

Here's some sample code you can study. On a form I have an Option Group (grpSearchFor) with three choices (Name, SSN, Case). This code, on the option group's AfterUpdate event, sets up and opens a combo box (cboFindSomeone) based on the option selected. It also resets the links to an underlying subform (fsubAddresses2), which may not be of concern, but I've left it in just in case.

Private Sub grpSearchFor_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT SSN, Name FROM tblClients "

Select Case grpSearchFor
Case 1 'search by name
strSQL = strSQL & "ORDER BY name; "
Me![cboFindSomeone].ColumnWidths = "0 in;0.75 in"
Case 2 'search by SSN
strSQL = strSQL & "ORDER BY SSN; "
Me![cboFindSomeone].ColumnWidths = "0.75 in;0.75 in"
Case 3 'search by CaseID
strSQL = "SELECT distinct tblCases.CaseID, tblCases.SSN, tblClients.Name" _
& " FROM tblClients RIGHT JOIN tblCases ON tblClients.SSN = tblCases.SSN" _
& " ORDER BY tblCases.CaseID; "
Me![cboFindSomeone].ColumnCount = 3
Me![cboFindSomeone].BoundColumn = 1
Me![cboFindSomeone].ColumnWidths = "0.5 in; 0.75 in; 0.75 in"
Me![cboFindSomeone].ListWidth = 2880 'measurement in twips
End Select
Me.RecordSource = IIf(Me![grpSearchFor] = 3, "qryCasesAndClients", "qryClientsAndCases&quot
[fsubAddresses2].LinkMasterFields = IIf(Me![grpSearchFor] = 3, "[fsubCases].Form![SSN]", "SSN&quot
[fsubContacts2].LinkMasterFields = IIf(Me![grpSearchFor] = 3, "[fsubCases].Form![SSN]", "SSN&quot
Me.Requery
Me![cboFindSomeone].RowSource = strSQL
Me![cboFindSomeone].Requery
Me![cboFindSomeone].SetFocus
Me![cboFindSomeone].Dropdown

End Sub

 

[scking]

Sub TickBox_AfterUpdate

Dim strSQL As String

If Me.TickBox = True Then
strSQL = 'Assign SQL to RowSource
Else
strSQL = 'Assign another SQL string
End If
Me.OtherControl.RowSource = strSQL

End Sub