updating table with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a StartDate and EndDate field (in a table) for a project. I would
like to take the year out of the dates and have that go into the field
StartYear and EndYear. I can get the year out alright, my problem is even
more basic. How do I get the year back into the table?

This is what I have been trying...


SY=Year(StartDate)
Mytable.StartYear=SY

The first part works, but not the second. Thanks.
 
Assuming Mytable is a correctly defined recordset, the correct syntax is
MyTable!StartYear = SY. However, I suggest you do this with an update query,
especially if you don't have to go through each record one by one for some
reason:

stringSQL = "UPDATE
SET [Field]=[Value] WHERE Condition;"
Docmd.RunSQL stringSQL
 
Thanks. Is there any way that you can use VBA to directly update a table.
For instance, at the end of a series of computations done in code, is there a
direct way to add that final value to a table?

kingston via AccessMonster.com said:
Assuming Mytable is a correctly defined recordset, the correct syntax is
MyTable!StartYear = SY. However, I suggest you do this with an update query,
especially if you don't have to go through each record one by one for some
reason:

stringSQL = "UPDATE
SET [Field]=[Value] WHERE Condition;"
Docmd.RunSQL stringSQL

stumped said:
I have a StartDate and EndDate field (in a table) for a project. I would
like to take the year out of the dates and have that go into the field
StartYear and EndYear. I can get the year out alright, my problem is even
more basic. How do I get the year back into the table?

This is what I have been trying...

SY=Year(StartDate)
Mytable.StartYear=SY

The first part works, but not the second. Thanks.
Click to expand...
Click to expand...
 
Yes, there are many ways to do this depending on the context. For example,
you can create a recordset object and set the field like: Recordset!
Field=Value; look up Recordset Property in Help for examples. Or if you're
doing this in a form, include the field as a control and set the control
value: Me.Control=Value. Or you can formulate an SQL statement to update 1
record in the table and run it.

stumped said:
Thanks. Is there any way that you can use VBA to directly update a table.
For instance, at the end of a series of computations done in code, is there a
direct way to add that final value to a table?
Assuming Mytable is a correctly defined recordset, the correct syntax is
MyTable!StartYear = SY. However, I suggest you do this with an update query,
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
Here's an example:

Dim MyTable As Recordset, MyDb As Database
Dim Var1 As String

Set MyDb = CurrentDb
Set MyTable = MyDb.OpenRecordset("SomeTableName")

With MyTable
..AddNew
!Field1 = Var1
..Update
End With

stumped beginner said:
Thanks. Is there any way that you can use VBA to directly update a table.
For instance, at the end of a series of computations done in code, is there a
direct way to add that final value to a table?

kingston via AccessMonster.com said:
Assuming Mytable is a correctly defined recordset, the correct syntax is
MyTable!StartYear = SY. However, I suggest you do this with an update query,
especially if you don't have to go through each record one by one for some
reason:

stringSQL = "UPDATE
SET [Field]=[Value] WHERE Condition;"
Docmd.RunSQL stringSQL

stumped said:
I have a StartDate and EndDate field (in a table) for a project. I would
like to take the year out of the dates and have that go into the field
StartYear and EndYear. I can get the year out alright, my problem is even
more basic. How do I get the year back into the table?

This is what I have been trying...

SY=Year(StartDate)
Mytable.StartYear=SY

The first part works, but not the second. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top