Results 1 to 4 of 4
  1. #1
    Persist is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32

    The number of records in a query

    * Is there a visual basic function that identifies the number of records in a query (MyQuery)?

    I plan to create a form (MyForm) that presents each record of MyQuery and loop through each record of the form

    nRecords = [this is what I do not know]
    For counter = 1 to nRecords
    Statements
    Next counter

    Alternatively I could loop using a while statement - if there was an End of records function

    If I have to use a recordset then I will have further questions about how to do this.




    Ms Access 2007 version 12





  2. #2
    Join Date
    Jul 2010
    Location
    GA.
    Posts
    23

    The number of records in a query

    Hello Persist
    One thing you could do is to place a unbound text box on your form
    and place this code in it. Put the Name of your Primary Key record name were the Record ID is. This will then show like ( Record 1 of 28 )

    ="Record" & " " & [CurrentRecord] & " of " & Count([Record ID])

    Or if all you want is the record count
    =Count([Record ID])
    This will give just the record count Like ( 28 )

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi persist,

    each recordset has a BOF (begin of file) and EOF (end of file) property you can use to loop through a recordset:

    example (assuming you opened a recordset rstMyRecordset)

    With rstMyRecordset
    If not(.BOF and .EOF) then 'if Begin Of File an End of file are both true, you have an empty recordset
    .movefirst
    while not .eof
    'do your thing here
    .movenext
    wend
    end if
    .close
    end with

  4. #4
    Persist is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32

    my solution

    Thanks for your replies

    Here is how I solved it the night I posted the quesiotn

    I created a form based on the query I wanted to operate on.


    MyForm = "MyFormBasedOnMyQuery"
    ' Fields in MyQuery: aCompany, aDate, CashIn

    DoCmd.OpenForm MyForm

    DoCmd.GoToRecord acDataForm, MyForm, acFirst ' Go to first record

    With Forms.MyFormBasedOnMyQuery
    Do While IsDate(.aDate)
    My processing
    DoCmd.GoToRecord acDataForm, MyForm, acNext ' Go to next record
    Loop ‘ Do
    End With

    IsDate was one of several tests I could have used - including Not IsNull

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Limit number of records in report by group
    By Dr Ennui in forum Reports
    Replies: 0
    Last Post: 06-22-2010, 12:36 AM
  2. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM
  3. Replies: 5
    Last Post: 10-08-2009, 05:15 AM
  4. Restart auto number after deleting records
    By P5C768 in forum Database Design
    Replies: 1
    Last Post: 09-11-2009, 02:07 PM
  5. Replies: 1
    Last Post: 01-31-2009, 10:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums