Reference a cell in an access table

[owuraku]

Hi y'all
Can I reference a cell in an access table within a VBA code?? I have 1 x 2 table (1 row and 2 columns). The first column is just an autoID generator. The second column keeps track of dates. I want to write a VB code that changes the table record in row 1, column 2 of the table, tblDate. The field name is ChngDate

Here's my code:

Sub SetDate()
Dim CellDate As Date
' Assign data in cell (row 1, col 2) to CellDate
CellDate = DateValue(tblDate.ChngDate)
' Add 3 months to the CellDate
CellDate = DateAdd("m", 3, tblDate.ChngDate)
' Assign the new value back to the Table
tblDate.ChngDate = CellDate
' Begin loop when date in table cell (row 1, col 2) = current system date
Do While CellDate = Date
         ' Add 3 months to current date in table cell (row 1, col 2)
         CellDate = DateAdd("m", 3, Date)
         ' Assign the new value back to the Table
         tblDate.ChngDate = CellDate
Loop
End Sub

[owuraku]

Hi y'all
Can I reference a cell in an access table within a VBA code?? I have 1 x 2 table (1 row and 2 columns). The first column is just an autoID generator. The second column keeps track of dates. I want to write a VB code that changes the table record in row 1, column 2 of the table, tblDate. The field name is ChngDate

Here's my code:

Sub SetDate()
Dim CellDate As Date
' Assign data in cell (row 1, col 2) to CellDate
CellDate = DateValue(tblDate.ChngDate)
' Add 3 months to the CellDate
CellDate = DateAdd("m", 3, tblDate.ChngDate)
' Assign the new value back to the Table
tblDate.ChngDate = CellDate
' Begin loop when date in table cell (row 1, col 2) = current system date
Do While CellDate = Date
         ' Add 3 months to current date in table cell (row 1, col 2)
         CellDate = DateAdd("m", 3, Date)
         ' Assign the new value back to the Table
         tblDate.ChngDate = CellDate
Loop
End Sub
 

PS: tblDate.ChngDate has a starting value of "6/30/08". And I have modified the code from previous post to make question a lot clearer.

[janders468]

If your table is static, i.e. you won't be adding more rows to it, then you can just use SQL to update the date column as there is only one value to update that will be the one that gets updated. You can run the query from vba if you want it to be a vba procedure. You could also use recordsets which give you a way to reference specific row/column positions (essentially the same idea as a cell).

[owuraku]

If your table is static, i.e. you won't be adding more rows to it, then you can just use SQL to update the date column as there is only one value to update that will be the one that gets updated. You can run the query from vba if you want it to be a vba procedure. You could also use recordsets which give you a way to reference specific row/column positions (essentially the same idea as a cell).

Thanks Janders, I had thought of recordsets and SQL too. With recordsets, I havent really worked with them enough to be able to implement them into my code. As for SQL, can you tell me how I can incorporate the "DO WHILE" loop into it? I know I can do a regular UPDATE query but my problem has to do with the loop...how do I loop in a query??

PS: And yes. My table is STATIC!

[janders468]

I am trying to understand how your loop is operating, but it appears that you are telling it to update the table value as long as the date in the table value is equal to today's date. Is this correct? It seems you might be viewing the table as a spreadsheet in design, when you run a query it will update every value in that column (one value in your case). If I've totally misinterpreted what you're saying I apologize.

[owuraku]

I am trying to understand how your loop is operating, but it appears that you are telling it to update the table value as long as the date in the table value is equal to today's date. Is this correct? It seems you might be viewing the table as a spreadsheet in design, when you run a query it will update every value in that column (one value in your case). If I've totally misinterpreted what you're saying I apologize.

Here's the break down of what the table's supposed to be doing:

At Today:
The value of cell = 6/30/08 + 3 mnths = 9/30/08

At 9/30/08 (Current system date at the time):
The value of cell = 9/30/08 + 3 mnths = 12/30/08
.
.
.
.
At nth day/30/mth year:
The value of cell = n/30/m + 3 mnths = (n+3)/30/m

