Problem with adding records

[tryggis]

so i am reading a texfile where there are two different values. i have to read the file and split the values. as you can see my condition is " ". anyway i want to insert one of the values into a recordset. And in the recordset is testnumber alredy entered in the recordset. so i only have to add "strekkfasthet however i want to add "strekkfasthet" inn to the recordset. the two valuse must go together. how can i do that? i know it is some kind of an if sentence i have to use. but when i try "stdset!testnr" is stuck on the first recordvalue. plis help me!!!


Option Compare Database


Private Sub Command0_Click()


Dim filsti, linje As String
Dim filnr As Long
Dim tempArray() As String
Dim strsql As String
Dim stdset As DAO.Recordset
Dim testnr As Long
Dim strekkfasthet As Double




filsti = CurrentProject.Path & "\data.txt "
filnr = FreeFile
Open filsti For Input As #filnr


Do While Not EOF(filnr)
Line Input #filnr, linje
tempArray = Split(linje, " ")

MsgBox (tempArray(1))



strsql = " SELECT * FROM test"
Set stdset = CurrentDb.OpenRecordset(strsql)


If stdset!testnr = tempArray(0) Then


strsql = " DELETE testnr from test"
CurrentDb.Execute (strsql)


stdset.AddNew
stdset!testnr = tempArray(0)
stdset!strekkfasthet = tempArray(1)
stdset.Update


Else
Call MsgBox("the testnumber doesnt exist")
End If




Loop
Close #filnr


End Sub

[pbaldy]

You're opening the recordset on the whole table, so you'll always get the first record. Sounds like you want to add a criteria to the SQL statement that uses the array value, and test for EOF.

[tryggis]

well i understand that the stdset!testnr is not "dynamic". but when i try to write .eof it all goes bad. i can`t just use stdset.movenext and move up to the next textvalues. the i find it hard to understand how to do it

[pbaldy]

I'm not clear on what you're trying to do. You realize this empties the table?

strsql = " DELETE strekkfasthet from test"
CurrentDb.Execute (strsql)

So the second time through, there's nothing there so your code will error.

[tryggis]

sorry it was supposed to be :
strsql= "DELETE testnr from test"
CurrentDB.Execute(strsql)

the record witch i intend to insert the two different numbers into has the "testnr" already.

[pbaldy]

Irrelevant; without a WHERE clause, the table will be emptied. Something like:

strsql= "DELETE * from test WHERE testnr = " & tempArray(0)

Of course I don't know if those are the correct fields/values.