Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2014
    Location
    In a deep fog...
    Posts
    11

    Unanswered: Run Time Error - acCmdRecordsGoToLast

    Hi there,

    I have a form that loads only the records that have been created today. It is a continuous form and by the end of the day can contain a large number of records. When the form loads, I want the focus to go to the last record. Originally, I was just using DoCmd.RunCommand acCmdRecordsGoToLast, but whenever someone opened this form before any records were entered there was a Run Time Error saying that it couldn't go to the last record. I tried the following code, but now it won't go to the last record. The Run Time Error no longer pops up when there aren't any records, but I can't get the focus to the last record. I've tried using ">=" and I've tried switching the order, i.e. date = LstDate.

    I'm using Access 2013.

    Appreciate your help!!!

    'Private Sub Form_Load()

    'Dim LstDate As Date

    'LstDate = DLast("Date", "Movement Stats Table")

    'If LstDate = date Then
    ' DoCmd.RunCommand acCmdRecordsGoToLast
    'End If

    'End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Put an error handler around the code and take appropriate action

    You can find if there are no rows in a recordset if its eof AND bof properties are true
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Location
    In a deep fog...
    Posts
    11

    Thanks

    healdem,

    Thanks for pointing me in the right direction. I have changed the code to:

    'Private Sub Form_Load()

    'If Me.Recordset.BOF = True And Me.Recordset.EOF = True Then
    'no records
    'Else
    ' DoCmd.RunCommand acCmdRecordsGoToLast
    'End If

    'End Sub

    This seems to work. I haven't created any error handling in this sub. Is there a better way to do this?

    btw, this Form is opened using a query.

    Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    im guessign the ' before each line is an artificat of how you posted the code on this forum (better off to encapsulate such lines with [ c o d e ] & [ / c o d e ] tags (but remove the spaces so [ / c o d e ] becomes [/code]

    Code:
    Private Sub Form_Load()
    
    If Me.Recordset.BOF = True And Me.Recordset.EOF = True Then
      'no records
    Else
      DoCmd.RunCommand acCmdRecordsGoToLast
    End If
    End Sub
    ..otherwise all you've doenn is comment out all the statements, meaning the code will not run, ever
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2014
    Location
    In a deep fog...
    Posts
    11
    Ah, I was wondering how to do that. Thanks, I'll try to follow that next time.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad that you got this working with the help of healdem. But to address a couple of things in your original code:

    Firstly, your Table name is Movement Stats Table, containing Spaces. Best practice, in Access, is to never use
    Special Characters
    , which includes Spaces, in the names of anything. Doing so can cause the developer to spend extra time notifying Access that they are, in fact, legitimate names, and can cause errors that are very difficult to debug! A far better approach would be to use a naming protocol such a 'CamelStructure,' i.e. instead of Movement Stats Table, use MovementStatsTable, or using the one Special Character that is allowed in names, the UnderScore character: Movement_Stats_Table.

    Secondly, your Table named "Movement Stats Table" contains a Field named Date. 'Date' is a Reserved Word, in MS Access, and should never be used for the name of anything! Doing so confuses the Access Gnomes and frequently triggers errors...errors that can be very hard to debug! So, first off, you need to change that Field name to something else.

    Lastly, you used the DLast() function, assuming, I'm sure, that it actually returns the 'last date' in that Field.' Sadly, DLast() is another of those Access Functions that do not perform in the manner that their name would suggest! Instead of actually returning the 'last date,' it returns a random Value for the Field! In this type of situation you'd be better off using DMax() against the Date Field. For a complete explanation of theDLast() function, enter it in the Access Help search box.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2014
    Location
    In a deep fog...
    Posts
    11
    Thanks Linq. I knew I that I needed to change the "Date" field. All my new stuff avoids that, but now that this database is up and running, it's hard to make all the changes without rewriting all the code. I tried yesterday and had to revert to a backup database as the changes made the database no longer work. I've read that there is a better convention for naming tables, etc. tbl for tables, frm for forms. I'm incorporating that into all the new stuff I'm doing.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    theres numerous conventions...
    but the very minimum is don't use reserved words or symbols

    after that the choice is largely down to style and preference
    use CamelCase (AnOverlyLongItem) or all lowercase with underscore (an_overly_long_item)
    if you want to use reverse Polish notation inside our code feel free to do so. BUT I'd strongly suggest NOT doing so within table & column nam,es (if its inside the SQL domain use the relevent SQL standards

    use abbreviation where relevent and obvious, but not just for the sake of it. be consistent accross the organisation (so if one group use CamelCase, all use CamelCase but don't mix & match

    here's a few:-
    use plurals for table names, singular for columns, don't repeat the table name for the columns inside
    that table, but by all means refer tot he parent table if that column is a FK to another table
    eg Don't use OrderNo inside the orders table, but do use it inside the order line item table.

    if its easy to read your tables and columns then you are a long way there. ideally most of the columns & tables should be immediately obvious for anybody (and definitiely for anybody with a direct understanding of what they design is meant to implement (whether thats within the business or the IT function)

    As regards re visitng existing code to fix non standard issues, its a good call to NOT do so. usually its too late, its a waste fo time and effort... if the code is working then leave well alone. you just dont' know what can of worms you are opening up. Access dos a pretty good job of translating but sometimes it gets its knickers in a twist.. so be aware if odd things are happening its possible you've got corruption exacerbated by dodgy naming
    I'd rather be riding on the Tiger 800 or the Norton

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
  •