Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    Unanswered: Run Report from ListBox selection

    I am referencing this post
    http://www.dbforums.com/microsoft-ac...ist-box-2.html

    Its older and dont know if its archived...

    I am using the same code and for some reason keep getting a window popping up askign for a parameter value...

    The message box I have in the code displays the built string correctly
    But then asks me to enter the parameter value?

    Say I select 1 record in the listbox...it shows me a window with the value in it 'testvalue' , where testvalue is the value of Location_Code field...I type testvalue and it runs the report fine...
    BUt why is it asking for a parameter value?

    I have a table, Query and the Report...


    Report:
    Record Source = qryPrintSelection

    Query :
    Code:
    SELECT Traffic_Counters.Location_Code, Traffic_Counters.Couter_Number, Traffic_Counters.MSAS_Number
    FROM Traffic_Counters;
    ListBox : Code behind the button to grab the selected items from a listbox and push those to the Report above
    Code:
    Private Function GetCriteria() As String
       Dim stDocCriteria As String
       Dim VarItm As Variant
    
       For Each VarItm In List74.ItemsSelected
            stDocCriteria = stDocCriteria & "[Location_Code] = " & List74.Column(0, VarItm) & " OR "
       Next
       If stDocCriteria <> "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
       Else
            stDocCriteria = "True"
       End If
         GetCriteria = stDocCriteria
          'MsgBox GetCriteria
       
    End Function
    
    Private Sub Command73_Click()
        DoCmd.OpenReport "rpt_PrintSelection", acPreview, , GetCriteria()
    End Sub

  2. #2
    Join Date
    Aug 2012
    Posts
    7
    I am referencing this post....THe person that posted is having the same problem...

    http://www.dbforums.com/microsoft-ac...-list-box.html

    THe second post "Syntax" seems to solve his problem...I am using Office 10...I think my problem might be in my syntax as well?????
    stDocCriteria = stDocCriteria & "[Location_Code] = ‘" & List74.Column(0, VarItm) & "’ OR "

    I can run the Report and it returns all the values to it....but when I try and run from the button adn set the Filter of the report it Asks me for Parameter Values....ugggggggggg

  3. #3
    Join Date
    Aug 2012
    Posts
    7
    I can hardcode the report like this: and it runs fine.

    DoCmd.OpenReport "rpt_PrintSelection2", acPreview, , "Location_Code = 'gfhtg' OR Location_Code = '999999' "

    So it appears I have a syntax issue....how do I build a string like this in a loop????

    WHAT I HAVE BELOW is missing the ' ' around the value in the List74.Column(0, VarItm)

    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In List74.ItemsSelected
    stDocCriteria = stDocCriteria & "Location_Code = " & List74.Column(0, VarItm) & " OR "
    Next

    The Above gives me:
    Location_Code = 23432 OR Location_Code = dfgds

    What I need is this:
    Location_Code = '23432' OR Location_Code = 'dfgds'

  4. #4
    Join Date
    Aug 2012
    Posts
    7
    Dim Test2 As String
    Dim VarItm As Variant
    For Each VarItm In List74.ItemsSelected
    Test2 = Test2 & "Location_Code = '" & List74.Column(0, VarItm) & "' OR "
    Next

    If Test2 <> "" Then
    Test2 = Left(Test2, Len(Test2) - 4)
    Else
    Test2 = "True"
    End If

    MsgBox Test2

    DoCmd.OpenReport "rpt_PrintSelection2", acPreview, , Test2

Posting Permissions

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