How do I prevent a continuous loop in Access VBA

Question

I'm running a simple procedure that checks Table 1 for a Yes/No and updates my table with either a Yes, No, or N/A if the record does not exist.

Public Function getAnswer() As Integer
Dim db As Database
Dim rst_MASTER As Recordset
Dim strSQL As String
Dim strSQL_Active as String
Dim strSQL_MASTER As String

'error handeling
On Error GoTo Err_Error

    DoCmd.SetWarnings False
    
    'updates all has bene flags if record with 401K type exists on bene file source
    strSQL = "UPDATE tbl_ACTIVE INNER JOIN tbl_FILE_SOURCE ON " & _
                "tbl_ACTIVE.Key= tbl_FILE_SOURCE.Key SET tbl_ACTIVE.isTrue= True " & _
                "WHERE tbl_FILE_SOURCE.PlanType='A';"
            
    DoCmd.RunSQL strSQL, True
    
    Set db = CurrentDb
    Set rst_MASTER = db.OpenRecordset("tbl_MASTER")
     
    If rst_MASTER.RecordCount > 0 Then
        rst_MASTER.MoveFirst
        
        Do Until rst_MASTER.EOF
            strSQL_Active = "SELECT tbl_ACTIVE.isTrue FROM tbl_ACTIVE WHERE tbl_ACTIVE.Key = " & rst_MASTER!Key

            rst_MASTER.Edit
            Select Case RetrieveVal(strSQL_Active, True)
                Case True
                    rst_MASTER![IsTrue] = "No"
                Case False
                    rst_MASTER![IsTrue] = "Yes"
                Case 0
                    rst_MASTER![IsTrue] = "N/A"
                Case Else
                    rst_MASTER![IsTrue] = "Err"
                    
            End Select
            
            rst_MASTER.Update
            rst_MASTER.MoveNext
        Loop
    End If

    getAnswer = 1

Err_Error:
     MsgBox Err.Number & Err.Description
     getAnswer = -1
       
End Function

When I run the above code, it seems to get stuff and continues to spin until I have to stop the program. I checked the table and only about half the fields are filled the rest remained blank. Each time it's at different intervals but never finishes.

How do I prevent this from getting caught in a that continuous loop?

Why do this in a loop at all? A second UPDATE statement on tbl_Master with an inner join to tbl_ACTIVE would suffice, no? Just the same, what is RetrieveVal doing? — JNevill
– JNevill, Commented Apr 25, 2022 at 15:20
I'm still thinking this is something that shouldn't be done in a loop. There is just a TON of sql being fired off for every record in tbl_MASTER that needs updating. Doing this relationally instead of looping would make more sense. — JNevill
– JNevill, Commented Apr 25, 2022 at 15:27
There is no obvious reason why this would go into an endless loop. Just give it more time - Access may tell you it's "not responding", but the code keeps running. BUT: as JNevill wrote, this can be done in one single query. — Andre
– Andre, Commented Apr 25, 2022 at 15:37
Yes it should be done relationally, but that's not answering the question of how to unstick a loop. For very large loops I Dim a numeric variable, iterate it by 1 every time, and then every few loops (say 20) use If CountLoops Mod 20 = 0 then Doevents. That will stop it from 'not responding' but will also slow down the Macro - however, if you're running a loop on 145000 records, you should be expecting a slow macro already! — Spencer Barnes
– Spencer Barnes, Commented Apr 25, 2022 at 16:19
Use Access query designer to build query that joins tables then select UPDATE on ribbon, add conditional expression for the UPDATE TO. But if these tables have a relationship, why update tblMaster? This value can be calculated when needed. Is tblActive.IsTrue a Yes/No field and tblMaster.IsTrue is text? I also question why there is an "Active" and a "Master" table instead of just one table with a status field. — June7
– June7, Commented Apr 25, 2022 at 17:30

dbmitch · Accepted Answer · 2022-04-25 17:40:47Z

1

Based on the limited description of your table design and the key fields, this should work using 3 preset update queries. I'm sure you can edit them to fit your specific tables and desired results.

QUERY 1:

UPDATE tbl_Master
INNER JOIN tbl_ACTIVE
ON tbl_ACTIVE.Key = tbl_Master!Key
SET tbl_Master.isTrue = "No"
WHERE tbl_ACTIVE![IsTrue] = True

QUERY 2:

UPDATE tbl_Master
INNER JOIN tbl_ACTIVE
ON tbl_ACTIVE.Key = tbl_Master!Key
SET tbl_Master.isTrue = "Yes"
WHERE tbl_ACTIVE![IsTrue] = False

QUERY 3:

UPDATE tbl_Master
INNER JOIN tbl_ACTIVE
ON tbl_ACTIVE.Key = tbl_Master!Key
SET tbl_Master.isTrue = "Err"
WHERE tbl_ACTIVE![IsTrue] Not In (True, False)

Note: You'll have to edit your 'RetrieveVal function to return something other than 0 for an EOF condition - your False condition evaluates to 0 already.

answered Apr 25, 2022 at 17:40

dbmitch

5,3964 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I prevent a continuous loop in Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related