Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: Open a report using a listbox as a parameter

    I've been searching on this one for a bit. I didn't find it in the DB forums archives but I found these two pages that I think are close:
    http://support.microsoft.com/?kbid=300693
    http://support.microsoft.com/?kbid=208630

    The problem is, I don't totally follow what they are saying.

    Here is the issue
    Someone needed a report to show issues in a an access thing (db, app, dbms) by class. so I built a parameter query, then I built a report to call that. No problems. Now I would like to take it a step further.

    Goal:
    Build a form with a listbox that shows the values for class but is bound by the number that is used to reference it in my other tables, when the user clicks the command button, whatever is selected in the box is used in place of the parameter on the query, no little popup box or anything.

    Now the two links pasted above seem to indicate that I can't do it quite that way but that I would have to us the "on Build" event of the report to call a stored procedure. Does that seem like the most correct path?

    Also, since I am still fussing with this, if a user selects multiple values, what is passed?
    Guidance is appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, since I am still fussing with this, if a user selects multiple values, what is passed?
    Why are we letting our users select more than one item?
    Here's a quick test to find out what happens when we multi-select (stick it behind a command button )
    Code:
    MsgBox Me.MyListBox.Value
    Ta-da
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And here's one possible solution for you
    Code:
    DoCmd.OpenReport "test", acViewNormal, "MyFieldID = " & Me.MyListBox.Value
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    I tried that, I got a message saying it was an invalid use of null.
    could it be an array?

    as for, why let them select multiple? Why not?
    In this case though, they want to be able to display issues with the type of "issue", "question" and so on.
    Is there a reason not to?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It makes it more complicated
    I don't have a copy of access to hand so I'm working completely on theory.
    Code:
    Me.MyListBox.Selected '(.Selection?) Check through all the properties of the object and play with them - see what results are returned!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    no worries. I agree it makes it more complicated. no argument there.
    I'm working with the code you gave me. Looks good, I'm not quite there but thanks for the code, I think it'll get me there.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    I don't know if you can adapt this to your needs or not, but I created a search form a while back for a dB I was working on. It allowed the user to select multiple items from a listbox for criteria for a search (also allowing the user to search by date range, key word/phrase, etc., but that's unimportant to the issue at hand). Here's a snippet of the code I used for that that you might be able to use:
    Code:
    Dim frm As Form, ctl As Control, varItm As Variant, strTemp As String, _
        i As Integer, intMax As Integer, blnAnySelected As Boolean
    Set frm = Forms!FrmName
    
    blnAnySelected = False
    i = 0
    intMax = Me.LstBoxName.ListCount
    For i = 0 To intMax
        If Me.LstBoxName.Selected(i) Then
            blnAnySelected = True
            Exit For
        End If
    Next i
    If blnAnySelected Then
        strTemp = "("
        Set ctl = frm!LstBoxName
        For Each varItm In ctl.ItemsSelected
            If strTemp <> "(" Then strTemp = strTemp & " OR "
            strTemp = strTemp & "(TblName.[FldName]) = '" & ctl.ItemData(varItm) & "'"
        Next varItm
        strTemp = strTemp & ")"
        If strWHERE <> "" Then strWHERE = strWHERE & " AND "
        strWHERE = strWHERE & strTemp
    End If
    
    DoCmd.OpenReport "RptName", acViewPreview, , strWHERE
    DoCmd.SelectObject acReport, "RptName"
    Me.Geek = True

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    nice, It's gonna take me a while to break that down into something I understand but I appreciate it. There will be many more filter and searchs to come and it looks like your code addresses some of those issues I haven't even gotten to yet.

Posting Permissions

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