By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

infinite loops

atksamy
P: 91
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

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub UpdateNulls()
  4.   Dim rs As DAO.Recordset
  5.   Dim rs2 As DAO.Recordset
  6.   Dim rs3 As DAO.Recordset
  7.   Dim tdf As DAO.TableDef
  8.   Dim db As Database
  9.   Dim varii As Variant, strField As String
  10.   Dim strsql As String, strsql2 As String, strsql3 As String
  11.   Dim astrFields As Variant
  12.   Dim intIx As Integer
  13.   Dim field As Variant
  14.   Dim astrvalidcodes As Variant
  15.   Dim found As Boolean
  16.   Dim v As Variant
  17.  
  18.  
  19.   Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
  20.   varii = ""
  21.   Do While Not EOF(1)
  22.     Line Input #1, strField
  23.     varii = varii & "," & strField
  24.   Loop
  25.   Close #1
  26.   astrFields = Split(varii, ",")  'Element 0 empty
  27.  
  28.  
  29.   'OPEN WORD DOCUMENT FOR WRITING REPORT
  30.  
  31.                      Dim objWord As Word.Application
  32.                      Dim docWord As Word.Document
  33.                      Dim docExists As Boolean
  34.                      Set objWord = CreateObject("Word.Application")
  35.                      objWord.Visible = True
  36.                      On Error GoTo OpenDoc
  37.                      docExists = False
  38.                      Set docWord = objWord.Documents.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT1.doc")
  39.                      docExists = True
  40. OpenDoc:
  41.  
  42.                      On Error GoTo 0
  43.                      If Not docExists Then
  44.                      Set docWord = objWord.Documents.Add
  45.                      End If
  46.  
  47.  
  48.  
  49.     For Each tdf In CurrentDb.TableDefs
  50.     If Left(tdf.Name, 4) <> "MSys" Then
  51.     strsql = "Select t.* From [" & tdf.Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"
  52.  
  53.     Set rs = CurrentDb.OpenRecordset(strsql)
  54.  
  55.     Do While Not rs.EOF
  56.         For intIx = 1 To UBound(astrFields)
  57.  
  58.          strsql2 = "SELECT label.validcode FROM variablen s INNER JOIN label ON s.id=label.variablenid WHERE varname='" & astrFields(intIx) & "'"
  59.          strsql3 = "SELECT s.[" & astrFields(intIx) & "], s.fall from [" & tdf.Name & "] s Inner Join 01UMWELT on s.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"
  60.  
  61.             Set db = OpenDatabase("C:\Documents and Settings\TAYYAPP\Desktop\GIDAS_Codebook.mdb")
  62.             Set rs2 = db.OpenRecordset(strsql2)
  63.  
  64.                 With rs2
  65.                 .MoveLast
  66.                 .MoveFirst
  67.                  astrvalidcodes = rs2.GetRows(.RecordCount)
  68.                 .Close ' assuming you want to do this
  69.                 End With
  70.  
  71.             Set rs3 = CurrentDb.OpenRecordset(strsql3)
  72.  
  73.                 With rs
  74.                     On Error Resume Next   'Ignore field if table doesn't have it
  75.                     Call Err.Clear
  76.                      If Err.Number <> 3265 Then          ' Field not found in table
  77.  
  78.                     With rs3
  79.                     While Not rs3.EOF
  80.                         found = False
  81.                         For Each v In astrvalidcodes
  82.                         If v = .Fields(0) Then
  83.                         found = True
  84.                         Debug.Print .Fields(0)
  85.                         Debug.Print .Fields(1)
  86.                         Exit For
  87.                         End If
  88.                         Next
  89.                     If Not found Then
  90.                     If tdf.Name <> "01umwelt" Then
  91.  
  92.                     docWord.Content.InsertAfter "Variable     " & astrFields(intIx) & "     " & "in   record" & "     " & .Fields(1) & "    contains invalid  value  not  prescribed  in  code  book "
  93.                     docWord.Content.InsertParagraphAfter
  94.  
  95.                     End If
  96.                     End If
  97.                     .MoveNext
  98.  
  99.  
  100.                     Wend
  101.                     End With
  102.                     End If
  103.  
  104.  
  105.                On Error GoTo 0        'End of special handling
  106.           End With
  107.         Next intIx
  108.         rs.MoveNext
  109.       Loop
  110.     End If
  111.   Next
  112.  
  113.  
  114.   objWord.Visible = True
  115.   docWord.Content.InsertParagraphAfter
  116.   docWord.SaveAs ("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.doc")
  117. End Sub
  118.  
  119.  
Nov 7 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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
Nov 7 '08 #2

atksamy
P: 91
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
Nov 7 '08 #3

Expert Mod 2.5K+
P: 2,545
To ignore errors in the short term use
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 7 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.