Results 1 to 7 of 7
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Programme a Table Update

    I need help coming up with some code.

    I have the following form structures.

    Main Form
    Primary Key: FinancialRiskKey

    Sub Form:
    Primary Key: FinancialID
    Foreign Key: CIKey
    Field to Update: FinancialsOld

    Main Form Primary Key is a One to Many Relationship with Sub Form Foreign Key: CIKey



    However what I am trying to do, and not very successfully is:
    Click the control button on the Main Form Footer.
    Trigger code that will open the table for the SubForm, "tblFinancialIdentity"
    Go to the last Record with the CIKey = to the CIKey in the Sub Form
    Update the Field "FinancialOld" with Now()
    and then when that is done, my new Form Opens to add more data. (This bit I can do and it works and I can record after record and the sub form updates, no problem, its the bit above I am having trouble with.

    I have tried this code:
    Code:
    Private Sub NewFinancialReview_Click()Dim rs As Recordset
     Set rs = CurrentDb.OpenRecordset("tblFinancialIdentity")
     rs.Edit
     rs!CIKey = Me.FinancialHistoric!CIKey
     rs!FinancialOld = Now()
      rs.Update
    End Sub
    However the error I am encountering is that it goes to the first record and changes the CIKey number to that of the current record, thus over writing the very first record, and doesn't go through to find matching records.

    I am assuming that I need a find first and loop, but I do not know how to write this.

    Can someone help my out please?

    I don't mind if you give an example without using my table and field names, I can easily adapt an example.

    Regards

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    while on the subform just:


    me.recordset.movelast
    txtFinancialOld = now


    or


    set rst CurrentDb.OpenRecordset("select * from tblFinancialIdentity where [id]=" & txtKey )

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If your recordset only ever contains 1 record that ought to work. Since you are opening the entire table you likely expect your rs contains many records, but it does not. Without a MoveLast, the rs only ever contains 1 record and will often be the first record of the table. You would need to get all the records loaded then use one of the Find methods or Seek method to locate the specific record. IMO, better to open the rs on a sql statement or query that will return only the record you need rather than the whole table.

    If CIKey = to the CIKey in the Sub Form happens to be the last record and also the one you want to edit, then the suggestion to MoveLast and edit will work. If not, then you'll be editing any random record that happens to be the last one. I think that could be an unreliable method. If anything ever changes such that the desired record stops being the last one, you'll be back to where you are now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would be opening it with the criteira needed and ordering so that your last is the first in the recordset, or no order and MoveLast, but you need criteria regardless.
    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

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Be careful using Last (or First) as parameter. Records do not have inherent order and the "Last" record might not actually be what you expect.

    Open recordset with a filtered and/or ordered SQL statement. Or instead run an UPDATE action SQL with appropriate criteria.

    What exactly is the criteria that would be used in a Find action? Why could that not be used to filter recordset or as criteria in UPDATE sql?
    Last edited by June7; 11-28-2021 at 04:33 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with June7, which is why I suggested a one record recordset or Find or Seek. Also, AFAIK, you cannot use criteria with recordset Move methods, but I'm not sure that's what WGM meant.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Micron View Post
    Agree with June7, which is why I suggested a one record recordset or Find or Seek. Also, AFAIK, you cannot use criteria with recordset Move methods, but I'm not sure that's what WGM meant.
    No, I meant open recordset with criteria and order, so that whatever the o/p considers last is actually last in the recordset, then MoveLast to get the correct record.?
    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

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

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2017, 04:29 PM
  2. Making a Schedule/Timesheet/Programme etc
    By Cipher_Ultra in forum Database Design
    Replies: 2
    Last Post: 05-09-2013, 12:16 PM
  3. Replies: 1
    Last Post: 02-25-2013, 09:54 AM
  4. Replies: 3
    Last Post: 01-17-2013, 10:43 PM
  5. Replies: 1
    Last Post: 02-08-2012, 04:50 PM

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