Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Default Criteria not working

    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.

    Thanks again,
    Pete Townsend
    PGT

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing in what you describe should prevent your application from working (see attachment). There must be another defined property, somewhere, that interferes.

    Could you be more precise in your description (type of controls used, etc), or perhaps provide an example?
    Attached Thumbnails Attached Thumbnails Def_Criteria_001.jpg  
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,

    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.
    Pete Townsend
    PGT

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    =Count(IIf([Forms]![fYearSelectDialog]![cYearSelect];1;Null))
    As for the tools I used to make and edit screen copies, the easiest (and cheapest) way I've found is:
    1. GadWin Printscreen 4.4 (free) for screen capture: Gadwin Systems, Inc. - Software for your business. DiagramStudio - program for create diagrams and flowcharts; GeForms - solution for design and filling forms, hundreds of free business forms.
    2. Excel for pasting and editing the snapshot and adding lines, frames, etc. (Word or Vision also work).
    3. Paint.Net (free) for the little corrections and for converting and saving the final image: Paint.NET - Free Software for Digital Photo Editing

    I'll be away for a few days so do not expect any more answers from me before the end of the week (probably Friday or Saturday).
    Have a nice day!

  5. #5
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    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.
    Pete T.
    PGT

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The semicolumn ( ; ) was because it is the list separator in the international settings of Windows on my computer.

    Your problem does not come from the reference to the form [fYearSelectDialog]. In your expression:
    Code:
    IIf([Forms]![fYearSelectDialog]![cYearSelect];1;Null)
    returns either 1 or Null while Count is a SQL function that returns the number of rows (records) issued by a query. What exactly do you try to achieve?
    Have a nice day!

  7. #7
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    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,
    Pete

    btw, using ScreenCapture32 for the images. Let me know if they are not readable or otherwise acceptable.
    Attached Thumbnails Attached Thumbnails SCwYear.gif   SCw2010.gif   SCwError.gif  
    PGT

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One solution (I don't like it but it works) would consist in creating a function in the form module, like this:
    Code:
    Private Function YearCount() As Integer
    
        Dim cnt As Integer
        
        With Me.Recordset
            .MoveFirst
            Do Until .EOF
                If !Year = Forms!fYearSelectDialog!cYearSelect Then cnt = cnt + 1
                .MoveNext
            Loop
        End With
        YearCount = cnt
        
    End Function
    Then use that function as the ControlSource of the control:
    Code:
    =YearCount()
    I'll try to investigate further any time soon.

    By the way, I was able to open the attached pictures without any problem.
    Attached Thumbnails Attached Thumbnails YearCount.jpg  
    Have a nice day!

  9. #9
    Join Date
    Aug 2006
    Posts
    126
    Thanks, if all else fails I may try that. However, I'm from a stubborn heritage and, like you, want to know why something that seems so intuitive, doesn't work.
    PGT

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pete,

    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:
    Code:
    SELECT IIf([Year]=Forms!fYearSelectDialog!cYearSelect,1,Null) AS YearCnt, Table1.Year
    FROM Table1;
    Then the ControlSource of the textbox that must display the total of matching years becomes
    Code:
    :=Count([YearCnt])
    And it works.
    Have a nice day!

  11. #11
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    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.
    Pete
    PGT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •