CASE Statement(s) to Update RS... works partially

**skydivetom** · 07-09-2021, 10:37 AM

Experts:

I need some assistance with tweaking/validating my CASE statements which are used to update a record set. Please see VBA code below...

Code:

Public Sub METHOD_2_FOR_INJ_TYPE()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim n As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("02_tbl_INJ_TYPE_After")
    
    Do While Not rs.EOF
    rs.Edit
    Debug.Print rs!INJ_INJURY_TYPE_TIER1_ID & "  " & rs!INJ_INJURY_TYPE_TIER2_ID
    
    'Increase counter
    n = n + 1
     
          
    Select Case rs!INJ_INJURY_TYPE_TIER1_ID
          
        Case 45
            rs!INJ_INJURY_TYPE_TIER2_ID = 85
        
        Case 105
            rs!INJ_INJURY_TYPE_TIER2_ID = 110
        
        Case 135
            rs!INJ_INJURY_TYPE_TIER2_ID = 140
        
        Case 285
            rs!INJ_INJURY_TYPE_TIER2_ID = 15
        
        Case 290
            rs!INJ_INJURY_TYPE_TIER2_ID = 25
        
        Case 295
            rs!INJ_INJURY_TYPE_TIER2_ID = 170
        
        Case Null
          
            Select Case rs!INJ_INJURY_TYPE_TIER2_ID
          
                Case 5 To 15
                    rs!INJ_INJURY_TYPE_TIER1_ID = 285
                    
                Case 20 To 25
                    rs!INJ_INJURY_TYPE_TIER1_ID = 290
          
                Case 30 To 100
                    rs!INJ_INJURY_TYPE_TIER1_ID = 45
                    
                Case 105 To 110
                    rs!INJ_INJURY_TYPE_TIER1_ID = 105
                    
                Case 115 To 140
                    rs!INJ_INJURY_TYPE_TIER1_ID = 135
                    
                Case 145 To 170
                    rs!INJ_INJURY_TYPE_TIER1_ID = 295
                    
            End Select
          
    End Select

        
    rs.Update
    rs.MoveNext
    Loop
    
    MsgBox n & " INJ_INJURY_TYPE records have been updated.", vbInformation, "Status"
    
End Sub

Per attached JPG "Test Results", the first 6 records are converted correctly. That is, on TIER2, I converted 1st record = 120 into 85.

Now, once I get to the 7th record (NULL value in TIER1), I expected the code to replace the Null value with 285 (1st "child" CASE statement within the "parent" CASE statement). Unfortunately, none of the records which contain a NULL value in TIER1 are updated.

My question: How do I need to restructure my CASE statements so that the null value in TIER1 are updated to 285, 290, 45, 105, 135, 295?

**orange** · 07-09-2021, 10:47 AM

Can you post a sample database? I am busy for about 1 hr., but will look.

**Minty** · 07-09-2021, 11:02 AM

Could you test to make sure that the Null value is actually being picked up?

**davegri** · 07-09-2021, 11:38 AM

Instead of Case NULL,
try
Case ELSE

**skydivetom** · 07-09-2021, 12:25 PM

davegri:

So simply... but yet so POWERFUL... changing NULL to Else "did the trick".

Many thanks!
Tom

**skydivetom** · 07-09-2021, 12:50 PM

Follow-up to post #5.

*************************

All:

Urgh... I spoke (wrote) too quickly.

That is, while the NULL value get updated, other records are now also updated. Please see attached DB... it contains the following:

1. Table [01_tbl_INJ_TYPE_Before]... this is the original data. I only included 50 sample records.
2. Table [02_tbl_INJ_TYPE_After} ... copy of table [01_...]. This is where the update (from VBA) are made.
3. Form "F01_MainMenu" which invokes the update function.
4. Module "modUpdateProductTables" contains VBA code.

