Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Update records based on value on from

    I would like to update a field for all selected records (from a list box) based on the value on from

    This is what I have; can you please help me with the rs.Edit lien? Or any other line that’s wrong.

    Thanks

    Private Sub cmdupdate_Click()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant

    On Error GoTo ErrorHandler

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblOrder", dbOpenDynaset, dbAppendOnly)


    Set ctl = Me.List144
    For Each varItem In ctl.ItemsSelected
    rs.Edit????????????
    rs!InternalID = ctl.ItemData(varItem)
    rs!InvoiceNo = Me.txtInvoiceNumber
    rs.Update
    Next varItem

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You'd have to move the recordset to each item inside the loop. Personally I'd ditch the recordset and run SQL:

    db.Execute "UPDATE...WHERE KeyFieldName = " & ctl.ItemData(varItem)
    Paul

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    Good idea of using db.Execute.
    I never did it. Can you please tell me how sql should look starting from db.Execute?
    Thanks

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's one of the results of a Google search:

    http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx
    Paul

  5. #5
    Join Date
    Jan 2004
    Posts
    115
    I have spent lot of time to figure this out. I would very appropriate if you can put the full code the I need to have. I'm not good on this.
    i have posted this few days go in diff group, but didnt get answer yet.
    Thanks

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Along the lines of

    CurrentDb.Execute "UPDATE tblOrder SET InvoiceNo = " & Me.txtInvoiceNumber & " WHERE InternalID = " & ctl.ItemData(varItem)

    If my guess about your structure is correct.
    Paul

Posting Permissions

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