Loop Statement based on a carriage return

  • Thread starter Thread starter Shawna
  • Start date Start date
S

Shawna

Hi

Is it possible to have a carriage return as test to
perform loop? i.e. Do While Not chr(13) gives me a type
mismatch error. I want to loop through some code (write
text to a table) until the start of the next line then I
need to continue to write to the same table but different
fields - I might be going about this wrong.

Sample Text:

01001,PHOS. LINE 2 2A PUMP,00,P2-2A-1087-C,2,316L
52,182.9428,219,CS,01-16-2003,0

The two lines combined make up one record in the table
(line one and line two contain separate fields of the
same record)

So far I have been able to import Line one of the code
appropriately into the table using the instr function to
find each field based on the comma delimiter. I don't
know how to loop to the second line.

Any help appreciated
 
Are you reading in an entire line of text from the file? Assuming that
you're looping through that line of text one character at a time, you could
try a loop similar to this:

Dim lngLoc As Long
Dim strLine As String, strChar As String
strLine = "Line of text from text file"
lngLoc = 1
strChar = Mid(strLine, lngLoc, 1)
Do While strChar <> Chr(13)
' put code here for what to do with the single character
lngLoc = lngLoc + 1
strChar = Mid(strLine, lngLoc, 1)
Loop

Note that the loop will end when the Chr(13) character is found.
 
Hi

I'm actually reading one line at a time right now.
I have the first line importing to record 1 to the right
tables, but when it gets to line 2 it goes to the right
table but record 2.

Sample code below with result following.

Function ImportTable()
Dim dbs As DAO.Database, rst As DAO.Recordset
'Dim Directory As String
Dim MyString As String
Dim arFields As Variant



DoCmd.SetWarnings False
DoCmd.SetWarnings True

Set dbs = CurrentDb

Open "C:\ControlNoInfo.txt" For Input As #1

' Create a dynaset-type Recordset object based on
Material table.
Set rst = dbs.OpenRecordset("tblControlNoInfo")


'Add a new Record

Do While Not EOF(1)

Line Input #1, MyString
rst.AddNew

If Left(MyString, 5) Like "#####" Then

rst!ControlNo = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!DwgRef = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!DwgRev = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!MarkNo = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!SpoolRev = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!Spec = MyString
'Write 1st line to ControlNoInfoTable
rst.Update
Else
rst!DiaInch = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!WtLb = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!Misc = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!DrawnBy = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!DrawnDate = MyString
'Write 2nd line to ControlNoInfoTable
rst.Update

End If
Loop

The resulting info basically is up until the Else
statement goes to record 1 and then after else starts
record 2 - all info go to right fields but is there a way
to force it all on one record. I just used the If Else
as I'm not sure what else to do....

Thanks for help.

Shawna
 
OK - this may be easier than you think.

Let's read both lines into a single string variable via concatenation, and
then you can manipulate as you wish.

Add another Dim to the top of the code:
Dim strLine As String

Change this line of code:
Line Input #1, MyString

to these lines of code:
Line Input #1, strLine
MyString = strLine
Line Input #1, strLine
MyString = MyString & strLine

Note that the above code will produce a single string text that is the
concatenation (without the Chr(13) character at the end of each line). Now
just parse out the data as you need to do so and write the values into your
table.
 
Thank you so much for your help, that did work. I didn't
think to concatenate the lines.

Muchly appreciated!
Shawna
 
OK - this may be easier than you think.

Let's read both lines into a single string variable via concatenation,
and then you can manipulate as you wish.

Add another Dim to the top of the code:
Dim strLine As String

Change this line of code:
Line Input #1, MyString

to these lines of code:
Line Input #1, strLine
MyString = strLine
Line Input #1, strLine
MyString = MyString & strLine

Note that the above code will produce a single string text that is the
concatenation (without the Chr(13) character at the end of each line).
Now just parse out the data as you need to do so and write the values
into your table.
Click to expand...

Now at this point you have MyString.....

Dim recInfo() as string

recInfo() = split(MyString,",")

rst!ControlNo = recInfo(0)
rst!DwgRef = recInfo(1)
rst!DwgRev = recInfo(2)
rst!MarkNo = recInfo(3)
rst!SpoolRev = recInfo(4)
rst!Spec = recInfo(5)
rst!DiaInch = recInfo(6)
rst!WtLb = recInfo(7)
rst!Misc = recInfo(8)
rst!DrawnBy = recInfo(9)
rst!DrawnDate = recInfo(10)
rst.Update


As long as the each full record has the same amount of fields in the same
order.



DS
 
OK - this may be easier than you think.

Let's read both lines into a single string variable via concatenation,
and then you can manipulate as you wish.

Add another Dim to the top of the code:
Dim strLine As String

Change this line of code:
Line Input #1, MyString

to these lines of code:
Line Input #1, strLine
MyString = strLine
Line Input #1, strLine
MyString = MyString & strLine

Note that the above code will produce a single string text that is the
concatenation (without the Chr(13) character at the end of each line).
Now just parse out the data as you need to do so and write the values
into your table.
Click to expand...

Now at this point you have MyString.....

Dim recInfo() as string

recInfo() = split(MyString,",")

rst!ControlNo = recInfo(0)
rst!DwgRef = recInfo(1)
rst!DwgRev = recInfo(2)
rst!MarkNo = recInfo(3)
rst!SpoolRev = recInfo(4)
rst!Spec = recInfo(5)
rst!DiaInch = recInfo(6)
rst!WtLb = recInfo(7)
rst!Misc = recInfo(8)
rst!DrawnBy = recInfo(9)
rst!DrawnDate = recInfo(10)
rst.Update


As long as the each full record has the same amount of fields in the same
order.



DS
 
Back
Top