Listbox and checkbox

[thart21]

Stuck on this code.

I have a list box filled with employee names and a command button that opens my form "Projects" filtered by the selected Employee name. I want to take it a step further and add a check box "ckActive" that, if checked shows only Active records (there are 5 other "status" types) and if unchecked shows all status types.

I have tried the following code:

On Error GoTo Err_Select_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Projects"
If Me![ckActive] = True Then
stLinkCriteria = "[projectowner] = " & Me![List5] & " " & _
stLinkCriteria = stLinkCriteria & "[status] = "Active"
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Doing it this way just opens ALL of the records regardless of what is chosen in my list box.

Thanks!

Toni

 

[FLabrecque]

I suggest you do this:

ListBox.RowSource = strLinkCriteria 'change your SQL
ListBox.Requery 'this updates the list box

Note: Your code would needs to take into account the fact that you may need to remove or add the criteria. This code would only add and add and add. Do something like this:

IF instr(strLinkCriteria, "[status] =") > 0 THEN
'Remove the where clause, since you probably had it, and the new check confirms you need all statuses
ELSE
stLinkCriteria = ListBox.RowSource & " AND [status] = 'Active' "
END IF

EDIT: Removed some errors.

 

[thart21]

Thanks for the reply. I am getting an 'object required' error message.

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[projectowner] = " & Me![List5] & " "
ListBox.RowSource = stLinkCriteria
ListBox.Requery 'this updates the list box
stDocName = "Projects"

If InStr(stLinkCriteria, "[status] =") > 0 Then
Else
stLinkCriteria = ListBox.RowSource & " AND [status] = 'Active' "
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Toni

 

[thart21]

My apologies, forgot to give you the line it is referring to.

ListBox.RowSource = stLinkCriteria

Thanks

 

[thart21]

My apologies, forgot to give you the line it is referring to.

ListBox.RowSource = stLinkCriteria

Thanks

 

[thart21]

My apologies, forgot to give you the line it is referring to.

ListBox.RowSource = stLinkCriteria

Thanks

 

[EMP]

Try this:

Private Sub cmdOpenFormProjects_Click()

   If IsNull(Me.List5) Then
      MsgBox "Select a project owner first."
      Me.List5.SetFocus
      Exit Sub
   End If
   
   Dim sCriteria As String
  
   sCriteria = "[ProjectOwner]=""" & Me.List5 & """" & _
         IIf(Me.ckActive = True, " AND [Status]='Active'", "")
   
   DoCmd.OpenForm "Projects", , , sCriteria
   
End Sub