Help me debug....Please... 2

[CindiN]

Hi all,
I have a form that has a list box with all the employees names and a command button that when clicked, will open a report for each selected employee.

Problem is that it only runs the report on the last name selected on the form. Can anyone tell me what I'm missing in my code?

Function NameList() As String
Dim Itm As Variant
Dim ListNames As Variant
NameList = "("
For Each Itm In lstNames.ItemsSelected
NameList = ListNames & Chr(34) & lstNames.ItemData(Itm) & Chr(34) & ", "
Next
NameList = Left(NameList, Len(NameList) - 2) & &quot"
If Len(NameList) = 2 Then
NameList = ""
Else
NameList = "Employee_Name In( " & NameList
End If
End Function


Private Sub Command2_Click()
DoCmd.OpenReport "FinalWorksheetRPT", acViewPreview, , NameList

End Sub

I really appreciate any help.
Thanks, CindiN

 

[dakota81]

It's all in this line:

NameList = ListNames & Chr(34) & lstNames.ItemData(Itm) & Chr(34) & ", "


Each time through the loop you're tossing out the work done on the previous items. Try NameList = NameList & " " & ListNames ... or something.

 

[CindiN]

Thank Dakota
I'm not too good at VB, can you be more specific?
Thanks, CindiN

 

[judgehopkins]

I guess I am not understanding what you want to do.

Are you saying that you have a dropdown list and you can select x-number of employees from that list? And then you want the report to print for each employee you selected?

NameList = ListNames & Chr(34) & lstNames.ItemData(Itm) & Chr(34) & ", "

What dakota81 was telling you is absolutely correct. After this line is your NEXT command and then whatever was in NameList is gone by the time your procedure reiterates.

Hope this helps (from Microsoft Access help):

ItemsSelected Property Example

The following example prints the value of the bound column for each selected row in a Names list box on a Contacts form. To try this example, create the list box and set its BoundColumn property as desired and its MultiSelect property to Simple or Extended. Switch to Form view, select several rows in the list box, and run the following code:

Sub BoundData()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant

    Set frm = Forms!Contacts
    Set ctl = frm!Names
    For Each varItm In ctl.ItemsSelected
        Debug.Print ctl.ItemData(varItm)
    Next varItm
End Sub

The next example uses the same list box control, but prints the values of each column for each selected row in the list box, instead of only the values in the bound column.

Sub AllSelectedData()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant, intI As Integer

    Set frm = Forms!Contacts
    Set ctl = frm!Names
    For Each varItm In ctl.ItemsSelected
        For intI = 0 To ctl.ColumnCount - 1
            Debug.Print ctl.Column(intI, varItm)
        Next intI
        Debug.Print
    Next varItm
End Sub

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[CindiN]

Thank you Judge,
You're assumption of what I'm trying to do is correct. I'll try to figure out what you've written, but as I mentioned above, I'm really not familiar with VB. Someone else wrote this code.

Thanks so much for your help.
CindiN

 

[judgehopkins]

Go to the keyword search in Access forms and type in
run report selected name

There are tons of threads. Hope one of them works.

I have set this thread for notification if anyone answers since I want to know the answer too! Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[CindiN]

Thanks Judge....That's great! I appreciate you telling me where I can look up the info. I sure don't mind researching for answers if I know where and how to look things up, and actually that helps me learn it better so I'll remember the next time. I'll let you know if I do discover the answer. In the meantime, if you do get any responses I'll be checking in.
Thanks so much,
CindiN

 

[judgehopkins]

http://communities.microsoft.com/newsgroups/default.asp?icp=Prod_Office&sLCI

CindiN, go to Microsoft's Access/Forms coding forum and post your question with as much detail and code as possible.

Microsoft monitors the forum and if you ask an interesting or difficult enough question, an actual human being from Microsoft (or other guru) will answer it.

The forum has lots of junk in it and it takes a long time before your question is posted. When your question is posted, make a note of the time and date because there is no e-mail notification of any answers posted.

Good luck and let me know what you find out. Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[CindiN]

Thanks Judge, I'll try it