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

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
Who is Participating?
pteranodon72Connect With a Mentor Commented:
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.

Jeffrey CoachmanMIS LiasonCommented:
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?
DeMyuAuthor Commented:
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

DeMyuAuthor Commented:
I really need help in resolving this problem and would appreciate support from the pros. Thank you.
DeMyuAuthor Commented:
Thank you!
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?

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?

DeMyuAuthor Commented:

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.
DeMyuAuthor Commented:

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

God bless you!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.