CODEWITHSUNDEEP
sqlvbams-accesslistbox
arbitel
arbitel

Reputation: 321

How to get MS Access Listbox to show ALL values on YesNo Field

I have a Listbox in Access that shows a list of seasonal employee names. It's row source is:

SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ", " & [FirstName] AS Expr1, tblLeaders.OnPay
FROM tblLeaders
ORDER BY tblLeaders.LastName;

It shows the names in a listed sorted by Last name and presented as: "[LastName], [FirstName]"

On the table [tblLeaders], there is a YesNo field called [OnPay].

My goal is to have a checkbox or combobox that can filter the names in the ListBox by either:

Option 1: ALL (show all OnPay values (yes or no)

Option 2: Active (show only OnPay values set to 'yes')

I've attempted to use the triple state of a checkbox, but that didn't work. I then tried to use a IIF statement to find "Yes" and "No" values if a combobox was set to all, but I kept receiving errors.

Does anyone have any simple ideas how to solve this?

UPDATE Thanks Johanness for this answer. Below is the working vba code I added

Private Sub chkbxShowInactiveLeaders_Click()
    Dim sql1 As String
    Dim sql2 As String
    Dim sql3 As String

    sql1 = "SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ', ' & [FirstName] AS Expr1, tblLeaders.OnPay FROM tblLeaders "
    sql2 = "WHERE tblLeaders.OnPay=TRUE "
    sql3 = "ORDER BY tblLeaders.LastName;"

    If Me.chkbxShowInactiveLeaders.Value = True Then
        Me.lstbxSelectLeader.RowSource = sql1 & sql3
    Else
        Me.lstbxSelectLeader.RowSource = sql1 & sql2 & sql3
    End If

    Me!lstbxSelectLeader.Selected(0) = True

End Sub

Upvotes: 0

Views: 360

Answers (1)

user783388
user783388

Reputation:

As you set vba as tag I assume you are familiar with vba. Then you could use a checkbox and create a OnClick-Action that changes the RowSource of the Listbox. That should be simple enough; something like:

Private Sub CBShowAll_Click()
  Dim sql1 as String
  Dim sql2 as String
  Dim sql3 as String

  sql1="SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ", " & [FirstName] AS Expr1, tblLeaders.OnPay FROM tblLeaders "
  sql2="WHERE tblLeaders.OnPay=TRUE "
  sql3="ORDER BY tblLeaders.LastName;" 

  If me.CBShowAll.Value Then
    me.Leaderlist.RowSource= sql1 & sql3
  Else
    me.Leaderlist.RowSource= sql1 & sql2 & sql3
  End If
  Me.Leaderlist.Requery 'Not sure if this is necessary
End Sub

Upvotes: 1

Related Questions