Results 1 to 9 of 9
  1. #1
    Petran is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    South Africa
    Posts
    5

    Using Forms 'Record Selectors' ='Yes' does not return total number of records on the form

    I have been struggling with this problem for a long time, hope someone can assist.


    I am using MS Access 2016, connected to an SQL database via ODBC.

    On my form, linked to a table in the SQL database via a query, I set the 'Record Selectors' to 'Yes'. This should return the number of records in the Record Selector indicator at the bottom of the form. (refer to Figure 1 on the attachment) I have created the code (Figure 2 in the attachment) in the On Current event of the form, this produced the 'Records 1 of 2403' information on the form.

    My problem is that by using 'Record Selectors' set to 'Yes', the information should be available by default. i.e. I should not have to create my own code to do it.

    If I click on the 'Goto Last Record' in the 'Record Selector' (>|), it does show the correct information.

    This is important because my users are used to getting the record number information from the 'Record Selector' portion of the screen, i.e. before I converted the Back End database to SQL it worked 100% every time. If I now create my own 'Record Selector' info, it does not show in the 'Datasheet' view.

    I really hope that someone can help me here, I have searched the web for an answer without success. . . .
    Attached Files Attached Files

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If I now create my own 'Record Selector' info, it does not show in the 'Datasheet' view.
    Perhaps you could put your datasheet form on a "Main" form and show your own 'Record Selector' info on the main form
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Petran is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    South Africa
    Posts
    5
    Thanks for the reply, Bob Fitz. Yes I can create my own 'Record Selector' and I have, and it works fine. But, the users are the ones that have the last say. I can understand their logic and it does work in MDB lined databases.
    Another thing, if you create you own 'Record Selector', and you open the form in Datasheet view (and this is what the users do), the 'Record Selector' is not repeared on the Datahseet.

    Lastly, if it did work in the (old) MDB envorinment, shurely there is some setting or fix somewhere that can rectify this?

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I'm a bit confused
    Yes I can create my own 'Record Selector' and I have, and it works fine.
    Then what is the problem
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Petran is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    South Africa
    Posts
    5
    Thanks again Bob Fitz, the problem is
    1) I need to use the Form's Record Selector, because that is what the user want and
    2) If i create my own Record Selector, the info is not displayed when the user view the (selected) record in Datasheet view. . .

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It seems to me you are confusing items.
    Record selectors are the 'buttons' on the left of each record.
    Navigation buttons are at the bottom of the form next to the record counter.
    Both can be shown in datasheet view.

    The record count will say 1 of 25 (or whatever) where counting the records doesn't prevent the form being loaded quickly
    However if there are a lot of records it will just say 1 ...until you go to the last record (triggering a count) and back again to the start.
    This allows the form to load quickly improving user experience

    Now if that behaviour wasn't done, loading the form could take ages!
    For example, I have a linked SQL table of 1.4 million records. It takes about 10 seconds to move to the last record using the navigation buttons & trigger a count.
    If Access did a MoveLast, get count them MoveFirst, your users would complain VERY LOUDLY

    So if your users need this info, you need to display it using a textbox (or button as you have now)

    Your screenshot shows what looks like an added button with a record count.
    That won't show in datasheet view as that view is intended just to display data
    Suggest you use a main form with a datasheet sub form. Place your record count button (or textbox) on the main form.

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Petran is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    South Africa
    Posts
    5
    Dear Ridders52

    My humble apologies, i was in fact referring to the 'Navigation Buttons', specifically the portion that indicates 1 of 25.
    The button I created is in fact a count of the records obtained from (RST.MoveLast, and RST.MoveFirst).
    I need this to show (as it does when DAO (MDB) is used), at the bottom of the screen. It does not and this is the problem.
    The user will have a max of 1200 records, so they are used to the few seconds wait.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problems.
    In that case, I suggest you do one of the following.
    Method 2 is in my view better

    1. Move your recordset.clone code to Form load but ignore the caption line
    This should force the record counter to display as you want but it will cause a short delay.
    Do bear in mind this will get slower over time as additional records are added

    2. Use a main form with datasheet subform
    In the main form footer, use an unbound textbox and call it e.g. TotalRecords.

    In the form load event, enter
    Code:
    Me.TotalRecords =DCount("*","YourQueryName")
    That may allow your form to load instantly with the count appearing after a very short delay.
    If it 'hangs', there s a simple solution involving a form timer event...but try that first.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Petran is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    South Africa
    Posts
    5
    Thank you, Ridders52 and Bob Fitz for your contribition to solving this issue. In the end I copied an exisitng form and created the required form from this copy. All worked well. Appreciated again. . . . .

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

Similar Threads

  1. Cannot Use Record Selectors in Filtered Form
    By NadiaVKhan in forum Forms
    Replies: 12
    Last Post: 03-31-2017, 12:59 PM
  2. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  3. Replies: 4
    Last Post: 10-26-2013, 02:04 PM
  4. Replies: 1
    Last Post: 01-24-2013, 05:50 PM
  5. Replies: 0
    Last Post: 03-06-2011, 04:10 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