Selection Form that transfers Named Fields to a report

[Blitznb]

I am struggling with marrying forms to reports based on my selections. It would be very nice if I could be pointed to a reference or helpfull info.

I am limited in Access, but feel as though I can grasp conpects once I see them.

I have the following Tables
Employees
Customers
Principals
Orders

A Form in the style of Northwinds Sales Report Dialog, however it adds an "additional master grouping field". This Form contains customers, order date, principals, etc in the first text box and the same in the second test box. There is then a text box for time frames Yearly, quarterly or monthly. Each text box allows for a filter.

What I struggle with is how to marry the selections back to Reports. The first text box transfers the selection to "SalesGroupingField" and the second text box transfers the selection to "SalesReportsFields".

Based on Northwinds it appears standard to have a named "varible" in the Detail section. Is it ok to do the same in a header for the "Master Grouping Field" in order to group by?

Another one of the struggles is that I need to have columns for "Sales", "Commission" and "Employee". Northwinds appears to assign 1,2,3,4 as quarters in Yearly and quarterly report, which appears to be a crosstab report. However is reports are based on singular column values of sales.

Based on what I have supplied in anyones oppinion, would this Northwinds/crosstab style report be best for my design considering I am using named fields? If so does anyone have a good link on using multiple column fields in crosstab reports with code?

 

[Blitznb]

If anyone would be so kind to share if I am atleast headed in the right direction?

Form

Option Compare Database
Option Explicit
Enum SalesPeriod1Enum
        ByMonth = 1
        ByQuarter = 2
        ByYear = 3
