infinite loops

[atksamy]

Heré is the code I am working on. I am using this to validate the values of tables in a database.
i am almost done but i have a infinite loop goin towards the end loop s not ending and the program is runnin over and over again

Option Explicit
 
Private Sub UpdateNulls()
  Dim rs As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim rs3 As DAO.Recordset
  Dim tdf As DAO.TableDef
  Dim db As Database
  Dim varii As Variant, strField As String
  Dim strsql As String, strsql2 As String, strsql3 As String
  Dim astrFields As Variant
  Dim intIx As Integer
  Dim field As Variant
  Dim astrvalidcodes As Variant
  Dim found As Boolean
  Dim v As Variant
 
 
  Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
  varii = ""
  Do While Not EOF(1)
    Line Input #1, strField
    varii = varii & "," & strField
  Loop
  Close #1
  astrFields = Split(varii, ",")  'Element 0 empty
 
 
  'OPEN WORD DOCUMENT FOR WRITING REPORT
 
                     Dim objWord As Word.Application
                     Dim docWord As Word.Document
                     Dim docExists As Boolean
                     Set objWord = CreateObject("Word.Application")
                     objWord.Visible = True
                     On Error GoTo OpenDoc
                     docExists = False
                     Set docWord = objWord.Documents.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT1.doc")
                     docExists = True
OpenDoc:
 
                     On Error GoTo 0
                     If Not docExists Then
                     Set docWord = objWord.Documents.Add
                     End If
 
 
 
    For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    strsql = "Select t.* From [" & tdf.Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"
 
    Set rs = CurrentDb.OpenRecordset(strsql)
 
    Do While Not rs.EOF
        For intIx = 1 To UBound(astrFields)
 
         strsql2 = "SELECT label.validcode FROM variablen s INNER JOIN label ON s.id=label.variablenid WHERE varname='" & astrFields(intIx) & "'"
         strsql3 = "SELECT s.[" & astrFields(intIx) & "], s.fall from [" & tdf.Name & "] s Inner Join 01UMWELT on s.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"
 
            Set db = OpenDatabase("C:\Documents and Settings\TAYYAPP\Desktop\GIDAS_Codebook.mdb")
            Set rs2 = db.OpenRecordset(strsql2)
 
                With rs2
                .MoveLast
                .MoveFirst
                 astrvalidcodes = rs2.GetRows(.RecordCount)
                .Close ' assuming you want to do this
                End With
 
            Set rs3 = CurrentDb.OpenRecordset(strsql3)
 
                With rs
                    On Error Resume Next   'Ignore field if table doesn't have it
                    Call Err.Clear
                     If Err.Number <> 3265 Then          ' Field not found in table
 
                    With rs3
                    While Not rs3.EOF
                        found = False
                        For Each v In astrvalidcodes
                        If v = .Fields(0) Then
                        found = True
                        Debug.Print .Fields(0)
                        Debug.Print .Fields(1)
                        Exit For
                        End If
                        Next
                    If Not found Then
                    If tdf.Name <> "01umwelt" Then
 
                    docWord.Content.InsertAfter "Variable     " & astrFields(intIx) & "     " & "in   record" & "     " & .Fields(1) & "    contains invalid  value  not  prescribed  in  code  book "
                    docWord.Content.InsertParagraphAfter
 
                    End If
                    End If
                    .MoveNext
 
 
                    Wend
                    End With
                    End If
 
 
               On Error GoTo 0        'End of special handling
          End With
        Next intIx
        rs.MoveNext
      Loop
    End If
  Next
 
 
  objWord.Visible = True
  docWord.Content.InsertParagraphAfter
  docWord.SaveAs ("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.doc")
End Sub
 
 

[Stewart Ross]

We can't debug more than 100 lines of code for you - it is unreasonable to expect us to do so without any indication of what you have done to restrict the range of your problem and debug for yourself.

At a glance, your indentation of the FOR loops, WHILE loops and IF statements is incorrect throughout and is actively misleading to you about where one loop begins and the next ends. You should go back through your code and re-indent every part consistently.

When you have done that, you should set a breakpoint and step through your code line by line, checking the values of all local variables set and cleared within your loops. Systematic working is needed here, as well as systematic logic.

-Stewart

[atksamy]

I have started debugging and have removed the do while loop at line 55.

now when control goes to next table at line 71 i get an runtime error 3061.I know i am getting this because the sql cannot be executed as teh field is not present in the table. I want to ignore this error and goto next table

how do i ignore this error

[Stewart Ross]

To ignore errors in the short term use

On Error Resume Next

You have a number of On Error statements in your code; comment them out temporarily and use a single On Error Resume Next placed after the DIM statements.

-Stewart