VBA - Access 03 - Iterating through a list box, with an if statement to evaluate

Question

So I have a one list box with values like DeptA, DeptB, DeptC & DeptD. I have a method that causes these to automatically populate in this list box if they are applicable. So in other words, if they populate in this list box, I want the resulting logic to say they are "Yes" in a boolean field in the table.

So to accomplish this I am trying to use this example of iteration to cycle through the list box first of all, and it works great:

dim i as integer
dim myval as string

For i = o to me.lstResults.listcount - 1
   myVal = lstResults.itemdata(i)
Next i

if i debug.print myval, i get the list of data items that i want from the list box. so now i am trying to evaluate that list so that I can have an UPDATE SQL statement to update the table as i need it to be done.

so, i know this is a mistake, but this is what i tried to do (giving it as an example so that you can see what i am trying to get to here)

dim sql as string
dim i as integer
dim myval as string
dim db as database

sql = "UPDATE tblMain SET "

for i = 0 to me.lstResults.listcount - 1
  myval = lstResults.itemdata(i)

    If MyVal = "DeptA" Then
        sql = sql & "DeptA = Yes"
    ElseIF myval = "DeptB" Then
        sql = sql & "DeptB = Yes"
    ElseIf MyVal = "DeptC" Then
        sql = sql & "DeptC = Yes"
    ElseIf MyVal = "DeptD" Then
        sql = sql & "DeptD = Yes"
    End If
Next i

    debug.print (sql)

    sql = sql & ";"
    set db= currentdb
    db.execute(sql)

    msgbox "Good Luck!"

So you can see why this is going to cause problems because the listbox that these values (DeptA, DeptB, etc) automatically populate in are dynamic....there is rarely one value in the listbox, and the list of values changes per OrderID (what the form I am using this on populates information for in the first place; unique instance).

I am looking for something that will evaluate this list one at a time (i.e. iterate through the list of values, and look for "DeptA", and if it is found add yes to the SQL string, and if it not add no to the SQL string, then march on to the next iteration). Even though the listbox populates values dynamically, they are set values, meaning i know what could end up in it.

Thanks for any help, Justin

Answer 1

I don't understand what you're trying to accomplish. However, I suspect your UPDATE statement needs a WHERE clause. ('WHERE OrderID = X', with X replaced by the OrderID of the row you're editing)

I suppose you could create a dictionary object with values initially set to False.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "DeptA", False
dict.Add "DeptB", False
' .. etc.    '

Then go through the items in your listbox, changing the dict value to True.

dict(myval) = True

Finally, build your UPDATE statement based on the dictionary values.

But that all seems like too much work to me. So now I'm wondering about your table structure. Is tblMain set up similar to this?:

OrderID DeptA DeptB DeptC DeptD
------- ----- ----- ----- -----
127     True  False False True

If so, consider a related table for the Dept information.

OrderID Which_Department
------- ----------------
127     DeptA
127     DeptD

The rule of thumb governing this is "columns are expensive; rows are cheap".

Edit: Seems to me you have two sets of items: SetA is all possible items; SetB is a subset of SetA. You want to produce a True for each item in SetB and a False for each SetA item which is not in SetB. Is that correct when you substitute dict (the dictionary object) for SetA and lstResults for SetB?

What I was trying to suggest is load dict with all the possible "DeptX" keys and assign them as False. Then iterate your lstResults and change each of those (in dict) to True. Afterward, build your SQL statement from dict.

Dim varKeys As Variant
Dim i As Integer
Dim strFragment As String

varKeys = dict.keys()
For i = LBound(varKeys) To UBound(varKeys)
    strFragment = strFragment & ", " & varKeys(i) & " = " & dict(varKeys(i))
Next i
strFragment = Mid(strFragment, 3)
sql = sql & strFragment & "WHERECLAUSE"