[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

UPDATED TIME STAMP MS ACCESS

I need to incoprorate an update function in my access data base anytime an individual record is modified.   It will require time and date.   Any suggestions?
0
BOEING39
Asked:
BOEING39
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I usually have four fields in every table that users enter data:

dt_created
txt_created_by
dt_modified
txt_modified_by

For the two date fields I'll have a default value of GetDate(), and for the txt_by's I'll call a function that grabs the windows login id of the user.

If you want any more details let me know.

Good luck.
Jim
0
 
peter57rCommented:
In an Access table Jim uses Now() rather than GetDate()  (which only works in SQL Server).
0
 
Dale FyeCommented:
As stated above, add a dtCreated or dtModified field to your table.  

In the dtCreated, set the default value to: = Now()

In the BeforeUpdate event of the form you are using to edit the table, add a line of code that updates the dtModified field.
Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.txt_dtModified = Now()
    
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Both of the above comments are correct.

Also, fyi, below is the function (although somewhat old) to get the Windows login user

Put this in the declaraction section of any code module

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Open in new window


Then add this function

Public Function fn_get_user_name() As String

'Returns the network ID of the person that logged into the machine that is running this Access application:
'This is not the same as the Access security user.

'Visual Basic Source Code Library, The Waite group, Chapter 12.5

On Error GoTo err_fn_get_user_name

Dim str_user_name As String, lng_buffer_size As Long, lng_ret_code As Long, lng_null_character_position As Long

str_user_name = Space(80)
lng_buffer_size = Len(str_user_name)

lng_ret_code = GetUserName(str_user_name, lng_buffer_size)

lng_null_character_position = InStr(str_user_name, Chr(0))
If lng_null_character_position > 0 Then
    str_user_name = Left(str_user_name, lng_null_character_position - 1)
Else
    str_user_name = " "
End If

fn_get_user_name = UCase(str_user_name)

exit_fn_get_user_name:
    Exit Function
    
err_fn_get_user_name:
    fn_get_user_name = "Not able to get user name."
    Resume exit_fn_get_user_name
    
End Function

Open in new window


Then building off of fyed's code above, also set txt_modified_by in the BeforeUpdate event.
Also add a BeforeInsert block, and set txt_created_by with ... me.txt_created_by = fn_get_user_name.
0
 
Jeffrey CoachmanCommented:
No points wanted...

In case your concern is "Auditing", ...see these popular links:
http://support.microsoft.com/kb/197592
http://allenbrowne.com/appaudit.html
0
 
BOEING39Author Commented:
Thx everyone for the responses.   Ok I created a new column in my Data base "DatetimeMod" which is where I want to store the date time stamp @ update and set the default value to "Now().    This particular page is a Details view that is hyperlinked off my main page.   Can someone advise on the placement of the above code on the attached "Home3.aspx".  


I did not use code behind on this page as it is just a details page.
Home3.aspx
0
 
Dale FyeCommented:
I've got no experience with asp, your original post indicates Access database, not ASP.
0
 
BOEING39Author Commented:
The two responses listed were closest answers too the category I selected.   Moving question to C# as I need assistance with code behind to fully resolve.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now