ComboBox Help on a Form

[CBG2112]

I've created a search form based on several unbound comboboxes. Two of them are cboTaskSearch and cboStatus. When the form opens, cboStatus is populated with all Status values and cboTaskSearch is populated with all Task values. When a user selects a value in the cboTaskSearch, the form will filter cboStatus to only values related to the task. My issue is that after the user searches and clears the values in the comboboxes, cboStatus will only display values based on the last search instead of the original list when the form opens. I've listed the code for cboTaskSearch and the Clear button. Thanks.

Clear button code

Private Sub btnClear_Click()

   Me.cboAssigned = ""
   Me.cboPriority = ""
   Me.cboProject = ""
   Me.cboStatus = ""
   Me.cboTaskSearch = ""
   Me.cboManager = ""
   Me.qryTasks_subform1.Form.RecordSource = " SELECT * FROM qryTasks " & BuildFilter
   Me.qryTasks_subform1.Requery

End Sub

cboTask code

Private Sub cboTaskSearch_AfterUpdate()

  If IsNull(Me!cboTaskSearch) Or Len(Me!cboTaskSearch) = 0 Then
     cboStatus.RowSource = [tblTaskStatusTypes].[StatusDescTx]
     Else
     cboStatus.RowSource = "SELECT StatusDescTx " & _
                        "FROM tblTaskStatusTypes " & _
                        "WHERE TaskTypeTx='" & Me!cboTaskSearch & "'"
  End If
End Sub

 

[pbaldy]

I would expect the first SQL to be the same as the second except no WHERE clause. I'm not sure what you have there is even valid.

 

[CBG2112]

You were right. I had to put the same SQL statement without the WHERE clause on cboStatus_Click() and it worked. Thanks.

 

[CBG2112]

I spoke too soon. It did not work. Thanks though.

 

[pbaldy]

Happy to help, though it doesn't sound like the right place. If you set it in the click event of that combo, won't it over-ride the filtered SQL set when a selection is made in the first combo?

 

[CBG2112]

You're right. I had to put the SQL statement in the GotFocus property of cboStatus. Thanks.

Private Sub cboStatus_GotFocus()
 If IsNull(Me!cboTaskSearch) Or Len(Me!cboTaskSearch) = 0 Then

     cboStatus.RowSource = "SELECT StatusDescTx " & _
                        "FROM tblTaskStatusTypes "
  End If
                   
End Sub

Private Sub cboTaskSearch_AfterUpdate()

  If IsNull(Me!cboTaskSearch) Or Len(Me!cboTaskSearch) = 0 Then
     cboStatus.RowSource = [tblTaskStatusTypes].[StatusDescTx]
     Else
     cboStatus.RowSource = "SELECT StatusDescTx " & _
                        "FROM tblTaskStatusTypes " & _
                       "WHERE TaskTypeTx='" & Me!cboTaskSearch & "'"
  End If
End Sub

 

[pbaldy]

I'm not sure why you have it there either. I'd do it all in the after update event of cboTaskSearch, replacing this line:

cboStatus.RowSource = [tblTaskStatusTypes].[StatusDescTx]

with this one:

cboStatus.RowSource = "SELECT StatusDescTx FROM tblTaskStatusTypes "