VBA - Access 03 - Iterating through a list box, with an if statement to evaluate
- by Justin
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
