Solved

Loop not moving to the next record

Posted on 2010-09-14
23
586 Views
Last Modified: 2012-05-10
I'm so confused and frustrated!  I'm trying to loop through >5K records in table "Files" that list file locations, go to that file, get what I need, put it in another table "Alerts" and move to the next file name in the "Files" table.  It's parsing the data correctly and adding it to "Alerts" ok, but it's all from the same file!  It isn't moving to the next record in the "Files" table and it errors "too many files" after 255 loops.  I assume that's a Free File limitation but I don't know how else to handle it. The files have unique numbers at the end of their names, but I haven't had any luck using them either.  (example: C:\Documents and Settings\sellis\Desktop\Remedy KM\WallboardAlerts\Wallboard2.html")  Here's the code.  I'd really appreciate any suggestions.

Private Sub btnLoad_Click()

Dim txt As String
Dim strF As String
Dim dbs As Database
Dim rstA As Recordset
Dim rstF As Recordset
Set dbs = CurrentDb
Set rstA = dbs.OpenRecordset("Alerts")
Set rstF = dbs.OpenRecordset("Files", dbOpenDynaset)

'LOOP THROUGH FILES
With rstF

    Do While Not .EOF
        rstF.MoveNext
        Forms!form1.cmbFile = File
        Forms!form1.cmbFile.Requery
        Forms!form1.Refresh
       
'LOOP THROUGH HTML
        filenum = FreeFile
        Open Forms!form1.cmbFile For Input As #filenum
   
NextLine:
            Do While Not EOF(filenum)
            Line Input #filenum, txt
            If InStr(txt, "Alert Number:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    stralertno = Replace(txt, "&#45", "-")
            ElseIf InStr(txt, "Product affected:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    strprod = Replace(txt, "&#45", "-")
            End If
            Loop
NextFile:
            Close #FileName
    'ADD TO ALERTS TABLE
        With rstA
            .AddNew
            !AlertNo = stralertno
            !Prod = strprod
            .Update
        End With
    Loop
End With

End Sub
0
Comment
Question by:sarahellis
  • 10
  • 6
  • 2
  • +2
23 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 33674067
You are at no point reading any data from Files (rstF).

0
 
LVL 77

Expert Comment

by:peter57r
ID: 33674095
And as an 'incidentally' comment, if you were to read from Files you would be starting from the second record and missing out the first one.

You need a structure of :

rs.movefirst
Do until rs.eof
' do someprocessing

rs.movenext
Loop
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674101
Close #FileName
should probably be
Close #filenum
that would fix the "too many files" i guess
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:sarahellis
ID: 33674102
Right - I WAS trying to get them out of the recordset.  Then I changed it so that it was supposed to scroll through the records in a combo box on a form.  I've had more experience with controls than recordsets.  It was a shot in the dark.  Nevertheless....
0
 
LVL 13

Accepted Solution

by:
Surone1 earned 500 total points
ID: 33674115
Forms!form1.cmbFile = File
maybe you want ???
Forms!form1.cmbFile = .File
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33674133
test this



Private Sub btnLoad_Click()

Dim txt As String
Dim strF As String
Dim dbs As Database
Dim rstA As Recordset
Dim rstF As Recordset
Set dbs = CurrentDb
Set rstA = dbs.OpenRecordset("Alerts")
Set rstF = dbs.OpenRecordset("Files", dbOpenDynaset)

'LOOP THROUGH FILES

With rstF 
rstF.MoveFirst
    Do Until .EOF
'        rstF.MoveNext  ' << Moved before Loop
        Forms!form1.cmbFile = File
        Forms!form1.cmbFile.Requery
        Forms!form1.Refresh
       
'LOOP THROUGH HTML
        filenum = FreeFile
        Open Forms!form1.cmbFile For Input As #filenum
   
NextLine:
            Do While Not EOF(filenum)
            Line Input #filenum, txt
            If InStr(txt, "Alert Number:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    stralertno = Replace(txt, "&#45", "-")
            ElseIf InStr(txt, "Product affected:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    strprod = Replace(txt, "&#45", "-")
            End If
            Loop
NextFile:
            'Close #FileName  '<< changed to below
             Close #filenum
    'ADD TO ALERTS TABLE
        With rstA
            .AddNew
            !AlertNo = stralertno
            !Prod = strprod
            .Update
        End With
     rstF.MoveNext 
    Loop
End With

End Sub

Open in new window

0
 

Author Comment

by:sarahellis
ID: 33674140
Oh, Surone1 - Good job!  That eliminated that error.  I got 5400 of the same record.  1 down, 1 to go!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33674208
Hi,

little hint: If you go to the "Tools" - "Options" menu and select "Require Variable Declaration" (which automatically adds an "Option Explicit" line to all new modules) then you would have found the problem on your own. You should always declare any variable first and if you want to add this check to existing modules, add the line "Option Explicit" as first line in any module where it is missing.
If you use "Debug" - "Compile" the compiler will show you any such errors and any missing variable. With this method you will have a cleaner code as you are forced to think about any variable first.

Cheers,

Christian
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674248
what are the field names of the "files" recordset?
0
 

Author Comment

by:sarahellis
ID: 33674264
Just one field called "File".  
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674274
Private Sub btnLoad_Click()

Dim txt As String
Dim strF As String
Dim dbs As Database
Dim rstA As Recordset
Dim rstF As Recordset
Set dbs = CurrentDb
Set rstA = dbs.OpenRecordset("Alerts")
Set rstF = dbs.OpenRecordset("Files", dbOpenDynaset)

'LOOP THROUGH FILES

With rstF
rstF.MoveFirst
    Do Until .EOF
'        rstF.MoveNext  ' << Moved before Loop
        Forms!form1.cmbFile = .File  '<<< changed to get from recordset
        Forms!form1.cmbFile.Requery
        Forms!form1.Refresh
       
'LOOP THROUGH HTML
        filenum = FreeFile
        Open Forms!form1.cmbFile For Input As #filenum
   
NextLine:
            Do While Not EOF(filenum)
            Line Input #filenum, txt
            If InStr(txt, "Alert Number:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    stralertno = Replace(txt, "&#45", "-")
            ElseIf InStr(txt, "Product affected:") > 0 Then
                    Do While InStr(txt, "<") > 0
                        txt = Replace(txt, Mid(txt, InStr(txt, "<"), InStr(txt, ">") - InStr(txt, "<") + 1), "")
                    Loop
                    txt = Right(txt, Len(txt) - InStr(txt, ":") - 1)
                    strprod = Replace(txt, "&#45", "-")
            End If
            Loop
NextFile:
            'Close #FileName  '<< changed to below
             Close #filenum
    'ADD TO ALERTS TABLE
        With rstA
            .AddNew
            !AlertNo = stralertno
            !Prod = strprod
            .Update
        End With
     rstF.MoveNext
    Loop
End With

End Sub
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674295
by moving the .movenext down we prevent skipping the first record (file)
0
 

Author Closing Comment

by:sarahellis
ID: 33674370
Thank you!  Sometimes I can't see the forest for the trees!  
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674390
you should really have split the points here i feel :-(
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33674391
;-)
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674398
but we are glad to help :-)
0
 

Author Comment

by:sarahellis
ID: 33674459
Ooooh, Sorry Capricorn1 - I didn't notice that his code just expanded on yours.  You've helped me so many times before too!  If a moderator can split, please do.  My sincere apologies.
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33674477
lol cap just gave you the solutions me and peter57r suggested, only sugarcoated ;-)
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33706333
my suggestion:
150 points each to cap and peter57r. the other 200 for me.
but you decide ofcourse! :-)
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33706342
and yes i was that someone else...
0
 

Author Comment

by:sarahellis
ID: 33707853
I'm afraid that I didn't find peter57r helpful.  He made two posts.  One that was just a statement "You are at no point reading any data from Files (rstF).", which was NOT AT ALL helpful.  The other was about reading the first record, which was not related to the problem I was having.  As he said, it was "incidentally."  I actually had .MoveFirst in the code originally and would have put it back once I got it working right.   Unfortunately, I found his tone to be a little bit sarcastic and condescending, so I didn't pay him much attention because of that.

As I looked more closely, at the posts, they were both solved by Surone1.  One issue was the "Close #FileName" vs "#FileNum" and the other was "forms!form1.cmbFile = .File" vs "=File"

These solved the problem I was having.  Capricorn1 was nice to re-post the code with the suggestions.  After re-examining the posts, the other helpful suggestion was from bitsqueezer, suggesting I use Option Explicit, which I did and was helpful.  But, I still think Surone1 should receive all the points.  

I do appreciate everyone's time in helping me with this.  I comment to everyone I know how wonderful the Experts are on this site.  You guys have saved me so many times.  Thank you.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33708060
Hi,

in my case it was only meant as I wrote: a little hint, not to assign points for as it was no solution for your problem...:-)
I'm glad that it helpful for you.

Cheers,

Christian
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now