Multi-Select List for a Report

  • Thread starter beaker1167 via
  • Start date

beaker1167 via

I am having a hard time grasping SQL. My project is just about complete, and
I come to an area were my progress has stopped. My goal is to print a report
showing the sales of our 5 farms. I allow the user to select any of the
farms to report on, grouped by the farm number. At first I attempted check
boxes, but found the multi-select list to be better. Here is what I have:

A Form named Sales by Farm Selection
Within that: List Box named FarmList
Command Button to Search
A Report Named Sales by Farm Number

The list box has a row source of SELECT [tblFarms].[Farm Number] FROM
[tblFarms] ORDER BY [Farm Number];

The event code for the Search button is:
Private Sub Search_Click()
On Error GoTo Err_Handler

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!SalesbyFarmSelection
Set ctl = frm!FarmList

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [FarmNumber]="
Next varItem

'Trim the end of the strSQL
strSQL = Left$(strSQL, Len(strSQL) - 17)

'Close report to allow for filter
If CurrentProject.AllReports(SalesbyFarmNumber).IsLoaded Then
DoCmd.Close acReport, "SalesbyFarmNumber"
End If

DoCmd.OpenReport SalesbyFarmNumber, acViewPreview, WhereCondition:=strSQL

Exit Sub

If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
End If
Resume Exit_Handler

End Sub

My first problem is an error: 2465 - MS Office can't find the field 'Sales
byFarmSelection' reffered to in your expression. Secondly, I don't know what
to put for OpenArgs on the DoCmd line. Finally, I would like the code to
work and I don't know if there are additional error.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question