Process:
1. Copy table [01_tbl_INJ_TYPE_Before' and save as, e.g., "xxx"
2. Delete table [02_tbl_INJ_TYPE_After].
3. Rename table [xxx] as "02_tbl_INJ_TYPE_After". Both tables are now identical.
4. Open form and click on command button.
5. Now, review/compare both tables.

More info on the tables:
a. For *testing purposes* only, records 1 through 18 contain values that are included in the VBA case statement.
b. Records #19 through #50 do NOT have values that should be picked up by the VBA; thus, these records should remain unchanged.
c. Originally, I had the "CASE NULL" in my child-level CASE statement IOT identifify records 7 through 18.
d. I changed the CASE NULL to CASE ELSE... and it worked since the NULL values were correctly updated.
e. *** HOWEVER *** the CASE ELSE also led to records (#19 through #50) being updated with value = 135 given that "120" (tier 2) was met by the condition "Case 105 To 110".

My question:
How does the VBA need to be tweaked so that only records 1-18 are updated while 19 - 50 remained unchanged? Again, for testing purposes only, I purposefully added values in record 1-18 to meet the VBA conditions.

**davegri** · 07-09-2021, 01:16 PM

Maybe this would restrict it properly:

Code:

    Select Case Nz(rs!INJ_INJURY_TYPE_TIER1_ID, 0)
          
        Case 45
            rs!INJ_INJURY_TYPE_TIER2_ID = 85
        
        Case 105
            rs!INJ_INJURY_TYPE_TIER2_ID = 110
        
        Case 135
            rs!INJ_INJURY_TYPE_TIER2_ID = 140
        
        Case 285
            rs!INJ_INJURY_TYPE_TIER2_ID = 15
        
        Case 290
            rs!INJ_INJURY_TYPE_TIER2_ID = 25
        
        Case 295
            rs!INJ_INJURY_TYPE_TIER2_ID = 170
        
        Case 0
          
            Select Case rs!INJ_INJURY_TYPE_TIER2_ID

**skydivetom** · 07-09-2021, 01:32 PM

davegri -- YES! That worked great! Thank you again for the help! I very much appreciate it.

**orange** · 07-09-2021, 02:37 PM

Glad you have it resolved.

**skydivetom** · 07-10-2021, 11:22 AM

Experts:

Another quick follow-up... I'm sure the error I ran into has a totally simple solution.

Quick recap:
1. Attached are 3 DBs: "INJ_BODY_PART_v01"; "INJ_INJURY_TYPE_v01"; "Merged_Functions"
2a. As previously stated, each of the DBs has 2 tables where table starting with "01_" is the original data set.
2b. Also, table "02_" is an exact copy of "01_" w/ the caveat that the function's records updates are applied to only table "02".

Process:
- Upon opening either "INJ_BODY_PART_v01" OR "INJ_INJURY_TYPE_v01", open form "F01_MainMenu".
- Click on the command button; doing so will execute the update function (see attached module) and apply the changes (where BRs are met) to table "02_".
- Again, both functions (in these 2 DBs) execute without issues and based on the business rules, all changes are applied correctly.

... so far so good.

- Now, the 3rd file "Merged_Functions" is nothing but a consolidated version of the 2 other DBs.
- The module includes both functions and when clicking the command button (in form), I would like to execute both functions.
- Unfortunately, I now get an error in the 2nd function. The VBA error indicates "Run-time error '3265'. Item not found in this collection."

My questions:
1. Given that I copied all table objects (and VBA code) into the "merged DB", why am I seeing this error and how can I fix it?
2. Also, presently, I have repeated the same declarations in both functions. Going forward, I may have 5 to 7 functions (w/ different business rules applied to different fields/tables) in this module. Thus, if possible, could I streamline this code so that certain elements will be declared only once but can by utilized by n functions? If so, how?

Presently though my primary goal is to be able to execute these 2 functions in the VBA. Any thoughts on how I need to tweak the VBA in the module so that I won't get the run-time error any longer?

Thanks,
Tom

**davegri** · 07-10-2021, 11:46 AM

- Unfortunately, I now get an error in the 2nd function. The VBA error indicates "Run-time error '3265'. Item not found in this collection."

The third DB runs fine for me, no runtime errors occur.

Try compact/repair, closing the DB and re-opening

**skydivetom** · 07-10-2021, 11:57 AM

I'll be darned... never knew that "compact/repair" would take care of VBA error. Thanks... that worked. Appreciate the feedback.

Do you have any recommendations for question #2? Is there a way to streamline the code (besides the individual business rules) so that I won't have to, e.g., declare "Dim db As DAO.Database", "Dim rs As DAO.Recordset", etc. in each function? If not, I'll be ok... just wanted to make sure I didn't overlook the opportunity for generalizing the code. Again, I may have 5-7 functions within the module.

Thanks again, davegri... I appreciate your assistance.

**davegri** · 07-10-2021, 12:20 PM

Do you have any recommendations for question #2? Is there a way to streamline the code (besides the individual business rules) so that I won't have to, e.g., declare "Dim db As DAO.Database", "Dim rs As DAO.Recordset", etc. in each function? If not, I'll be ok... just wanted to make sure I didn't overlook the opportunity for generalizing the code. Again, I may have 5-7 functions within the module.

You could probably set some global object variables to the DB and recordsets, then pass those variable names to a function. I'm too lazy to code and debug that. Declaring the objects within each function would be OK with me.

**skydivetom** · 07-10-2021, 12:22 PM

Ok... works for me. Thank you davegri.

CASE Statement(s) to Update RS... works partially

Thread Tools

CASE Statement(s) to Update RS... works partially

Similar Threads

Help with a calculated query field for late status, only partially works

Case Statement

Case or Switch in an update statement

Spellcheck works in MSA2k3, but only partially in MSA2k3 Runtime

Update query only partially working using SQL statement

Posting Permissions