Results 1 to 11 of 11

Thread: List box (97)

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List box (97)

    List box
    My form allows the user to select items from a list box and then preview the report. The first time I ran it the report ran for a single list box selection. Now when I run it (for whatever selection I make, irregardless) the form continually comes up blank, even after closing it and restarting the form. My first time with a listbox. Please advise.

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (97)

    If you use a listbox to "multi-select" criteria for your report, you can not simply reference the list box from the query to retrieve the criteria.

    You have to use code to "Loop" through the selections to dynamically create the "Where" argument to be used with the DoCmd.OpenReport method.

    First set your lstbox to multi select property to Simple or Extended (depending on what you want, I use Simple most of the time). Leave the Control Source property for this lstbox empty (unbound).

    Now to get the multi select values
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: List box (97)

    You said the user can select "items" from a listbox. Is this a multiselect listbox? And what is the code you are using to open the report?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (97)

    Well the item is a list box with multi select (simple) I also picked up code from Dev Ashish

  5. #5
    New Lounger
    Join Date
    Jul 2001
    Location
    Washington, District Of Columbia, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (00)

    I've tried to follow the procedure you set out for arage last week for my own project. I have a multiselect list box based on a table Committees. I have a report based on a very complicated query with lots of IIF statements and relying on two underlying queries. The query also prompts the user for a variable. Perhaps not surprising, my adaptation of your code (using elements from Helen Feddema) didn't seem to work right. Would you mind helping me with this? Code below.

    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click

    Dim lst As Access.ListBox
    Dim strWhere As String
    Dim varItem As Variant
    Dim lngID As Long

    Set lst = Me![fldSelectCommittee]

    If lst.ItemsSelected.Count = 0 Then
    MsgBox "Please select at least one committee"
    lst.SetFocus
    Exit Sub
    End If

    lngID = lst.Column(0, varItem)
    For Each varItem In lst.ItemsSelected
    strWhere = strWhere & "CommitteeID =" _
    & Chr(39) & lngID & Chr(39) & " Or "
    Next varItem

    strWhere = Left(strWhere, Len(strWhere) - 4)

    DoCmd.OpenReport "MeetingCalendar", acPreview, , strWhere

    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

    End Sub

    Also - here's the rather inelegant SQL for the primary underlying query.

    SELECT Meetings.CommitteeID, Committees.Committee, IIf([Meetings]![CommitteeID]=0,[Purpose],[Committee]) AS [Group], IIf(IsNull([SubGroup]),[Group],[Group] & " - " & [SubGroup]) AS MtgGroup, IIf([EventType]="Meeting","",[EventType]) AS Type2, IIf([Type2]="Subgroup Meeting","",[Type2]) AS Type1, IIf([Type1]="",[MtgGroup],[MtgGroup] & " - " & [EventType]) AS Type, IIf([EventType]="Report",[ReportTitle],[Type]) AS Title, aryDate.StartMonth, Meetings.Year, aryDate.Days, IIf([TentativeDates]=Yes,"Tentative","") AS Tentative, Meetings.Time, Meetings.StartMonthID, Meetings.StartDay, aryDate.StartMonth
    FROM (Committees RIGHT JOIN Meetings ON Committees.CommitteeID = Meetings.CommitteeID) LEFT JOIN aryDate ON Meetings.MeetingID = aryDate.MeetingID
    WHERE (((Meetings.StartMonthID)>=[Enter Current Month - by number]))
    ORDER BY Meetings.StartMonthID;



    Current Error: After it offers the user prompt, I get the error: Expression is typed incorrectly, or is too complex to be evaluated.

    Thanks for any help you can provide.

    Alysha Taylor

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: List box (97)

    The best thing to do is to place a breakpoint in your code at the line that Trims strSQL after you have created it. Examine the value of strSQL to see what it looks like, and even copy and paste it into the SQL view of a query and try to run it there.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (97)

    I did run the debug at that point you mentioned & it looked fine like this in the debug window using debug.print:

    Select * from tblFinancialAudit where [ReferenceNumber]=1401-63 OR [ReferenceNumber]=1401-64

    And yes, when I substitute the value in strSql into the sql view of the query, it will run correctly.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: List box (97)

    I'm surprised it will run in SQL view. The problem is that your ReferenceNumbers aren't really numbers, they are strings. Modifiy your original code to bracket your entries with quotes, like this:
    <pre>You had:
    strSQL = strSQL & ctl.ItemData(varItem) & " OR [ReferenceNumber]="

    Change to:
    strSQL = strSQL & chr(34) & ctl.ItemData(varItem) & chr(34) & " OR [ReferenceNumber]="
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (97)

    when running the report I did notice that I didn

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: List box (97)

    You can use single or double quotes in the SQL statement to delineate strings.

    Looking back on your code, I don't know what you are using that strSQL string for anyway! I'd remove any competing WHERE clause from the query behind the report. Then instead of strSQL I'd form strWHERE, which is the WHERE clause part of strSQL (but without the word WHERE). Then use it in the OpenReport method.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List box (97)

    Well, I finally found what was wrong with it, a missing filtername argument of the openReport method,

    Well, problem solved anyway.

    DoCmd.OpenReport stDocName, acPreview, , strWhere

Posting Permissions

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