Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Counter on filter results

    i have a form with 5 buttons, when the buttons are pressed it filters the records based on time intervals 12-2, 2-4 etc the table has the data already inputted based on what time the callback should be carried out, i.e 12-2pm etc, what i would like is rather than having the record count at the bottom of the form show how many records have been filtered, to have 5 counters next to the 5 buttons showing how many records are left in the filter in real time. the record are removed from the filter result if the field "owner" has been occupied by a 2 character initial. so as people are updating the records to show they have take ownership the counter decreases in realtime.
    thanks
    i've been told to use a Dcount previously but im a little lost on how this would work or to do it.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can use dcount or you could also write a simple function to return the values. Your function might look like this.

    Function retRecsRemaining() as variant
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "<paste your sql statement here> ie...unmatched recs in tablename to query filter is based on (save your filter as a query)."
    rs.open strsql, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    retRecsRemaining = null
    else
    retRecsRemaining = rs.recordcount
    end if
    rs.close
    set rs = nothing
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    Im a bit lost on the sql statemnt? which one should that be as my query and filter is based on every record that has a time 10-12pm etc will be returned after the button is pressed on the form.

  4. #4
    Join Date
    Sep 2008
    Posts
    101
    im still needing help on this.....my query/filter brings back 6 results so i would like a little box on my form showing 6 and when my field for "owner" is not null anymore the little box would show this in real time and decreases accordingly.
    thanks

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In your unbound textbox field (in the ControlSource line),

    To do a count of records returned to your form, you would put in =Count(*)

    To do a count of the records in a listbox on a form, you would put in =Forms!MyFormName!MyListboxName.Listcount -1

    Does that help?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Sep 2008
    Posts
    101
    i need it to count the filter results before acutally pressing the filter button?

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like you need a form timer event to have the code run without interaction from the user.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by gmc2k2
    i need it to count the filter results before acutally pressing the filter button?
    "In your unbound textbox field (in the ControlSource line),
    To do a count of records returned to your form, you would put in =Count(*)"

    This will count the records before you filter the results. I guess I'm still not following you. Have you tried the above expression?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Sep 2008
    Posts
    101
    sorry pkstormy, what i mean is, i have 5 buttons which are named 10-12,12-2,2-4,4-6 and 6-8. once pressed it filters the records by preffered callback time, i.e 12-2 and it brings all the callback between that time starting from newest to oldest by date.
    what i need is a counter on the side of each button to show how many records are there in those filter results without actually pressing the button so i could be in the 12-2 filter results but can see the counter on the side of the 6-8 button showing how many we have for later without having to press the 6-8 button.
    this of course would show home many results that are remaining in each time segment in real time. so if i go into the 12-2 results and take ownership it will remove itself from the filter results and counter accordingly.
    hope that helps
    thanks

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think I've said this before, but you just need to have 5 DCounts happening that populate 5 text boxes (counters)... called on a Form Timer to keep the values updated... perhaps once every 15 seconds or so.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Sep 2008
    Posts
    101
    cheers startrekker how would that look in the control source of the text box for the 5 dcounts

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Control Source? It would be blank.

    Your form's On Timer even would have something like this:

    Me.txtCount1 = DCount("field", "table/query", "<criteria1>")
    Me.txtCount2 = DCount("field", "table/query", "<criteria2>")
    Me.txtCount3 = DCount("field", "table/query", "<criteria3>")
    Me.txtCount4 = DCount("field", "table/query", "<criteria4>")
    Me.txtCount5 = DCount("field", "table/query", "<criteria5>")


    Of course, you'd have to substitute out "field" for a field in the table that always has a value in it, "table/query" for the table or query name that data is being counted in and "criteria" for the specific criteria that you want (callback time between two dates and owner is null, that kind of thing).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Sep 2008
    Posts
    101
    As below....
    Last edited by gmc2k2; 10-20-08 at 12:28.

  14. #14
    Join Date
    Sep 2008
    Posts
    101
    how would it work as my criteria is filtered on every record that has a date that <=Date() and the field "preferred contact time" contains a 10-12 12-2 2-4 4-6 and 6-8 for the different buttons so what would the above code be written like given that my txt box are named the above:
    txtCount1
    txtCount2
    txtCount3
    txtCount4
    txtCount5
    Last edited by gmc2k2; 10-20-08 at 12:28.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You haven't provided enough information: such as the table name or query name that holds the data that you apply the criteria to. You haven't given the name of the field that holds the date you're referring to. You haven't given the data type of the [preferred contact time] field.

    It's much better for you to apply yourself and learn how to use the advice you have been given. If the answer is just handed to you, then you won't learn much.

    TRY!! If you try to apply what you have been given, it actually makes it much easier for us to help you. It doesn't matter if you don't succeed. If you paste here what some of your attempts are, we can tell where you are going wrong and offer further, more specific advice. You'll learn more too
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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