When DB opens an Unbound control is populated with a 4 digit 'year to be worked on'. That window is then minimized or hidden so the value is available for later use. I use that value as a criteria in several queries as [Forms]![fYearSelectDialog]![cYearSelect] and they work fine.
In a subform I want to use that year as the default value in a control. And I want to do a count of entries that have that year.
In earlier versions my default value was =Year(Now()) and it worked. When I change the default value spec to =[Forms]![fYearSelectDialog]![cYearSelect] it doesn't work. When I change the Validation Rule for that control from >=Year(Now()-2 And <=Year(Now()+1 to >=[Forms...cYearSelect]-2 etc. it doesn't work. Likewise at the bottom of the subform the "Total" control works if I use =Count(IIf([Year]=(Year(Now()-1)),1,Null)) but not if I replace the Year(Now() with the ...cYearSelect].
I suspect the problem might have to do with data typing, but can't figure out what to do about it.
I regenerated the default value field of the subform using expression-builder and that now works. The formula looks the same but sometimes regenerating them clears out a piece of garbage.
Next I used expression-builder to regenerate the source for the count control at the bottom of the subform. Expression-builder created =Count(IIf([Forms]![fYearSelectDialog]![cYearSelect],1,Null)). This still produces #Error in the control. The expression worked fine when it was =Count(IIF(Year(Now()),1,Null)).
To answer your questions, all 3 controls are text boxes. The only differences I see is the cYearSelect and Count controls have Decimal set to Auto, the subform data control has it set to 0; but that control is now working.
I've not looked at the Validation rule until I get the Count control working.
As an aside- what do you use to capture the screen/window images? I am just back up from a fried HD and have to re-install many of those helper apps.
Hope you were on vacation and enjoyed the break. Thanks for the refs. I've been using GIMP for photos, but its way too complicated for small stuff. I'll try your apps.
On the db front. I tried your ";" instead of "," but that definitely didn't work.
If I change the source for the Total control at the bottom of the subform to:
=Count(IIf([Year]=2010,1,Null)) it works fine.
If I insert (Forms!fYearSelectDialog!cYearSelect) for 2010 I get the #Error.
The value of cYearSelect is 2010; and it works when used as a query criteria.
I'm attaching 3 images from the actual form.
The first is the sub-form with the Properties for the control-being-counted shown.
The 2nd is the subform with the Properties for the Count-control shown.
In this case the count is based on an explicit "2010". Note the correct count is 2.
The 3rd is the subform with the Properties for the Count-control when I try to use the reference to the cYearSelect control. Note the #Error.
Also in the 1st image, I've shown that the cYearSelect is working in the Default Value property. However, In the Validation Rule property, putting cYearSelect in place of Year(Now()) produces an error.
Thanks for your help and patience,
btw, using ScreenCapture32 for the images. Let me know if they are not readable or otherwise acceptable.
One solution (I don't like it but it works) would consist in creating a function in the form module, like this:
Private Function YearCount() As Integer
Dim cnt As Integer
Do Until .EOF
If !Year = Forms!fYearSelectDialog!cYearSelect Then cnt = cnt + 1
YearCount = cnt
Then use that function as the ControlSource of the control:
I'll try to investigate further any time soon.
By the way, I was able to open the attached pictures without any problem.
I've tried every expression I could think of but to no avail (until now).
However a solution that does not need the VBA function would consist in modifying the RecordSource of the subform and adding a computed column to it. In my example, the original RecordSource of the subform was a table (Table1), it now becomes a query:
SELECT IIf([Year]=Forms!fYearSelectDialog!cYearSelect,1,Null) AS YearCnt, Table1.Year
Then the ControlSource of the textbox that must display the total of matching years becomes
Thanks, this ends the discussion unless you suddenly get a brainstorm.
In the interim I'd tried creating an invisible control directly in the sub-form with the source equal to the cSelectYear (it did pick up the correct value), and then referencing that. That didn't work either.
Once you suggested going back to the underlying query, it reminded me that in two other places in this mdb, I've had to do that for other reasons. Since an underlying query was already in use, I just added the computed column, and the ref to it in the Count source.
Thanks again for your patience and expertise.