i have a problem with a task involving looping and inserting it to a recordset

[tryggis]

hi i am a beginner in VBA.
i have a double loop where the loop check if a number is higher then 200. but it seams like when the loop ends, and it does`nt take the value up to the start of the loop.
can anybody help me?


my task is to select values from a recordset, and then insert it in another recordset. but the values inserted in the new recordset cant exceed over the value 200. if there are values over 200 in the first recordset, then the loop is gonna subtact 200 each time.



Private Sub comBeregn_Click()
Dim strsql As String
Dim stdset As DAO.Recordset
Dim nLength As Long

strsql = "SELECT * FROM 1strecordset"
Set stdset = CurrentDb.OpenRecordset(strsql)
stdset.movelast
stdset.movefirst
With stdset
Do While Not .EOF
nLenght = 0
if !recordset1value >= 0 Then
nLenght = !recordset1value
End If

If nLength > 200 Then
nLength = nLength - 200
end if
strsql = "INSERT INTO Recordset2 (recordset2value) VALUES (" & nLength & ")"
CurrentDb.Execute (strsql),dbfailonerror
.MoveNext
Loop
.Close
set stdset = nothing
End With
end sub

[OBP]

Check the spelling of the Variable
nLength
you have used in some of the code

nLenght

[SamT]

Without the unnecessary bits

With stdset
Do While Not .EOF
nLenght = !recordset1value Mod 200
'Blah blah blah
Loop
'Blah blah blah
End with

[OBP]

SamT, please read what I wrote.
The Variable Dimensioned is
nLength As Long

The variable used in this line, which you have repeated

nLenght = !recordset1value Mod 200

is nLenght

ie misspelt

[SamT]

DELIBERATELY
I read what you wrote

[OBP]

OK. :beerchug:

[jonh]

One simple insert query would do.


currentdb.execute _
"INSERT INTO Recordset2 ( recordset2value ) " & _
"SELECT Len([recordset1value]) - 200 " & _
"FROM 1strecordset " & _
"WHERE Len([recordset1value]) > 200 "