Skipping a few records (1 Viewer)

[littlej]

Hi all!
I am currently writing some code for a custom import. The problem I encounter is that it is reading the second record on each page and skipping the rest. I need all records on that page. I have tried a movefirst and movenext stmt but the same problem occurs. Any suggestions on what I maybe missing? I have the code but it is sort of long.. Thanks.

 

[WayneRyan]

littlej,

Need way more information here, table structures, file formats,
and code.

Wayne

 

[littlej]

Code

Below is the code. I am converting a text file into an Access table. The text file is 467 pages. However I am receiving about 1/3 of the records. It skips the first record reads the second record then it skips 3rd record onward until it gets to the next page. Only then it reads the second record again. There are three lines for each record. Very confusing. Hope this helps.


Set dbvar = CurrentDb
Set rsimport = dbvar.OpenRecordset("PH010502", dbOpenDynaset)
Open "Y:/ops_programming/applications/first point/ph0105.txt " For Input As #1

DoCmd.SetWarnings False
DoCmd.OpenQuery "PHDel", acViewNormal, acEdit
DoCmd.SetWarnings True
Do While Not EOF(1)
Do Until strtextline = " BALANCE SVC DATE ADDR PHONE NUMBER PT PAY DATE C T"
Line Input #1, strtextline

If EOF(1) Then
GoTo Endroutine
End If
Loop

Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline
Line Input #1, strtextline2
Line Input #1, Strtextline3
stramtplaced = Left(strtextline, 17)
strlstsrv = Mid(strtextline, 18, 10)
strps = InStr(31, Strtextline3, ",")
strfirst = Trim(Mid(Strtextline3, strps + 1, 10))

strlast = Trim(Left(Strtextline3, strps))

strmailing = Mid(strtextline, 54, 29)
strhome = Mid(strtextline, 83, 15)
strssn = Mid(strtextline2, 27, 27)
strcity = Mid(strtextline2, 54, 23)
strstate = Mid(strtextline2, 78, 2)
strzip = Mid(Strtextline3, 54, 6)
stracct = Mid(strtextline, 98, 12)
strlstpmt = Mid(strtextline, 110, 13)


With rsimport


.AddNew
![Amt Placed for Collection] = stramtplaced
![Date of Last Service] = strlstsrv
![First Name] = strfirst
![last name] = strlast
![last name] = Replace(![last name], ",", "")
![Mailing Address] = strmailing
![Home Phone Number] = strhome
![SSN] = strssn
![City] = strcity
![State] = strstate
![Zip] = strzip
![Acct or Ref Number] = stracct
![Date of Last Payment] = strlstpmt
.Update
.MoveNext

End With

Line Input #1, strtextline

Loop

Endroutine:
Close #1
MsgBox "Process Complete."

 

[WayneRyan]

littlej,

It's hard to tell without seeing the data and checking what
you are reading.

It might be that the Inserts are failing (duplicates, missing required fields, etc.), or that the page headers are not consistent.

Have you run your code with the Debugger?

You can see the .AddNew execute, then check the table
to see if it was entered.

Just click on the left-margin (in design view) and that will
set a breakpoint. You'll see a little reddish-brown circle. Hover the mouse over a variable and its value will be displayed. F8 will move a single-step, F5 will run to completion (or the next break-point).

Feel free to set as many break-points as you like.

Wayne

 

[littlej]

Problem fixed.

Thanks

 

[Waltang]

It looks to me at a glance that your DO LOOP's should not be nested.

Try moving the :

Do While Not EOF(1)

to just below the first

Loop

Right now the code is cycling. Finding the heading row, inserting the heading row, then inserting the first record. It then loops back and starts looking for the text that makes up the heading row again. Since this text does not exist again in the file, it eventually reaches the end of file and exits out.

I am guessing at this since I dont know what makes up the text file