Results 1 to 6 of 6
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    VBA routine requires small modification (record set)

    Good morning:

    I need some assistance with (I hope) is a minor tweak in a currently working VBA function. Attached are two ACCDB version.

    Background on Version 1:
    - Upon opening, open the form and click on the command button.
    - Based on the module (VBA), business rules are executed which will update a cost field in tbl_PERSON.
    - As depicted in the image "Version 1", field [MSHP_CLASS_ID] is included in table [tbl_PERSON].
    - Then, in the VBA, I use [MSHP_CLASS_ID] in my SELECT CASE statement update matching records accordingly.


    - This *testing* version works fine and does NOT require any modification.

    Background on Version 2 (the one I need some help with):
    - V2 is essentially a copy of V1.
    - In this version, however, a 2nd table [tbl_MISHAP] is added and the field [MSHP_CLASS_ID] only exists within the new table.
    - Both tables have field [MSHP_LEGACY_REPORT_NUMBER] in common and it can be used to join the tables (if necessary).
    - In the VBA, the process is required. That is, I need to update records in the PERSON table still based on [MSHP_CLASS_ID].

    My question:
    - Given that [MSHP_CLASS_ID] is no longer part of [tbl_PERSON] (as demonstrated in testing version 1), how should be VBA be tweaked so that I still update the cost field in the PERSON table based on matching records in table [MSHP_CLASS_ID]?

    Thank you for your help in advance.
    Attached Thumbnails Attached Thumbnails Version 1.JPG   Version 2.JPG  
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    I only looked at v2.
    Not sure of requirement since post doesn't have details below case statement.
    Not sure what exactly has to be updated.
    But try this sql (Select query only until confirmation of usefulness)

    SELECT tbl_PERSON.ID AS PersID
    , tbl_MISHAP.MSHP_CLASS_ID
    FROM tbl_MISHAP INNER JOIN tbl_PERSON ON
    tbl_MISHAP.MSHP_LEGACY_REPORT_NUMBER = tbl_PERSON.MSHP_LEGACY_REPORT_NUMBER;

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- thank you for the feedback.

    First, allow me to provide additional background:
    - In v1, the VBA worked since I had the required field [MSHP_CLASS_ID] in the same table. Thus, when looping through the recordset (rs), all records/fields are in sync.
    - However, in contrast to v1, v2 has the [MSHP_CLASS_ID] in a different table. Now, when looping through the rs, my records are somehow no longer synchronized. That is, prior to your posting the feedback, I actually came up w/ a solution that executes (see below). When comparing the values in the calculated field [PERS_INJURY_COST] between v1 and v2, both versions now have different calculated values for a *subset* of records (while others have the same calculated values in v1 and v2).

    Code:
    Public Sub Update_PERS_INJURY_COST()
    
        Dim db As DAO.Database
        Dim rs_mshp As DAO.Recordset
        Dim rs_pers As DAO.Recordset
        Dim n As Integer
            
        Set db = CurrentDb
        Set rs_mshp = db.OpenRecordset("tbl_MISHAP")
        Set rs_pers = db.OpenRecordset("tbl_PERSON")
        
        Do While Not rs_pers.EOF
        rs_pers.Edit
        
        'Increase counter
        n = n + 1
        
        ' **** BUSINESS RULES (calculation of injury costs) ****
        Select Case rs_mshp!MSHP_CLASS_ID
        
            'Class A
            Case 1
    
                Select Case Nz(rs_pers!PERS_EMPLOYMENT_STATUS2_ID, 0)
                
      
      ' ... more code
    
    
          rs_pers.Update
          rs_mshp.MoveNext
          rs_pers.MoveNext
          Loop
          
          'Throw message box
          MsgBox n & " PERS_INJURY_COST records in table 'tbl_INJURY' have been updated.", vbInformation, "Status"
          
    End Sub

    Now, after I modified the code, you just posted a recommendation to use a SQL statement. Where should the SQL statement go in the posted version 2?

    Thanks,
    EEH

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Please have a look at the updated file, no need for second recordset, just use a dLookup.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    THANK YOU!!!!!!!!!!!!!

    Your solution is -- AS ALWAYS, ALWAYS, ALWAYS -- perfect! This addresses the RS looping *offset* (for lack of better terms).

    Thousand thanks for your recommendation and fixing the VBA function.

    Cheers,
    Tom

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome Tom!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 17
    Last Post: 08-21-2020, 12:46 PM
  2. Record Modification Dates
    By CementCarver in forum Programming
    Replies: 11
    Last Post: 05-27-2013, 10:16 AM
  3. Update datetime stamp on record modification
    By sitaramnayak in forum Access
    Replies: 1
    Last Post: 10-12-2011, 11:54 AM
  4. Record Retrieval/Modification Based On Entry
    By eddiebo924 in forum Forms
    Replies: 1
    Last Post: 06-19-2011, 06:41 PM
  5. Replies: 3
    Last Post: 02-09-2011, 07: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