Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2011
    Posts
    24

    Unanswered: do while loop update all records does not work

    I have a simple question:

    I want to create a simple update query in a form.
    My idea is something like this:


    Code:
    Dim DB As Database
    Dim RSValues As DAO.Recordset
    
    Set DB = CurrentDb()
    Set RSValues = DB.OpenRecordset("TABLEname", dbOpenDynaset, dbSeeChanges)
    
    RSValues.MoveLast
    RSValues.MoveFirst
    
    Do while Not RSValues.EOF
    
    if format(date1, "mm") > format(date2, "mm") then
    Currency1.value = Currency2.value
    else
    Currency1.value = Currency2.value
    End If
    
    Loop
    I don't understand howto make sure I can update each record, cos the Do While Loop stays at record number 1. . .

    Can someone help? Thanx!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    add RSValues.movenext between End If and Loop to advance to the next record

  3. #3
    Join Date
    Mar 2011
    Posts
    24
    AHA! So that was the problem. Thanx. I 'll try it.

    I have tried RS.MoveNext, but it stays at the first record
    Last edited by Engelienart; 03-11-11 at 08:46. Reason: It didnt work :(

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I'm surprised that you didn't get an error, there doesn't appear to be a variable 'RS' defined.

    I also noticed that you seem to be doing the same thing regardless of whether your if statement returns true of false (Currency1.value = Currency2.value
    ).

  5. #5
    Join Date
    Mar 2011
    Posts
    24
    Quote Originally Posted by rogue View Post
    I'm surprised that you didn't get an error, there doesn't appear to be a variable 'RS' defined.

    I also noticed that you seem to be doing the same thing regardless of whether your if statement returns true of false (Currency1.value = Currency2.value
    ).

    Oops should be:

    if format(date1, "mm") > format(date2, "mm") then
    Currency1.value = Currency2.value
    else
    Currency1.value = "0"
    End If

    I have the correct if statement in the VBA
    I also have a msgbox (recordID)
    And a counter that goes to 5
    the msgbox (recordID) returns 5 times a: "number 1"

    So I know for sure it doesnt go to the next record.

  6. #6
    Join Date
    Mar 2011
    Posts
    24
    the total code is here:

    Code:
    Private Sub UpdateRecords()
        On Error Resume Next
        Dim DB As Database
        Dim counter As Integer
        Dim varType As String
        
        counter = 0
    
        Dim RSValues As DAO.Recordset
        
        Set DB = CurrentDb()
        Set RSValues = DB.OpenRecordset("Table", dbOpenDynaset, dbSeeChanges)
        
        RSValues.MoveLast
        RSValues.MoveFirst
        
        Do While Not RSValues.EOF And counter < 5
             varType = "txtType" & counter
        
            If IsNull(DateBet) Then
                TKD.Value = KD.Value
                KE.Value = "0"
            Else
                If Format(DateBet, "yyyy") > Format(Date, "yyyy") Then
                    If Format(DateBet, "mm") > Format(Date, "mm") Then
                        TKD.Value = "0"
                        KE.Value = (KD.Value * "1,19")
                    Else
                        TKD.Value = KD.Value
                        KE.Value = "0"
                    End If
                Else
                    TKD.Value = KD.Value
                    KE.Value = "0"
                End If
            End If
            
    
            counter = counter + 1
            MsgBox (RecordID)
        RSValues.MoveNext
        Loop
    
        MsgBox "Finished", vbInformation, "Whatever your app is called"
    End Sub

  7. #7
    Join Date
    Mar 2011
    Posts
    24
    I have tested the code some more:

    I have added a 'discontinuance point' (F9)

    The "Set DB = CurrentDb()" returns: "Nothing"

    So the code doesnt know what databse I have? Maybe someone knows what this means?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can this not be doen as a simple updatwe query, instead of writing a chunk of VBcode?
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2011
    Posts
    24
    Update Query? I have no idea. I will look into it.

  10. #10
    Join Date
    Mar 2011
    Posts
    24
    I have looked at how to create an updatequery. I have no idea how it works.
    I tried some, but I cant make the updatequery update field2 with the value of field1.
    For as far as I can tell, a do while loop, works better. Because I can juggle with values form differend fields.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Update Query:
    Code:
    Dim strSQL as String
    strSQL = "UPDATE <TableName> SET <Field1> = <Value1>, <Field2> = <Value2>, ... <FieldN> = <ValueN> WHERE <Condition>;
    CurrentDB.Execute strSQL, dbFailOnError + dbSeeChanges
    Have a nice day!

  12. #12
    Join Date
    Mar 2011
    Posts
    24
    ah okay, that looks better I will tell how it turned out.

  13. #13
    Join Date
    Mar 2011
    Posts
    24
    Okay I understand the code for a update query.
    I had a question but:

    nvm I am stupid Sorry
    I will post what I have made in the near future

    Everybody: Thanx for your help
    Last edited by Engelienart; 03-17-11 at 07:23.

  14. #14
    Join Date
    Mar 2011
    Posts
    24
    Now I have a question:

    I get an error:

    Error 3061
    Too few parameters. Expected 2

    My code looks like this:

    Code:
        strSQL = "UPDATE table SET TKD = KD.value, TKE = KE.value WHERE date = #01-01-11#;"
        CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
    table is tablename
    TKD, KD, TKE and KE are currency fields
    date is a date field
    Last edited by Engelienart; 03-17-11 at 07:44.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not convinced you need + dbSeeChanges....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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