List box (97)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

You had:
strSQL = strSQL & ctl.ItemData(varItem) & " OR [ReferenceNumber]="

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

Reply | Quote

when running the report I did notice that I didn

Reply | Quote

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.

Reply | Quote

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

Reply | Quote