In Microsoft Access 2010, how do I reference a bound control in a report?

Question

I am creating a report to display data on clients with several subreports to show different demographic data further broken by fiscal quarter. For example, my subreport on the primary language of the clients has a combobox control to create the rows in the detail section; the control is bound to my table of language choices(I'm not sure if this is relevant but I used an AutoNumber for the primary key of language choices instead of the name of the language itself as the key. I can change that though if needed). For the columns I created a label for each fiscal quarter. What I'm trying to do is for each language choice in this bound control, I need it to see which clients speak that language, see when they were first served to break them down into the appropriate fiscal quarter, and display the count in the appropriate boxes.

In other subreports like gender of new clients broken down by fiscal quarter it was easy. There's only M/F so there's no need to have it expandable so I just created the two rows with labels/rectangles and used some VBA to reference a query that gathered my client data. The query looks to see when they were first served and I wrote a function to return which Fiscal Quarter they belong to (Caution: amateur coding ahead =D )

Public Function FiscalQuarter(InputDate As Date) As Integer

Dim ReportMonth As Integer
Dim ReportDay As Integer
Dim ReportYear As Integer
Dim FiscalYear As Integer


'''!!! Break up Current Date !!!'''
ReportMonth = Month(InputDate)
ReportDay = Day(InputDate)
ReportYear = Year(InputDate)

' Quarter 1
    If ReportMonth = "9" Then
        FiscalQuarter = 1
        FiscalYear = ReportYear + 1
    End If
    If ReportMonth = "10" Then
        FiscalQuarter = 1
        FiscalYear = ReportYear + 1
    End If
    If ReportMonth = "11" Then
        FiscalQuarter = 1
        FiscalYear = ReportYear + 1
    End If

' Quarter 2
    If ReportMonth = "12" Then
        FiscalQuarter = 2
        FiscalYear = ReportYear + 1
    End If
    If ReportMonth = "1" Then
        FiscalQuarter = 2
        FiscalYear = ReportYear
    End If
    If ReportMonth = "2" Then
        FiscalQuarter = 2
        FiscalYear = ReportYear
    End If

' Quarter 3
    If ReportMonth = "3" Then
        FiscalQuarter = 3
        FiscalYear = ReportYear
    End If
    If ReportMonth = "4" Then
        FiscalQuarter = 3
        FiscalYear = ReportYear
    End If
    If ReportMonth = "5" Then
        FiscalQuarter = 3
        FiscalYear = ReportYear
    End If

' Quarter 4
    If ReportMonth = "6" Then
        FiscalQuarter = 4
        FiscalYear = ReportYear
    End If
    If ReportMonth = "7" Then
        FiscalQuarter = 4
        FiscalYear = ReportYear
    End If
    If ReportMonth = "8" Then
        FiscalQuarter = 4
        FiscalYear = ReportYear
    End If

End Function

Then in the ClientGender report I used code like the following:

MaleFirstQ = DCount("MinOfEventDate", "qryQRChildDemographics", "FiscalQuarter([MinOfEventDate])=1 And [Gender]='M'")
MaleFirst.Caption = MaleFirstQ

For each gender/quarter box I have a snip of code resembling the above to assign to the caption.

So my problem is that I am having trouble referencing the values in the bound control on the report to use in my Fiscal Quarter breakdown. I tried to use a Dcount similar to the one I already used except I was trying to use a reference to the bound control as a criteria and I kept getting different runtime errors saying that I made a reference to a control or property that doesn't exist.

I'll admit that I'm somewhat of a novice and I enjoy figuring things out on my own by playing around with it, but I am completely stuck. I have been slowly poring through the Microsoft Access 2010 Bible, Microsoft Access 2010 In-Depth, and the Wrox Microsoft Access 2010 Programmer's Reference(Just started this one) books but so far I haven't found what I think I need. I have a feeling that my approach to this task is completely wrong so if anyone could point me in the right direction as far as what I'm overlooking and what tools to use I would greatly appreciate it!

---

I tried several different ways, but they are all similar to this with slight changes in the second criteria:

LanguageFirst.Caption = DCount("PrimaryLanguage", "qryQRChildDemographics", "FiscalQuarter([MinOfEventDate])=1 AND Me!LanguageField")

Produces Run-time error 2471 It doesn't like the Me!LanguageField.

I've also tried this as the second criteria:

[PrimaryLanguage]=Me.Controls!LanguageField

I figured since I can use a function as a criteria that I should be able to use the value of a field too, but I'm starting to think that Dcount might not even be the right thing to use here.

Answer 1

I don't really understand what your question is as you've thrown out so much information I can't make heads or tails out of it, but I have these two comments:

1. combo boxes don't belong on reports. They are UI objects, for editing, and a report can't be edited (though I guess with A2010 there is the possibility of editing data in a report, I wouldn't recommend such a thing). Instead, you should do the join with the table that's the basis of the combo box in the recordsource of the subreport.

2. you're using labels when you should be using textboxes. When you code this:

   LanguageFirst.Caption = DCount("PrimaryLanguage", "qryQRChildDemographics", "FiscalQuarter([MinOfEventDate])=1 AND Me!LanguageField")

...you're assigning data to a caption. Instead, delete the caption and replace it with a textbox that has as its ControlSource the DCount() formula. You'll also need to adjust the WHERE clause, as you can't have Me!LanguageField in a DCount() (I don't see how it could possibly work in any context, actually).

If you do the above (i.e., replacing the labels whose captions you are editing with textboxes), you lose the problem of having to code the stuff in VBA and getting the code in the right events and so forth.

Answer 2

Try for the third parameter:

"FiscalQuarter([MinOfEventDate])=1 AND [PrimaryLanguage] = " & Me!LanguageField.Value

You should be constructing a string, but if you put Me!LanguageField inside the quotes then it's treated like text.

Answer 3

This isn't an answer to your question, but it doesn't work so well as a comment. Since you are just starting with VBA, see if you prefer this approach for your FiscalQuarter function.

Public Function FiscalQuarter(InputDate As Date) As Integer

    Select Case Month(InputDate)
      Case 9 To 11 ' Quarter 1
        FiscalQuarter = 1
      Case 1, 2, 12 ' Quarter 2
        FiscalQuarter = 2
      Case 3 To 5 ' Quarter 3
        FiscalQuarter = 3
      Case 6 To 8 ' Quarter 4
        FiscalQuarter = 4
    End Select

End Function