End Enum
Sub PrintReports(ReportView As AcView)
    ' This procedure used in Preview_Click and Print_Click Sub procedures.
    ' Preview or print report selected in the ReportToPrint option group.
    ' Then close the Print Sales Reports Dialog form.
    Dim strReportName As String
    Dim strReportFilter As String
    Dim lOrderCount As Long
    ' Determine report filtering
    If Nz(Me.lstReportFilter) <> "" Then
        strReportFilter = "([SalesGroupingField1])= """ & Me.lstGroupingFilter & """)" & "([SalesGroupingField])= """ & Me.lstReportFilter & """)"
        
    End If
    
    ' Determine reporting time frame
    Select Case Me.lstSalesPeriod1
    Case ByYear
        strReportName = "Yearly Sales Report1"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear)
    Case ByQuarter
        strReportName = "Quarterly Sales Report"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
    Case ByMonth
        strReportName = "Yearly Sales Report1"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Month]=" & Me.cbMonth)
    End Select
        
    If lOrderCount > 0 Then
        TempVars.Add "Group By1", Me.lstSalesGrouping.Value
        TempVars.Add "Group By", Me.lstSalesReports.Value
        TempVars.Add "Order By", Me.lstSalesGrouping.Value
        TempVars.Add "Display1", DLookupStringWrapper("[Display]", "Sales Reports2", "[Group By]='" & Nz(Me.lstSalesGrouping) & "'")
        TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
        TempVars.Add "Year", Me.cbYear.Value
        TempVars.Add "Quarter", Me.cbQuarter.Value
        TempVars.Add "Month", Me.cbMonth.Value
        
        eh.TryToCloseObject
        DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
    Else
        MsgBoxOKOnly NoSalesInPeriod
    End If
End Sub

Private Sub Form_Load()
    SetSalesPeriod1 ByYear
    InitFilterItems
End Sub

Sub SetSalesPeriod1(SalesPeriod1 As SalesPeriod1Enum)
    Me.lstSalesPeriod1 = SalesPeriod1
    Me.cbQuarter.Enabled = (SalesPeriod1 = ByQuarter)
    Me.cbMonth.Enabled = (SalesPeriod1 = ByMonth)
End Sub
Private Sub lstSalesPeriod1_AfterUpdate()
    SetSalesPeriod1 Me.lstSalesPeriod1
End Sub

Private Sub lstSalesReports_AfterUpdate()
    InitFilterItems
End Sub

Private Sub InitFilterItems()
    Me.lstReportFilter.RowSource = DLookupWrapper("[Filter Row Source]", "Sales Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
    Me.lstReportFilter = Null
End Sub
Private Sub lstSalesGrouping_AfterUpdate()
    InitFilterItems1
End Sub

Private Sub InitFilterItems1()
    Me.lstGroupingFilter.RowSource = DLookupWrapper("[Filter Row Source]", "Sales Reports2", "[Group By]='" & Nz(Me.lstSalesGrouping) & "'")
    Me.lstGroupingFilter = Null
End Sub
Private Sub cmdPreview_Click()
    PrintReports acViewReport
End Sub

report for yearly sales

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrorHandler
    Dim strSQL As String
    If IsNull(TempVars![Display1]) Or IsNull(TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Or IsNull(TempVars![Group By1]) Or IsNull(TempVars![Group By]) Then
        DoCmd.OpenForm "Sales Reports Dialog2"
        Cancel = True
        Exit Sub
    End If
    
    strSQL = "SELECT [Year]"
    strSQL = strSQL & ", [Month]"
    strSQL = strSQL & ", First([" & TempVars![Display1] & "],) AS SalesGroupingField1"
    strSQL = strSQL & ", First([" & TempVars![Display] & "]) AS SalesGroupingField"
    strSQL = strSQL & ", Sum([Sales]) AS [Total Sales]"
    strSQL = strSQL & ", Sum([Commission]) AS [Total Commission]"
    strSQL = strSQL & ", Sum([Employee]) AS [Total Employee]"
    strSQL = strSQL & ", First([Sales Analysis].[Month Name]) AS [Month Name]"
    strSQL = strSQL & " FROM [Sales Analysis] "
    strSQL = strSQL & " Where [Month]=" & TempVars![Month] & " AND [Year]=" & TempVars![Year]
    strSQL = strSQL & " GROUP BY [Year], [Month], [" & TempVars![Group By1] & "], [" & TempVars![Group By] & "];"
    
    
    Me.RecordSource = strSQL
    Me.SalesGroupingField1_Label.Caption = TempVars![Display1]
    Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
    Exit Sub
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Yearly Sales Report1_Open", "strSQL = " & strSQL) Then
        Resume
    Else
        Cancel = True
    End If
End Sub

 

[jdraw]

Is your main concern with report formats? You seem to be asking for design advice, but haven't identified a specific problem.
Have you tried some things? Did you get errors?
Did you test your SQL in the form of queries to see if the proper data was selected?

Only you know what your requirements are, so you have to say what you want; identify issues as they arise and seek specific corrective/options actions.

Can you mock up a sample of the report you want?
Can you work with some individual reports, and select the report and possibly parameters from a menu?

Here's a link you may find helpful
http://www.fontstuff.com/access/index.htm examples with code and rationale

Good luck.

 

[Blitznb]

Hello Jdraw

Its giving Run-time error 3071

The code is for the orginal thread, where I am looking to know if it is possible to have a report that that generates based on a form where the form passes varibles to the report. Guidance is requested on if I am headed in the right direction in regaurds to grouping and including proper columns and fields based on the varialbe values.

I believe I have the code correct.......to some extent atleast. The issues is marring back to form. When I run the form and select, for instance; principal (1stsalesgrouping), customer (1stSalesReports) and Yearly (1stSalesPeriod) the code asks for parameters (SalesGrouingField1_Label, SalesGroupingField_Label) so some issues exist in code, but the bigger issue is it breaks/debugs on
DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
strReportName = Yearly Sales Report1
ReportView = 5
acWindowNormal = 0
Which are all ok.......to my knowledge

strReportFilter appears to be the problem. Where it is supposed to equal
"([SalesGroupingField1])= """ & Me.lstGroupingFilter & """)" & "([SalesGroupingField])= """ & Me.lstReportFilter & """)"

 

[Blitznb]

Is your main concern with report formats? You

Good luck.

Link was exactly what I needed. Thanks!