CODEWITHSUNDEEP
vbaloopsms-accessrecordset
designspeaks
designspeaks

Reputation: 91

How do I prevent a continuous loop in Access VBA

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?

Upvotes: 0

Views: 211

Answers (1)

dbmitch
dbmitch

Reputation: 5386

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.

Upvotes: 1

Related Questions