Consulting

Results 1 to 7 of 7

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

  1. #1
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    1
    Location

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

    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
    Last edited by Bob Phillips; 10-07-2017 at 09:27 AM. Reason: Added code tags

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Check the spelling of the Variable
    nLength
    you have used in some of the code

    nLenght

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Without the unnecessary bits
        With stdset 
            Do While Not .EOF 
                    nLenght = !recordset1value Mod 200
    'Blah blah blah
    Loop
    'Blah blah blah
    End with
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    DELIBERATELY
    I read what you wrote
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK.

  7. #7
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    One simple insert query would do.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •