Loop doesn't loop :(

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

I don't know what's wrong with it ...data is enough in the table

line always has the same value

Do While Not rs.EOF
mat = Chr$(34) & rs("Material") & Chr$(34)

use= rs("Use") & ","
If IsNull(use) Then
use= ""
Else
use= Chr$(34) & rs("Use") & Chr$(34)
End If

line= "'" & mat & "','" & use& "'"
Debug.Print line
Loop


please help

Gina
 
Gina,

You forgot to include:

rs.MoveNext

as the last line in your loop, so you are always working on the first
record, and never exiting the loop!
Also, there's another bug in your code:
use= rs("Use") & ","
If IsNull(use) Then
Click to expand...

use will never be null, since the previous line makes sure it will be at
least use = ",", even if rs("Use") is Null - which I guess is what you
intended in the first place?

HTH,
Nikos
 
Hi Nikos, yes you are right!!!

I forgot it ... how embarrassing ....

but now I am strung up in a different thing .... I can export the stuff ...
thanks to the working loop now ;-)
my table has 3 fields: text, text, currency
write to file:
_______________
Do While Not rs.EOF
mat = Chr$(34) & rs("Material") & Chr$(34)

If Not IsNull(rs("Use")) Then
use= Chr$(34) & rs("Use") & Chr$(34)
Else
use= ""
End If

If Not IsNull(rs("Price")) Then
price= Chr$(34) & rs("Price") & Chr$(34)
Else
price= ""
End If
line= "'" & mat & "','" & use& "' ,'" & price& "'"
Print #1, line
rs.MoveNext
Loop
_______________

one line in the file now looks like : '"myMaterial"','"myMaterial
work"','"10"'


read from file to import:
--> ERROR: 1 parameter was expected, too few params ...etc (runtime err
3061)
______________
'SQL = "INSERT INTO tbl_MaterialList(Material, Use, Price) VALUES (mat, use,
price)" <--- here I got error 3 parameters expected ... too few parameters

SQL = "INSERT INTO tblMaterialList VALUES (line)" <-- now I changed it to
this one
Open CurrentProject.Path & "\" & DT & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, line
Debug.Print line
--> db.Execute SQL, dbFailOnError
Loop
Close #1
______________

do I have to split up the line to single strings ?
is something wrong with the way I write it (currency maybe) to the file ?
(changed it around before I run the directly above code - no positive result
though)

maybe again you have the right and highly appreciated solution for me !?!??
Gina
 
Gina,

You need to split the line after import. Also, since you'll be working
line by line, code execution will be more efficient if you employ a
recordset operation than by running an Append query for each line. Try
something like:

Sub Import_Text_File()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vInputFile As String
Dim vInputLine As String
Dim vFileds As Variant

vInputFile = "c:\MyFolder\MyFile.txt"

Set db = CurrentDb
Set rst = db.OpenRecordset("TargetTable")

Open vImputFile For Input As #1
Do Until EOF(1)
Line Input #1, vInputLine
vFields = Split(vInputLine, ",")
rst.AddNew
For i = 0 To 2
rst.Fields(i) = vFields(i)
Next
rst.Update
Loop

Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Finally, if you have no compelling reason to enclose all fields in
quotes then don't, it will only make life harder (for instance, you will
need to use a Val() function on the currency field). This also greatly
simplifies the export code (snippet), which then becomes:

Do While Not rs.EOF
line= rs("Material") & "," & rs("Use") & "," & rs("Price")
Print #1, line
rs.MoveNext
Loop

HTH,
Nikos
 
Nikos .... wow ... I was struggeling around and around and in the meantime
used that split func as well.

somehow it works .... but not correctly

your code- on the other hand - looks extremely good to me
I wonder whether the rst:Update is a real update of a recordset (most
presumably not) in case a record has been edited and a part. field should
just be updated.

therefore I do these 2 db.Execute strSQL_Upd & strSQL_Ins, dbFailOnError =
False
even if it is bad style it should work anyhow ?!? ...

or should I make a coffe break and try it again your way ???

think I am going to be mad by this evening !!!
Gina

