Results 1 to 9 of 9
  1. #1
    tryggis is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    7

    make the loop

    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.

    my code look like this:

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






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

    If totallenght > 200 Then
    strsql = " INSERT INTO Recordset2 (recordset2value) VALUES (200)"
    CurrentDb.Execute (strsql)
    totallength = totallength - 200

    Else
    strsql = "INSERT INTO Recordset2 (recordset2value) VALUES ( & totallength & )"
    CurrentDb.Execute (strsql)
    totallength = 0
    .MoveNext
    End If
    Loop
    .Close
    End With







    Call MsgBox("the record is updated")
    End Sub
    Last edited by tryggis; 10-07-2017 at 07:48 AM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, this is invalid:

    If >= 0 Then

    You don't have anything there to test (if what is greater than or equal to 0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    tryggis is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    7
    oh sorry it dissapeard when i was copy/pasting

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So what exactly is the problem? Something else I see is that the MoveNext should probably be outside the If/Then block. As is, it only executes if the Else clause executes, so you might get caught in a never-ending loop. I assume this is also a typo: "VALUSES". I'd think you could simply use an append query with an IIf() to handle the values greater than 200.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This is probably closer to what you need (untested):
    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 !recordset1value is negative, it will be set to zero. Problem?
    
        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 davegri; 10-06-2017 at 04:43 PM. Reason: some syntax

  6. #6
    tryggis is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    7
    hmmm it seems like the solution davegri doesn`t insert any value to the second recordset.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code tested and corrected. Works OK for me now.

    Code:
    Option Compare Database
    Option Explicit
    
    
    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
            nLength = 0
            If !recordset1value >= 0 Then
                nLength = !recordset1value
            End If
    ' if !recordset1value is negative, it will be set to zero. Problem?
    
    
        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

  8. #8
    tryggis is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    7
    thank you soo much1 your a savior !!!!!!!

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Glad to help. Good luck with the rest of your project!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. How to make a loop in VB
    By darwin in forum Modules
    Replies: 5
    Last Post: 04-21-2015, 04:17 PM
  4. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  5. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM

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
  •  
Other Forums: Microsoft Office Forums