Create Dynamic Report by Filtering a Crosstab Query from Combo Boxes on Unbound Form

Posted on 2012-09-06
Last Modified: 2013-11-28
Hi everyone,

I have a crosstab query in my MS Access 2007 database that I would like to filter based upon
user selections from the combo boxes [CmbMonth] and [CmdYear] on an unbound form [frm_show_Monthly_Payment_Records].  

I would like users to make a selection from the two combo boxes (CmbMonth) and [CmdYear] then push a button to view a dynamic report based on the Crosstab query below.

I have the crosstab query/SQL statement (also provided below) working code as shown below.

The problem is that I am unable to get the dynamic report to work. I am receiving the errors shown in Figures 2 & 3 below.

Crosstab Query
PARAMETERS [Forms]![frm_show_Monthly_Payment_Records]![CmbMonth] Text (2), [Forms]![frm_show_Monthly_Payment_Records]![CmdYear] Text (2);
TRANSFORM Sum(PaymentHx.Amount) AS SumOfAmount
SELECT Children.Firstname+' '+UCase(Children.Lastname) AS TheChild
FROM Children INNER JOIN PaymentHx ON Children.childID=PaymentHx.ChildID
WHERE Mid(PaymentHx.PayWeek,1,2)=[Forms]![frm_show_Monthly_Payment_Records]![CmbMonth] And Mid(PaymentHx.PayWeek,7,2)=[Forms]![frm_show_Monthly_Payment_Records]![CmdYear]
GROUP BY Children.Firstname, Children.Lastname
ORDER BY Children.Lastname
PIVOT PaymentHx.PayWeek;

VBA Code for generating dynamic crosstab report
Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
 Dim rstReport As Recordset
Dim strQry As String

Set rstReport = CurrentDb.OpenRecordset(Me.RecordSource)

'MsgBox "Fields Count" & rs.Fields.Count, vbOKOnly, "Welcome"

     For intI = 1 To rstReport.Fields.Count - 1
         Me("Lbl" & intI - 1).Caption = rstReport.Fields(intI).Name
      Next intI

     'Place correct controlsource
      For intI = 1 To rstReport.Fields.Count - 1
         Me("Col" & intI - 1).ControlSource = rstReport.Fields(intI).Name
     Next intI

     'Place Total field controlsource
      For intI = 1 To rstReport.Fields.Count - 1
         Me("ColTotal" & intI - 1).ControlSource = "=SUM([" & rstReport.Fields(intI).Name & "])"
     Next intI

    If rstReport.Fields.Count < 6 Then
            Lbl4.Visible = False
    End If
 End Sub

VBA Code for button on form that opens report
Private Sub cmdShowReport_Click()

DoCmd.OpenReport "Monthly_CrossTab", acViewPreview
DoCmd.Maximize  'Maximize the report window.

End Sub

Figure 1Figure 1

Figure 2Figure 2

Figure 3Figure 3

Figure 4Figure 4

Figure 5Figure 5
Question by:DeMyu
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    The "too Few parameters" error means just that.

    Somehow, the parameters are not being acknowledged.

    Try filtering the data before it gets to the crosstab query
    (possibly in the Children table).

    But lets back up a bit, ...does this even work with no parameters?

    Author Comment

    Yes, it works without parameters. My first step was to produce a crosstab query without filtering, then I added filters to it.

    I filter by month '01', '02', '03' etc    and year '11','12','13' etc for 2011, 2012, and 2013.

    I believe the error message is from the form that is responsible for providing the required parameters.

    Thank you for your support.

    Author Comment

    I really need help in resolving this problem and would appreciate support from the pros. Thank you.

    Author Comment

    Thank you!
    LVL 14

    Expert Comment

    Can you tell me the field type of PayWeek in PaymentHx? If PaymentHx is a query, what is the field type in the underlying table?

    LVL 14

    Expert Comment

    I don't often use parameter queries myself, but the "Too few parameters" error crops up for me when I have misspelled a field / table / query name in SQL. Perhaps that is the case.

    This is a complicated scenario because you have:
    - a crosstab query (dynamic field names to report)
    - based on Figure 1, the field names generated by the crosstab query have spaces in them, much care must be taken
    - Form references for parameters

    Could you possibly post a sample database with anonymized data?


    Author Comment


    PayWeek is type text with values such as 09/03/12 - 09/07/12.

    I have attached the sample database with anonymized data.

    Thank you.
    LVL 14

    Accepted Solution

    This had me scratching my head exactly as you did. I cannot identify any reason the code you have should generate the particular error.

    I was able to make a work-around. You saved a version of the parameter query without the form references as P_payment_hx_crosstab. The parameters are named PyMonth and PyYear.

    If you change the report's Open event's old line:
    Set rstReport = db.OpenRecordset(Me.Recordsource)
    Dim qdf As QueryDef
    Set qdf = db.QueryDefs("P_payment_hx_crosstab")
    qdf.Parameters("PyMonth") = [Forms]![frm_Show_monthly_Payment_Records]![CmbMonth]
    qdf.Parameters("PyYear") = [Forms]![frm_Show_monthly_Payment_Records]![CmdYear]
    Set rstReport = qdf.OpenRecordset()

    Open in new window

    then the code is able to assign the form values to the named parameters before the recordset is opened without manually typing in the values and without that dang error message.

    I'd love to hear if any one else can identify the reason for that error message.


    Author Closing Comment


    Thank you much. This solved a problem I had spent countless hours trying to solve.

    God bless you!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now