Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010

    Question Unanswered: More problems with List Box

    Microsoft Access version 2003

    Hi there, with the following code, I am able to select one employee and have the report return the data accordingly. For some reason, when I select more than one employee the report runs and returns 0 records. Does anyone have a solution for this???

    Thank you.

    Private Sub BTN_EMPLOYEE_Click()
    Dim strEmployee As String
    Dim varItem As Variant

    'make sure a selection has been made
    If Me.LIST_EMPLOYEE.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
    End If

    'add selected values to string
    For Each varItem In Me.LIST_EMPLOYEE.ItemsSelected
    If Len(strEmployee) > 0 Then strEmployee = strEmployee & "'"
    strEmployee = strEmployee & Me.LIST_EMPLOYEE.ItemData(varItem) & "'"
    Next varItem

    'trim trailing comma
    strEmployee = Left(strEmployee, Len(strEmployee) - 1)

    'open the report, restricted to the selected items
    DoCmd.OpenReport "R_HOURS_EMPLOYEE", acPreview, , "NUID = '" & strEmployee & "'"

    End Sub

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Does this help?

    Multi-Select Listbox

    Hint: you have code to trim a comma that never got added in the first place. You also don't want =, you want to build an IN clause.

  3. #3
    Join Date
    May 2010
    Your code is trimming a ending comma, but there is no place in your code where you appear to be adding a comma.

    try this:

    strEmployee = strEmployee & Me.LIST_EMPLOYEE.ItemData(varItem) &  "', "

    To trim the trailing comma, I would use this:

    strEmployee = Trim(strEmployee)
    'trim trailing comma, if there is one
    If Right(strEmployee ,1) = "," Then  
      strEmployee = Left(strEmployee, Len(strEmployee) - 1)
    End If
    Boyd Trimmell aka HiTechCoach (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    Another issue is you will need to use some other comparison with more than one Employee. NUID= will not work. Try NUID In (strEmployee), like this:

    DoCmd.OpenReport "R_HOURS_EMPLOYEE", acPreview, , "NUID IN (" & strEmployee & ")"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts