have a problem with loop an inserting it to a recordset. (1 Viewer)

tryggis

New member
Local time
Today, 02:48
Joined
Oct 7, 2017
Messages
1
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.

CODE:
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:48
Joined
Feb 19, 2013
Messages
16,553
you never assign anything to nLength

recommend you put Option Explicit at the top of all modules which would pick up this sort of error - and when posting code, use the code tags (the # button) to preserve indenting which will make it much easier for people to understand
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:48
Joined
Aug 30, 2003
Messages
36,118
Approved both posts. Hasn't this been solved elsewhere?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,175
Chk your spelling of nLength, there are two of them.
 

Users who are viewing this thread

Top Bottom