[RESOLVED] add a value to a certain table in MS access

[Asher David]

Hi,

Can anyone kindly help me how to possibly change/add a value in a specific table using VBA code in MS access. Let say row1,column1 of table1, I want to put a text value of "test" when the date become Jan 19, 2007.

In excel I know how to put value since we just have to refer to Sheet Name and cell location. How about in Ms access table using VBA code?

Thanks!

[dmaruca]

Tables are different in Access than they are in Excel. You'll have to update row values using SQL or by iterating through rows in a RecordSet object.

Method 1:

Const SQL as String = "UPDATE MyTable SET Value1=""THIS"" WHERE Value2=""That"";"
 
Currentdb.Execute SQL, vbFailOnError

Method 2:

Const SQL as String = "SELECT Value1 FROM MyTable WHERE Value2=""That"";"
Dim rst as RecordSet
 
Set rst = CurrentDB.OpenRecordSet(SQL)
Do Until rst.EOF
  If rst!Value1 = "This" Then
    rst!Value2 = "New Value"
  End If
Loop

[dmaruca]

Make sure you have a reference to Microsoft DAO Object selected under Tools->Reference

[dmaruca]

Dangit, I botched the 2nd method...

Const SQL as String = "SELECT Value1, Value2 FROM MyTable WHERE Value2=""That"";"
Dim rst as RecordSet
 
Set rst = CurrentDB.OpenRecordSet(SQL)
Do Until rst.EOF
  If rst!Value1 = "This" Then
    rst.Edit
    rst!Value2 = "New Value"
    rst.Update
  End If
Loop

[Asher David]

Many many thanks dmaruca!!! I really appreciate your help...I really picture out what I will do with my code.

Just clarification in the code you gave me.since im new with this... on your last code posted....

What value1 and value2 represent? is it the column name? Am i right? Is there a way that after the code assign a new value to Value2 then it cannot be change anymore? Like the value will be locked?

[dmaruca]

value1 and value2 are the column names, yes. if any of your names have a space, you'll have to put brackets around them.. [value 1] [value 2]

I'm not too experienced with record locking, so I'm not the person to come to for that.

Good luck with your code.

[Asher David]

Anyway!

Thanks a lot for the code!!!