CODEWITHSUNDEEP
vbams-access
grichmer
grichmer

Reputation: 47

On form load, how can I automatically make listbox selections based on yes/no column in table

I have a table (tblStates) containing two columns, "State" (string) and "Selected" (Yes/No). My form contains a multi-select listbox (lstStates) that displays all states listed in tblStates. When the form first opens, I want the listbox to already have states selected based on the "Selected" column in tblStates. Below is the code that I'm currently working on.

Dim rs As DAO.Recordset
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblStates")

Do Until rs.EOF = True
    For i = 0 To Me.lstStates.ListCount - 1
        If rs!Selected = Yes Then
            Me.lstStates.Selected(i) = True
        End If
    Next
    rs.MoveNext
Loop

While it doesn't error out, the If condition is never met, even though most of the states have a "Yes" status in tblStates. Am I evaluating the recordset values correctly?

Upvotes: 0

Views: 262

Answers (1)

Olivier Depriester
Olivier Depriester

Reputation: 1625

You didn't quote the "Yes" so it is considered as a variable and not a string

 If rs!Selected = "Yes" Then

You should add the keywords Option Explicit at the beginning of each of your UserForm/Module to avoid this kind of issue. You would have had a compilation error as Yes is not a declared variable.

EDIT : Let's say your tblStates has a field State and you filled the lstStates with some State values : With lstStates.MultiSelect = fmMultiSelectMulti set, your code should be

Dim rs As DAO.Recordset
Dim i As Integer
Dim found as Boolean 
Set rs = CurrentDb.OpenRecordset("SELECT State, Selected FROM tblStates")
Do Until rs.EOF
    i = 0
    found = False
    While i < Me.lstStates.ListCount And Not found
      ' Check if the current list item matches the State from the current record
      If rs!State = Me.lstStates.List(i) Then
         ' Once this item is found, it is selected or not depending on the DB value
         Me.lstStates.Selected(i) = rs!Selected
         ' Item found for this record : not need to search other items in the list
         found = True
      End If
      i = i + 1
    Wend
    rs.MoveNext
Loop

Should do the job if the lstStates has 1 column. If several columns, you have to handle the colum index here : If rs!State = Me.lstStates.List(i, [theColumnIndex]) Then

Upvotes: 1

Related Questions