For Loop - Access Developer Forum - Computer Learning Zone


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

For Loop

Joe Hood

5 years ago

Below is the code that I am using to add records to a table from a multiselect list box. When I display the the selected items in the status box it seems to be working just fine it displays the information for each record selected. However when adding the records to the table with the SQL statement it adds the first record the same number of times as the items selected. Can anyone tell me what I am doing wrong?

Dim S As String
    Dim X As Long

    StatusBox = ""

    With PaymentList

        S = "INSERT INTO VehicleExpenseT (ExpenseDate, ExpenseAmount, ReportingMonth, ReportingYear, ExpenseTypeID, AssignmentID, VehicleID) " & _
            " VALUES (ApplyDate, " & .Column(2) & ", Month, Year, ExpenseTypeCombo,  " & .Column(3) & ", " & .Column(0) & ")"

        For X = 0 To .ListCount - 1
            If .Selected(X) Then
                Status X & ": " & .Column(1, X) & " " & .Column(0, X)
                DoCmd.RunSQL S
            End If
        Next
    End With

Thanks
Joe

	Alex Hedley @Reply 5 years ago
Your `INSERT` statement isn't inside your `FOR` loop

	Richard Rost @Reply 5 years ago
Technically it is, but you're not UPDATING it's value inside the loop, so it's adding the same record over and over again. Move your S= line right above the RunSQL line.

Joe Hood OP @Reply

5 years ago

Hello Guys,
I tried moving the Insert statement inside the Loop and ended up with the same result. I finally used variables to set the (.Column values) before the SQL statement, then used the variables in the statement. Works fine now. If you get a chance take a look at the code and let me know your thoughts.

Funny thing is that the button that uses a Record set to add all the payments at once was much easier to write and works Great!

Thanks again for your help!
Joe

Joe Hood OP @Reply

5 years ago

Dim S As String
    Dim X As Long
    Dim W As String
    Dim Y As String
    Dim Z As String

    StatusBox = ""

    DoCmd.SetWarnings False
    With PaymentList
        For X = 0 To .ListCount - 1
            If .Selected(X) Then
                W = .Column(0, X)
                Y = .Column(2, X)
                Z = .Column(3, X)
                Status "Payment for Truck No. " & .Column(1, X) & " has been made!"
                S = "INSERT INTO VehicleExpenseT (ExpenseDate, ExpenseAmount, ReportingMonth, ReportingYear, ExpenseTypeID, AssignmentID, VehicleID) " & _
                        " VALUES (ApplyDate, " & Y & ", Month, Year, ExpenseTypeCombo,  " & Z & ", " & W & ")"
                DoCmd.RunSQL S
            End If
        Next
    End With
    DoCmd.SetWarnings True

	Richard Rost @Reply 5 years ago
Oh yeah, I didn't catch that you were missing X from your statement. That's the row. Hence, the same record. :)

	Joe Hood OP @Reply 5 years ago
LOL! Got it, now it all makes sense. VBA seems so simple, right up to the point where you start writing actual code. Went back to the original code, added the (X) and it works perfectly fine. Thanks Again!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View