From the above, the table would not be due for an update until 30th August. Basically until the entry date in the table equals the existing current date, table remains unchanged.

I hope it's clear enough.

[janders468]

If my understanding is correct then you want to update this value to three months plus today if the date equals today's date, the below SQL will accomplish that. My table is called Test_tbl, and the Date Field is simply DateField. You can adjust it to the fields in your table and your table's name

UPDATE Test_tbl SET Test_tbl.DateField = DateAdd("m",3,[Test_tbl].[DateField])
WHERE (((Test_tbl.DateField)=Date()));

Let me know if that is what you are looking for.

[owuraku]

If my understanding is correct then you want to update this value to three months plus today if the date equals today's date, the below SQL will accomplish that. My table is called Test_tbl, and the Date Field is simply DateField. You can adjust it to the fields in your table and your table's name

UPDATE Test_tbl SET Test_tbl.DateField = DateAdd("m",3,[Test_tbl].[DateField])
WHERE (((Test_tbl.DateField)=Date()));

Let me know if that is what you are looking for.

Thanks Bud...
That worked!! Guess I didnt need a loop function after all. Now another problem building off of this one is that I need to run a macro based off of the date in this table. Since the table data is going to change once every 3 months, I want to write a code that starts off the macro once every 3 months. So if the table data reads 10/30/08, the macro should run on this date; and the procedure should reference the table data.

[janders468]

I didn't know if by macro you meant actual macro objects, but here is how I would approach the problem. To fire the event on open of the database you will either need to create a macro titled AutoExec (not trying to talk down if you already know all this) or from the tools...startup menu select an object to open on startup. If you choose the first option Access will run whatever is in the AutoExec Macro, if you choose the second you will have to place the relevant code in the OnOpen event of the object (probably a form) you choose to open. Either way the below function will return the result that you want.

Function CheckDate()
    Dim dte As Date
    Dim strSql As String
    dte = DLookup("DateField", "[Test_tbl]")
    strSql = "UPDATE Test_tbl SET Test_tbl.DateField = DateAdd('m',3,[Test_tbl].[DateField]) WHERE (((Test_tbl.DateField)=Date()));"
    If dte = Date Then
        DoCmd.RunSQL strSql
    End If
End Function
 

Run CheckDate either from the AutoExec macro using RunCode and this function name or run it from the Open Event of the form.
I made this a function even though it doesn't return a value because Macros will not accept Subroutines, if you run it strictly from code it can be a Sub which would be more appropriate.

[owuraku]

I didn't know if by macro you meant actual macro objects, but here is how I would approach the problem. To fire the event on open of the database you will either need to create a macro titled AutoExec (not trying to talk down if you already know all this) or from the tools...startup menu select an object to open on startup. If you choose the first option Access will run whatever is in the AutoExec Macro, if you choose the second you will have to place the relevant code in the OnOpen event of the object (probably a form) you choose to open. Either way the below function will return the result that you want.

Function CheckDate()
    Dim dte As Date
    Dim strSql As String
    dte = DLookup("DateField", "[Test_tbl]")
    strSql = "UPDATE Test_tbl SET Test_tbl.DateField = DateAdd('m',3,[Test_tbl].[DateField]) WHERE (((Test_tbl.DateField)=Date()));"
    If dte = Date Then
        DoCmd.RunSQL strSql
    End If
End Function
 

Run CheckDate either from the AutoExec macro using RunCode and this function name or run it from the Open Event of the form.
I made this a function even though it doesn't return a value because Macros will not accept Subroutines, if you run it strictly from code it can be a Sub which would be more appropriate.

Thanks Bud!!! You're gem. Although the code did not meet my exact need, it pointed me to where I wanted to get. I tweaked it a bit to serve my purpose. Here's my code:

Function RunMacro()
Dim dte As Date
dte = DLookup("[RecertDate]", "tblRecertDate")
If dte = DateValue(Now()) Then
DoCmd.RunMacro "RecertMacro"
End If
End Function
 

[NeoPa]

FYI Date() returns exactly the same value as DateValue(Now()).