Looping Through Text File Question

[alexanderjames]

Hi All

Just a question on capturing the data that i need from looping through a text file.
I need to find a couple of records and then store them into an access db,
so in my limited vb knowledge i have come up with this idea.

Dim N As Integer
Do While Not rs.EOF
For N = 0 To rs.Fields.Count - 1
    If rs.Fields(N).Value = "^CASH" Then
          Text2.Text = rs.Fields(N).Value '<<<< store in text box "CASH"
          Text3.Text = rs.Fields(N + 3).Value ' <<< store in text box "$25.00
          Exit For
    End If
Next N
rs.MoveNext
Loop

to store the data, found from the loop into text boxes and then update the database from the data in the textbox.
But i need to pick out about 40 records ie. "Credit Card", "Total Sales" etc and this would mean that i would need
alot of invisible text boxes, so my question is, is this the best way to achieve this or is there a better way.

Many thanks

[smUX]

Why use textboxes? Why don't you use an array? If you're using a lot of subs and the variable isn't available in them all, try using a module and dimming an array as public...so if you wanted to dim data(40) you would put "public data(40)" and it would make the variable available anywhere in the program

[alexanderjames]

Hi Smux

Thank you for your reply, it is a small app with not many subs at all, it reads a comma delimited text file, i then have to loop through it just putting the data that i need into a db. I have never tried coding what you have suggested, could you point me to a tutorial or give some guidelines please.

Many thanks

[Al42]

Your code is looping through the database. Your words say that you have to loop through a text file. Assuming the latter is true, do something like:

Dim s As string, strSQL as String
'assume conn is your database connection
 
Open "myTextFile.txt" For Input As #1
While Not EOF(1)
  s = Line Input #1
  'get your transaction type and amount out of s here
  strSQL = "Insert Into <table name> (TransactionType, Amount) Values('" & <transaction type from text file> & "'[COLOR=Black], " &  <amount from text file>[/COLOR]
  conn.Execute strSQL
Wend