Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Solong is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Posts
    2

    Return to specific record on new Access run


    I have an Access database with some 50,000 aircraft records in it - the records need updating on a random basis as more information comes to hand. I have a form which handles the records and allows such updating and provides all the record navigation I require. My problem is that every time I open the form it starts at the first record which is tedious if I want to work on a record several thousand in. I could highlight a field called 'Serial' and enter a specific value to call up the appropriate record, but I would prefer the form to open on the last record I was working on. I have tried writing the Serial to a separate table, then using that value as part of an SQL string in the query behind the form (q_Aircraft) using the VBA DoCmd.OpenForm "f_Aircraft", , q_aircraft, Sql where Sql = "Serial = '" & AirSerial & "'" with AirSerial being the target serial number but that results in just the one single record being picked up rather than the entire database but with the focus on the specific record. Once I have completed an update, I want to be able to click to the next record (or several records further on) so need the entire set of records to be there and my current methodology prevents that. Any suggestions?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only way you can pinpoint the record you worked on last is to date/time stamp it because by that definition, it could be anywhere between record 1 and 1000. I'd say that to do this and still get a complete record list provided by the table or query would be to make that record the active record. You probably could use the Find method for that. Do not make the mistake of thinking that the Last function will reliably find the "last record you worked on".

    EDIT - to correct GoTo to Find
    https://docs.microsoft.com/en-us/off...cmd.findrecord
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    When you open a form like that, all you have done is filter to that record. If you remove the filter, you will get all records, but again start at the top?
    I would probably have a small button that would store the ID, removed the filter then find the ID, perhaps movenext to the next record after your filtered choice.

    Thinking about it more though, another choice would be >= your stored ID, and then you would be filtered for all including and after your stored ID.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    You probably need some kind of history system to record your activities, and a way for you to "tell" the form to "remember" a particular activity. Also, do you want it to remember what record you viewed but didn't make changes? For instance, you are viewing a record but have to leave work, and you hope tomorrow the form will remember it. You certainly don't want it to remember every record you view when you are just browsing the records. So this is a tricky situation.

    I had a similar situation. Take a look at my form. (If the Imgur site doesn't work, look at the attached image of this post). I have a movie collection database with 4000+ records. I set up a subform datasheet to show these 4000+ records. This allows easier browsing of tons of records. If your form only shows one record per page, consider this method. That would solve one of your problems, the tediousness of navigating records.

    Secondly, I have an Edit button on top that opens up another form that allows editing of the record you are currently viewing (in this case, the movie title "Mouchette"). And THAT would be a perfect location for you to put in an event procedure (such as OnClick) that adds a history record for the item you are editing.

    Thirdly, my datasheet is uneditable. A record is only editable if I click the Edit button as I mentioned above. Consider this as well, as you don't want accidental changes while browsing records. (A history system might also help you look up inadvertent edits.)



    Click image for larger version. 

Name:	mycollection pic3.jpg 
Views:	21 
Size:	241.0 KB 
ID:	47871

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the Unload event of the f_Aircraft form write the current Serial in a local tblSettings table. In the Load event of the same form add something like this (adjusted to match your names):
    Code:
    Dim rst As DAO.Recordset 
    Dim strSerial As String 
     
    Set rst = Me.RecordsetClone 
    strSerial = nz(dLookup("LastSerial","tblSettings"))
    rst.FindFirst "Serial = '" & strSerial & "'"
    If rst.NoMatch Then Goto CleanUp 'skip and open the form normally
          
    Me.Bookmark = rst.Bookmark   'load the last record     
     
    CleanUp:
    rst.Close
    Set rst=Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    O/P was already doing that?, but just filtering to that record, or at least that is my understanding?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, when you open the form using the Where clause of the Docmd.OpenForm method it opens the form filtered to that record. But that was not what the OP wanted, which I think using the RecordsetClone.Find will achieve as it repositions the form to that record without restricting the record source so the navigation buttons should be fully functional.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Well I tested my method before offering it, and that worked just as well, without any other commands?
    Or am I missing something here?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    But you still have to remove the filter manually, don't you? And you propose adding a button as well. With what I suggested the form would automatically save the last record on close (unload) and open it when loading but allowing full navigation.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    No, that was my first thought, then I thought, why not just filter to that record and anything after that?
    That would use the method the O/P is using now, just a subtle change. They said they also wanted to move to records after the stored one. If before then ok, remove the filter.
    Not saying my method is better, just simpler?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I took that the OP wants to navigate to other records after opening the form, not necessarily in one direction (next) but also previous; my method simply positions the form at the last used record. I don't see that being that complicated, it is commonly used code (the built in Access combo wizard does something similar when you select the option to search for records in an unbound combo).

    Now that the OP has lots of alternatives (I actually used Micron's Docmd.FindRecord to do something like this many times before) I'm sure she\he will pick whatever fits better.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would prefer the form to open on the last record I was working on.
    again, that can only be done with an update time stamp.
    Once I have completed an update, I want to be able to click to the next record (or several records further on) so need the entire set of records to be there
    this works to do that, based on having a date field that stores the UpdateDate, which would be updated to Now() upon record saving or editing. I've coded it to work from a button click, which I'd recommend rather than arbitrarily opening the form to the record last edited or created without any choice at the time. Pardon the lame button name. I'm assuming the table has an autonumber PK field (PK_ID_fieldName in the code below). Also, I didn't have all those [brackets] so I might have goofed it up there.

    Code:
    Private Sub Command177_Click()
    Dim rs As DAO.Recordset
    Dim lngID As Long
    
    Set rs = Me.RecordsetClone
    lngID = DLookup("[FieldName]", "[tableName]", "[FieldNameWithUpdateDate]" = #" & DMax("[FieldNameWithUpdateDate]", "[tableName]") & "#")
    rs.FindFirst ("[PK_ID_fieldName] = " & lngID)
    If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
    Set rs = Nothing
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    again, that can only be done with an update time stamp.
    Sorry but I disagree, having code in the unload event of the form that saves that unique ID in a local settings tables would also work (and the local settings table could also have a boolean flag to load the last record on load or not, eliminating the need for the button).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe check out Allen Browne's Return to the same record next time form is opened ?


    Vlad's code is a lot like Allen's........

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is that good or bad ?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 07-25-2019, 02:47 PM
  2. Replies: 24
    Last Post: 09-16-2018, 04:02 PM
  3. Replies: 3
    Last Post: 11-28-2016, 03:17 PM
  4. Replies: 1
    Last Post: 12-31-2012, 06:25 PM
  5. Replies: 3
    Last Post: 08-15-2011, 10:06 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