none
recordset clone records with certain criteria RRS feed

  • Question

  • I have a sub form and to  loop trough the records  I have a reordset clone

    Set rst = Me.QFacturenrapp.Form.Recordset.Clone

    the user can choose some of the records he needs and I want to know how many records that are

     

    can I set criteria in the set Recordset.clone  

    or

    is there a count function that I can use to see how many records meet the criteria ?

    Sunday, June 4, 2017 8:59 AM

Answers

  • I just want to know how many of the records in the subform a user has chosen (1 field is set to 'true)

    Why bother with the RecordsetClone.  You can address the data directly by calling the DCount function.

    Let's assume as a simple example that the parent form is based on a Customers table and the subform on an Orders table.  If we assume the Orders table has a Boolean (Yes/No) column named Selected then code in the subform's module could be something like this:

        Dim strCriteria As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And Selected = True"

        ' ensure current subform record is saved
        Me.Dirty = False
        ' show count of selected records in a text box
        ' named txtSelectedCount in the subform's footer
        Me.txtSelectedCount = DCount("*","Orders", strCriteria)
        
    However, you should be able to get the same result by calling the DCount function in the control's ControlSource property, or even more simply by setting the ControlSource property to:

        = Sum(IIf(Selected,1,0))



    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, June 4, 2017 5:17 PM Typo corrected.
    • Marked as answer by tekoko10 Sunday, June 4, 2017 5:31 PM
    Sunday, June 4, 2017 5:15 PM

All replies

  • Hi, Have you tried something like? Me.QFacturenapp.Form.RecordCount
    Sunday, June 4, 2017 12:53 PM
  • yes, but this counts all the records

    . I just want to know how many of the records in the subform a user has chosen (1 field is set to 'true)

    Sunday, June 4, 2017 1:18 PM
  • Hi,

    How did the user filter the subform? If they didn't, then you can try using the Filter property. For example:

    Set rs1 = Me.QFaturenapp.Form.RecordsetClone

    rs1.Filter = "FieldName=True"

    Set rs2 = rs1.OpenRecordset

    MyCount = rs2.RecordCount

    Hope it helps...

    Sunday, June 4, 2017 2:34 PM
  • I do not why , but mycount is allways 1 , although there are more records chosen


            Set rst = Me.QFacturenrapp.Form.Recordset.Clone
        
     
      
      rst.Filter = "Ftbetaald =True "
     
    Set rst2 = rst.OpenRecordset
    Mycount = rst2.RecordCount
     

    Sunday, June 4, 2017 4:38 PM
  • There is a difference between Recordset.Clone and RecordsetClone.

    -Tom. Microsoft Access MVP

    Sunday, June 4, 2017 5:08 PM
  • Hi, You might try also adding one more step:

    rst2.MoveLast
    MyCount=rst2.RecordCount


    • Edited by .theDBguy Sunday, June 4, 2017 7:47 PM
    Sunday, June 4, 2017 5:12 PM
  • I just want to know how many of the records in the subform a user has chosen (1 field is set to 'true)

    Why bother with the RecordsetClone.  You can address the data directly by calling the DCount function.

    Let's assume as a simple example that the parent form is based on a Customers table and the subform on an Orders table.  If we assume the Orders table has a Boolean (Yes/No) column named Selected then code in the subform's module could be something like this:

        Dim strCriteria As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And Selected = True"

        ' ensure current subform record is saved
        Me.Dirty = False
        ' show count of selected records in a text box
        ' named txtSelectedCount in the subform's footer
        Me.txtSelectedCount = DCount("*","Orders", strCriteria)
        
    However, you should be able to get the same result by calling the DCount function in the control's ControlSource property, or even more simply by setting the ControlSource property to:

        = Sum(IIf(Selected,1,0))



    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, June 4, 2017 5:17 PM Typo corrected.
    • Marked as answer by tekoko10 Sunday, June 4, 2017 5:31 PM
    Sunday, June 4, 2017 5:15 PM
  • #omg so easy Mr Sheridan ! THANKS
    Sunday, June 4, 2017 5:44 PM
  • Hi,

    Ken is the best! Glad to hear you got it sorted out. Good luck with your project.


    • Edited by .theDBguy Sunday, June 4, 2017 7:48 PM
    Sunday, June 4, 2017 7:48 PM