By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

update record in a table

P: 1
Hi, I am new to Access. I want to know how to update a particular record in a table. Ex. if there are 20 records in a table, and I want to update 6th record. How will I do it.
Sep 7 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 126
Hi, I am new to Access. I want to know how to update a particular record in a table. Ex. if there are 20 records in a table, and I want to update 6th record. How will I do it.
Do you always want to update the nth record on the table? Or do you want to update a record with, say, a particular employeeID?

1.
Expand|Select|Wrap|Line Numbers
  1. Dim rs as DAO.RecordSet
  2. Set rs = DBEngine(0)(0).openRecordSet("SELECT * FROM YOURTABLENAME;)
  3. Dim i As Integer
  4. i = 0
  5. Do While i < 5 and NOT rs.EOF
  6.     i = i + 1
  7.     rs.MoveNext
  8. Loop
  9. If Not RS.EOF Then DoCmd.runSQL("UPDATE YOURTABLENAME SET updatedfield = 'newvalue' WHERE PRIMARYKEY = '" & rs!PRIMARYKEY & ";")
  10.  
In this example, and the following one, replace PRIMARYKEY with the field name of the primary key in the table, YOURTABLENAME with the name of the table, updatedfield with the field you want to update, and newvalue with its new value.
Expand|Select|Wrap|Line Numbers
  1. If you want to update a particular field due to some criteria (2), then:
  2. DoCmd.runSQL("UPDATE YOURTABLENAME SET updatedfield = 'newvalue' WHERE PRIMARYKEY = '" & yourCriteriaHere & ";")
  3.  
Sep 7 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.