_____________________________

Do While Not EOF(1)
Line Input #1, line
arWords = Split(line, vbTab)
mat = arWords(0)
use= arWords(1)
'If use= "" Then use= mat <-- gives me an error if verw = empty
so changed it to mat to be error free :-((
price= arWords(2)
If IsNull(price) Then price= "0"

strSQL_Upd = "UPDATE tbl_MaterialList SET
tbl_MaterialList.Material = '" & mat & "', tbl_MaterialList.Use= '" & use&
"', tbl_MaterialList.Price= '" & price& "' WHERE (tbl_MaterialList.Material
= " & mat & ")"
Debug.Print "UP " & strSQL_Upd
strSQL_Ins = "INSERT INTO tbl_MaterialListe (Material,
Verwendung, Einzelpreis) VALUES (" & mat & ", " & use& ", " & price& ")"
Debug.Print "INS " & strSQL_Ins
db.Execute strSQL_Upd, dbFailOnError = False
db.Execute strSQL_Ins, dbFailOnError = False
Loop
____________________________
 
I am amending from german ... so
strSQL_Ins = "INSERT INTO tbl_MaterialList (Material,
Use, Price) VALUES (" & mat & ", " & use& ", " & price& ")"

well .... I cannot say what it is but for some reason I get "X" quotes
around the inserted / updated fields values ....

maybe 'evening' is already now for me ;-)

I set a vbTab as a delimiter as in germany we use a comma as a decimal
separator ...

Gina
 
Gina,

See below.

Regards,
Nikos
Nikos .... wow ... I was struggeling around and around and in the meantime
used that split func as well.

somehow it works .... but not correctly

your code- on the other hand - looks extremely good to me
I wonder whether the rst:Update is a real update of a recordset (most
presumably not) in case a record has been edited and a part. field should
just be updated.
Click to expand...
My code actually adds a new record; don't let rst.Update confuse you, it
is not the same as in Update vs. Append query, it just means "save" the
current record in the recordset, regardless of whether in was a new one
or just an edited existing one.

therefore I do these 2 db.Execute strSQL_Upd & strSQL_Ins, dbFailOnError =
False
even if it is bad style it should work anyhow ?!? ...
Click to expand...
I see now what you are trying to do. Hadn't realized that before. You
can still do it through a recordset operation like:

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tbl_MaterialList")
Open "YourFile" For Input As #1
Do While Not EOF(1)
Line Input #1, line
arWords = Split(line, vbTab)
mat = arWords(0)
use= arWords(1)
If use= "" Then use= mat
price= arWords(2)
If IsNull(price) Then price= 0 'no quotes for a currency field!
rst.FindFirst "Material = '" & mat & "'"
If rst.NoMatch Then
rst.Addnew
rst.Fields("Material") = mat
Else
rst.Edit
EndIf
rst.Fields("Use") = use
rst.Fields("Price") = price
rst.Update
Loop
rst.Close
set rst = Nothing
Set db = Nothing

Your approach should work fine but less efficiently, as it is like
opening two recordsets for each line imported.
 
Thanks Nikos ..... so much

you know I really had doubts re these delimiter stuff .... one thing I
changed now was to not export them with Chr(34) & var & Chr(34)
and now it works .... and now I am up too do it properly and as you say
motre efficiently .... I'll going to do it according to your latest code !!!
big big ... very big thanks for your assistance .... alone at your pc ...
noone around who can help .... I wanted to bite into the desk earlier this
day ;-)

very grateful
cheers Gina
..... until my next problems arise ;-))
 
Welcome! Glad it works.
Thanks Nikos ..... so much

you know I really had doubts re these delimiter stuff .... one thing I
changed now was to not export them with Chr(34) & var & Chr(34)
and now it works .... and now I am up too do it properly and as you say
motre efficiently .... I'll going to do it according to your latest code !!!
big big ... very big thanks for your assistance .... alone at your pc ...
noone around who can help .... I wanted to bite into the desk earlier this
day ;-)

very grateful
cheers Gina
.... until my next problems arise ;-))
Click to expand...
 
Back
Top