B
beaker1167 via AccessMonster.com
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_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Search_Click"
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.
Jim
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_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Search_Click"
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.
Jim