Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: pls help me i have three multiselect list box to consolidate and give report,

    iam new ,pls help me to get report of two tables, i have 3 multiselect listbox, which should give report but iam having some error
    i ahve attached the database
    thanks for the help

    Private Sub TRAIL_Click()
    Dim StrWhere As String
    Dim varItem As Variant

    StrWhere = "("
    For Each varItem In Me![PLANTS].ItemsSelected
    StrWhere = StrWhere & "PLANT =" _
    & Chr(39) & Me![PLANTS].Column(0, varItem) & Chr(39) & " OR "
    Next varItem
    StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ") AND ("

    For Each varItem In Me![SHOPS].ItemsSelected
    StrWhere = StrWhere & "SHOP=" _
    & Chr(39) & Me![SHOPS].Column(0, varItem) & Chr(39) & " OR "
    Next varItem
    StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ") AND ("

    For Each varItem In Me![STATUS].ItemsSelected
    StrWhere = StrWhere & "STATUSCER=" _
    & Chr(39) & Me![STATUSCER].Column(0, varItem) & Chr(39) & " AND "
    Next varItem

    StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ")"

    DoCmd.OpenReport "IMTE MASTER REP", acViewPreview ' report name
    ' IMTE MASTER REP query name
    End Sub
    Last edited by eddi; 09-16-11 at 01:07.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You don't do anything with StrWhere after you composed it. You should use:
    Code:
        DoCmd.OpenReport "IMTE MASTER REP", acViewPreview, , StrWhere
    2. The report tries to set a sort order on [NEW LOCATION] and [Status] while these fields do not exist in the RecordSource query [IMTE MASTER REP]

    3. In the third For...Each loop it should be:
    Code:
    ... & Me![STATUS].Column(0, varItem)...
    Not:
    Code:
    ... & Me![STATUSCER].Column(0, varItem)...
    4. After the first For...Each loop, this line:
    Code:
    StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ") AND ("
    will cause an error if nothing is selected in the list [PLANT].
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    66

    three multiselect list box thanks for help SINNDHO

    SINNDHO thank you very much for reply and thanks for helping

    i did the changes in the database and the report is opening

    i have a problem iam not able to get the report of the items which i select from the list box where as all are reports are generated

    i want report the consolidated report of the 3 multiselect listbox

    can you pls help me sorry for the trouble

    iam attaching the new corrected database
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Private Sub TRAIL_Click()
    
        Dim strPlants As String
        Dim strShops As String
        Dim strStatus As String
        Dim StrWhere As String
              
        strPlants = GetList(Me.PLANTS)
        strShops = GetList(Me.SHOPS)
        strStatus = GetList(Me.STATUS)
        If Len(strPlants) Then StrWhere = "PLANT IN " & strPlants
        If Len(strShops) Then StrWhere = IIf(Len(StrWhere) > 0, StrWhere & " AND ", "") & "SHOP IN " & strShops
        If Len(strStatus) Then StrWhere = IIf(Len(StrWhere) > 0, StrWhere & " AND ", "") & "STATUSCER IN " & strStatus
        DoCmd.OpenReport "IMTE MASTER REPP", acViewPreview, , StrWhere
    
    End Sub
    
    Private Function GetList(ByVal List As ListBox) As String
    
        Dim strList As String
        Dim varItem As Variant
        
        For Each varItem In List.ItemsSelected
            If Len(strList) > 0 Then strList = strList & ", "
            strList = strList & "'" & List.Column(0, varItem) & "'"
        Next varItem
        If Len(strList) > 0 Then strList = "(" & strList & ")"
        GetList = strList
        
    End Function
    You should also enforce the declaration of the variables (In the VBA Editor, open the Tools Menu, then Options, select the Editor tab and check the option "Require Variable Declaration"). This will spare you countless hours of debugging.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    66

    Thank you SINNDHO

    Thank you sinndho your reply has served my purpose and i am able to get the reports as i require

    Thanks for taking trouble and writing the new code

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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