VBA help

[mnickell]

I am working on writing the code for a multiselect list box and am running into problems. I have posted in other sections and had some help with people giving me the code to paste in from other examples but I am so new to VBA I am not sure what I need to change and what I do not need to change. Is there anyone who can give me a rundown on different pieces of this code so that I can understand it better?

[ItsMe]

You will have to provide examples of what you have tried and where exactly you believe you are failing.

Basically, multi select list boxes have a value that is an array. It is an array of strings. You can store an array of strings in type Variant. You can also declare a string array and iterate the strings within the array (more complex). You will need to use variables to collect data as you iterate the List Box's value(s).

[mnickell]

Okay so here is the code that I have currently. My problem is, The code is based off of a two column list box, mine is only one. The names of the items from the example are obviously different than mine but I don't know what they are in the example so I dont know what to replace them with from my data base. I know that I need to know the name of my list box (ListBox), the form (SelectEntity), the click command (Command6_Click), and I dont know if I need it in the code but the form is set to run a query (FullListQuery). I just don't know enough about it to be able to write it and/or catch a mistake when I make one

Private Sub Command6_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "Products by Category"
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
End Sub

[ItsMe]

You need to focus on retrieving the correct data from your listbox before you can begin to "do something" with the data. Once you know how to do this correctly, you can move to the next step. There is not any instant gratification in VBA.

Get data from the ItemsSelected property. Here is an example.
https://www.accessforums.net/forms/l...tml#post224893

Here is an explanation of what the ItemsSelected property is
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[mnickell]

Right I understand that but I just don't understand how to get to that point from what I have. The examples give me the same problem from the other examples that I have. I have no reference point to what they are referencing. I don't know left from right on the examples. Is there any way that you can talk me through how to write that part of if and what each piece is because thats where I am having problems at this point

[ItsMe]

...The code is based off of a two column list box, mine is only one. The names of the items from the example are obviously different than mine but I don't know what they are in the example so I dont know what to replace them with from my data base....

To move past this first hurdle, I suggest you follow the recommendation of post #4. Copy your listbox over to a new, blank form for testing purposes. Create a code block that iterates and retrieves data from the Multi Select List Box. Study the Immediate Window to ensure the results are suitable before moving to the next step.