Hello. I have a question and I would greatly appreciate any help that you could give me.

Ok, I am trying to build a report that will draw from two separate control sources. Let me explain. I am trying to build a report to calculate the personnel turnover ratio for my organization. I have already built a report that is sorted/organized according to several fields (sub-organization identifier code, type of accession/attrition, and type of occupation). This report pulls from a primary control source and counts the total number of accessions and attritions (and a variety of other information associated with those personnel). What I would like to do now is to add a new text box to the first grouping (the sub-organization identifier code grouping) and have the report pull from a second control source (or said another way, a second query) and count the total number personnel in the matching the sub-organization identifier code. Whereas the first control source/query contains only information for those personnel that were either hired or separated from the organization during a given period of time, the second control source/query contains only information for those personnel that are active during the organization during a given time.

Now, I *think* I created an expression that could work, however I cannot input this expression into the expression builder because it is entirely too long (>8,000 characters) and exceeds the box limit.

=(IIf([SUB_ORG_CODE_NUM]="0100000000",(DCount("*","[Query-Onboards]","[SUB_ORG_CODE_NUM]='0100000000'")),Null)) &
(IIf([SUB_ORG_CODE_NUM]="0300000000",(DCount("*","[Query-Onboards]","[SUB_ORG_CODE_NUM]='0300000000'")),Null)) &
(IIf([SUB_ORG_CODE_NUM]="0400000000",(DCount("*","[Query-Onboards]","[SUB_ORG_CODE_NUM]='0400000000'")),Null)) &
...

Not knowing how to work around this, I consulted ‘the Internet’ and learned (after much reading) that a macro *might* be able to help me and I came up with the following macro (FYI – Text602 is the text box that I want to have this onboard personnel number appear in; also this is truncated.):

Public Sub Text602_Enter()
If [SUB_ORG_CODE_NUM] = "0100000000" Then
[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='0100000000'")
ElseIf [SUB_ORG_CODE_NUM] = "0300000000" Then
[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='0300000000'")
ElseIf [SUB_ORG_CODE_NUM] = "0400000000" Then
[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='0400000000'")
...
End If
End Sub

This code *seems* to work, however, the onboard numbers (in text box 602) do not automatically appear in the print preview. But, if you click on the box (on each line) the numbers automatically appear.
Can anyone tell me:
One, is it possible to bypass the character limit of the expression builder box?
Two, why does the macro (above) not work?
Three, do you know how I can fix the macro (above)?

Thank you very much.

Are you testing for every [SUB_ORG_CODE_NUM]= value? That would be unnecessary work if the value is on the current form.

You would replace all the tests with just one line..

me.[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='" & me.[SUB_ORG_CODE_NUM & "'")

This looks like it should go in either the form_current event procedure and/or the afterupdate event procedure of the [SUB_ORG_CODE_NUM] control if there is manual input in this box.

Peter46,

Thanks for the response. I have entered your expression into the expression builder and it is currently returning a '0' (zero) for all the number of onboard personnel (which, unfortunately, is not correct).

me.[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='" & me.[SUB_ORG_CODE_NUM & "'")

More specifically, I didn't entre your text verbatim. I actually cut off the text before the first '=' because the text box would not accept it. Also, I thought that the 'me.' could be a part of the problem, so I modified the expression to the following (inputing the name of the main query, in place of the me.), but it still returns a zero.

=DCount("*","[Query-Onboards]","[SUB_ORG_CODE_NUM] '" = & '[Query-AccessAttrit]![SUB_ORG_CODE_NUM]' & "'")

Since the expression is returning a value instead of an error code, I think I am on the right path, but I am not certain what is wrong. Any other thoughts?

Again, thanks for all your help/comments.

IPX

My response was modifying your code not the expression in the textbox controlsource. The 'Me.' syantax is only usable in code.

Without knowing more about the app I can't be sure, but to repeat what I said before.....

"This looks like it should go in either the form_current event procedure and/or the afterupdate event procedure of the [SUB_ORG_CODE_NUM] control if there is manual input in this box."

Sorry about my confusion. I have only recently learned Access and know next to nothing about VBA code. I put your suggested edit into the code builder as follows (I added the bracket):

Option Compare Database

Private Sub Text602_Enter()

Me.[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='" & me.[SUB_ORG_CODE_NUM] & "'")

End Sub

It does the exact same thing my original code did, albeit in significantly less characters, so that is good. However, the numbers in that textbox only appear (1) in the 'Report View' of the Report (not in 'Print Preview', and (2) only after you click the actual textbox. I should also mention that when I click on the textbox, the same value appears in all instances of the texbox, regardless of [SUB_ORG_CODE_NUM].

I have tried to change the Procedure, as you suggested, but when I change it to afterupdate, the value no longer shows up (even when you click the textbox). Weird. Nice to know the code does work, but there has to be a way to make the code "auto-populate" the field for each instance of [SUB_ORG_CODE_NUM] without clicking. (If I had hair, I would be close to pulling it out.)

This post has been edited by IPX: Feb 28 2012, 01:27 PM

Very Sorry - for some reason not now apparent I assumed we were dealing with a form, not a report.

A report in preview mode is not interactive so you cannot use click events or enter and exit events because they don't happen.

The code needs to go into the Format or Print event procedure for the section containing the text602 control.

To be completely unambiguous about what is going on I suggest you change the name of the textbox control containing SUB_ORG_CODE_NUM to txt .SUB_ORG_CODE_NUM
Then use...

Me.[Text602] = DCount("*", "[Query-Onboards]", "[SUB_ORG_CODE_NUM]='" & me!txt[SUB_ORG_CODE_NUM & "'")

Assuming that text602 is in the Detail section of the report, right click the Detail bar (in design view) and choose Properties.

In the On Format property choose Event Procedure from the drop down list and then click the build button (...) .

You should now be in the Detail_format procedure and you can place the code there.

That should give you the values you want.