Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: Update Multiple Records in VBA

    Can anyone tell me why updating a single record works using...

    Code:
    rs.Open "SELECT * FROM personaldetails", con, adOpenDynamic, adLockOptimistic
    
        rs.AddNew
            rs("FirstName") = Me.FirstName
            rs("LastName") = Me.LastName
            rs("Email") = Me.Email
        rs.Update
        
    rs.Close
    but doesn't work when trying to update multiple records...

    Code:
    rs.Open "SELECT * FROM personaldetails", con, adOpenDynamic, adLockOptimistic
    
    Do While Not rs.EOF
        rs.AddNew
            rs("FirstName") = Me.FirstName
            rs("LastName") = Me.LastName
            rs("Email") = Me.Email
        rs.Update
        rs.MoveNext
    Loop
        
    rs.Close
    All I'm adding is a 'Do While' loop and asking it to iterate through the records.

    I don't get an error of any kind - it just doesn't work.

    Thanks for any info.

  2. #2
    Join Date
    Jan 2011
    Posts
    8
    ignore previous - I've worked it out.


  3. #3
    Join Date
    Mar 2011
    Posts
    24

    sollution?

    Hi,

    Can you post the sollution too?

    Thanx!

  4. #4
    Join Date
    Jan 2011
    Posts
    8
    I was looping through the wrong recordset in the 'Do While' loop.

    Before I'd only created a local recordset instead of local and remote.

    I needed to loop through the local recordset adding the records to the remote one...

    Code:
    Do While Not rs_Local.EOF
            rs_Remote.AddNew
                rs_Remote("---") = rs_Local("---")             
            rs_Remote.Update
            rs_Local.MoveNext
        Loop
    Simple as that - hope it helped.

  5. #5
    Join Date
    Mar 2011
    Posts
    24
    Thanx for your quick reply. Nice work

  6. #6
    Join Date
    Mar 2011
    Posts
    24
    I still have some questions if you please:

    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 you help? Thanx!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First your code does exactly the same thing whether the test on the dates returns True or False (it sets Currency1.Value equal to Currency2.Value in both cases), I suppose it's a typo:
    Code:
    if format(date1, "mm") > format(date2, "mm") then
    Currency1.value = Currency2.value
    else
    Currency1.value = Currency2.value
    Second, you forgot a MoveNext instruction in the loop:
    Code:
        Dim DB As DAO.Database
        Dim RSValues As DAO.Recordset
        
        Set DB = CurrentDb
        Set RSValues = DB.OpenRecordset("TABLEname", dbOpenDynaset, dbSeeChanges)
        With RSValues
            Do While Not .EOF    
                If Format(Date1, "mm") > Format(Date2, "mm") Then
                    Currency1.Value = Currency2.Value
                Else
                    Currency1.Value = Currency2.Value
                End If
            .MoveNext
            Loop
            .Close
        End With
    Moreover Date1, Date2, Currency1 and Currency2 are not fully referenced: are they field of the recordset? (in this case you have to use a dot (.) or a bang (!) operator: RSValues!Currency1, etc...), are they controls on a form? (Me.Currency1.Value = ...).

    Third, if Date1, Date2, Currency1 and Currency2 are columns of the table, this kind of operation would be processed more efficiently using SQL:
    Code:
    Currentdb.Execute "UPDATE <TableName> SET <Column1> = <Column2> WHERE DatePart('mm', Date1) > DatePart('mm', Date2);", dbFailOnError + dbSeeChanges
    Have a nice day!

Posting Permissions

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