At this point, I just want to be able to group a report by using a 'checkbox' on a form. This form is for reporting and I need to be able to group using different columns in my report.
The first question I have is, should the following grouping code be placed in the reportopen sub (of the report) or in the onclick sub (of the form). Let me say also that the onclick sub opens the report. I am fairly new at VBA and have had a lot of trouble with this. At this point, the group by code is only in the onclick for a button to generate the report.
I receive no errors at all, it just doesnt work.
PLEASE look at the code and let me know what I am doing wrong or if there is ANY more information that you may need. Am I overthinking this? I have read through the posts about grouping using queries and joins but - I am not entirely sure how or if that would (and have tried) solve my grouping issue. Those queries are long since deleted.
Also, this is code that someone off of tek-tips helped me with. So, thanks to them for getting me this far.
Thank you,
Bob
Expand|Select|Wrap|Line Numbers
- Public Function getOrderBy() As String
- Dim chkcoord As Variant
- Dim chksales As Boolean
- Dim chkjobcode As Boolean
- Dim chkflc As Boolean
- Dim chkcust As Boolean
- chkcoord = True
- chkcust = True
- chksales = True
- chkflc = True
- chkcust = True
- chkjobcode = True
- If chkcoord Then
- getOrderBy = "Coordinator, "
- End If
- If chksales = True Then
- getOrderBy = getOrderBy & "Sales #, "
- End If
- If chkjobcode Then
- getOrderBy = getOrderBy & "Job Code, "
- End If
- If chkflc Then
- getOrderBy = getOrderBy & "OfficeLocation, "
- End If
- If chkcust Then
- getOrderBy = getOrderBy & "CUSTOMER NAME, "
- End If
- If Len(getOrderBy) = 0 Then
- getOrderBy = ""
- Exit Function
- Else
- getOrderBy = "ORDER BY " & Left(getOrderBy, Len(getOrderBy) - 2)
- End If
- End Function
Expand|Select|Wrap|Line Numbers
- Private Sub btnReportWriterPreview_Click()
- On Error GoTo Err_btnReportWriterPreview_Click
- getOrderBy
- Dim stDocName As String
- stDocName = "RptPMSPSR"
- DoCmd.OpenReport stDocName, acPreview
- Exit_btnReportWriterPreview_Click:
- Exit Sub
- Err_btnReportWriterPreview_Click:
- MsgBox Err.Description
- Resume Exit_btnReportWriterPreview_Click
- End Sub