 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006updating table with VBA
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 [Table] SET [Field]=[Value] WHERE Condition;" Docmd.RunSQL stringSQL >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 [quoted text clipped - 7 lines] > >The first part works, but not the second. Thanks. 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, [quoted text clipped - 15 lines] > > > >The first part works, but not the second. Thanks. 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. >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 [quoted text clipped - 5 lines] >> > >> >The first part works, but not the second. Thanks. 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 > 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 [quoted text clipped - 19 lines] > > > > > >The first part works, but not the second. Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.