Results 1 to 2 of 2

Thread: Access : Update field in table for a selected item

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Access : Update field in table for a selected item

    I have a list box on a form which is having unique values read from a table. WHen a user clicks on a row in the list box then I would like a field (of 'yes/no' type and called 'refresh') to have it's value set to Yes.

    Best to use VBA or call a query or .. ?

    Thanks.

  2. #2
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: Access : Update field in table for a selected item

    I prefer doing it in VBA - you have more control over various things...

    Here is a code that queries a "Training Dates" table, and returns records that match "TestDate" with the selection in a listbox, then it updates the field named "NewDate" to today's date.

    I typed some of this freehand, so you might have to correct some things. It's also good to do some error handling, and checking to ensure their is a record (or if it returns multiple records...)


    Code:
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset
        Dim sql1 As String
        
        sql1 = "select * from [Training Dates] Where [TestDate] = " & """" & me.listbox.value & """"
        Set db = CurrentDb
        Set rs1 = db.OpenRecordset(sql1)
        
        rs1.MoveFirst
                 
        rs1.Edit
        rs1![NewDate] = Now()
        rs1.Update
            
        rs1.Close
        Set rs1 = Nothing
        db.Close
        Set db = Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width