Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    25

    Unanswered: Update selected record in a table using update query

    Hi,
    I want to update only the selected record in a form to a table. Please advise if it is possible.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Dim strSQL as String
    
    strSQL = "UPDATE <TableName> " & _
             "SET <Column1> = <Value1>, ..., <ColumnX> = <ValueX> " & _
             "WHERE <IdentityColumn> = <KeyValue>;"
    Currentdb.Execute strSQL, DbFailOnError
    Where <TableName> is the name of the table you want to update, <Column...> are the names of the columns (fields) you want to update and <IdentityColumn> is the name of a column that allows to uniquely identify a row (record) in the table and <KeyValue> is the value for this column. The values could be derived from controls on a form:
    Code:
    ... FirtsName = '" & Me.FirstName.Value & "'"...
    You could also use a Recordset:
    Code:
    Dim strSQL as String
    Dim rst as DAO.Recordset
    
    strSQL = " SELECT * FROM <TableName> " & _
             "WHERE <IdentityColumn> = <KeyValue>;"
    Set rst = CurrentDb.OpenRecordset(strSQL, DbOpenDynaset, DbSeeChanges)
    With rst
        If .EOF = False Then
            .Edit
            !<Column1> = <Value1>
            ....
            !<ColumnX> = <ValueX>
            .Update
            .Close
        End If
    End With
    Set rst = Nothing
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    25
    The 1st example that you've given might work. can you give me a proper example some of the terms are new to me.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Both examples perform the same operation (update a row in a table), they just use a different method to do so. If one is convenient, the other is as well.
    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
  •