Default Values..

[winshent]

Hi Guys

I've just realised that i can set the date a record is created using the Default Value property on a table, by setting it to NOW()..

Now, I was wondering is it possible to capture the username of who created the record? I've always used this function when writing records to a db using recordsets..

Public Function fnOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngx As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngx = apiGetUserName(strUserName, lngLen)
    If (lngx > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

I tried setting the default value of a text field to fnOSUserName().. failed..

Is there any way of doing this? Or is this more in the realms of SQL Server.. given that access is just a file.

 

[John Big Booty]

How did the code fail? Are you aware that setting the default value for a text box will only work for new records not for existing records?

Also unless you specifically require a time component in your date use the Date() function in preference to Now() function.

 

[boblarson]

You can't set a default for data like that unfortunately. But you can set that field to the value of the function in the form's Before Update event.

 

[boblarson]

A little more clarification on what I wrote. Defaults in the table cannot be set to many different functions. They are very limited. You might be able to use it in a control's default but the solution I usually use is in the form's Before Update event as stated.

 

[winshent]

How did the code fail? Are you aware that setting the default value for a text box will only work for new records not for existing records?

Also unless you specifically require a time component in your date use the Date() function in preference to Now() function.

I implemented this at table level in the back end data file.. Yeah, i realise that will only work on new records..

If I use DATE() instead of NOW() I presume it will still occupy the same of disk space as the field is datetime?

You can't set a default for data like that unfortunately. But you can set that field to the value of the function in the form's Before Update event.

Yeah, I wanted to implement this at table level.. Basically there is a number of apps which write to various access data files on our network.. Wanted an easy way to roll out an update to capture more info about new records being created..

I plan to implement this accross all tables in all databases using DDL..

 

[boblarson]

If I use DATE() instead of NOW() I presume it will still occupy the same of disk space as the field is datetime?

It stores dates as double (part on left of decimal is day, part on right of decimal is time) so yes, that should be correct. I would use a date/time timestamp as you can always display only the date but it can be good sometimes to have the time a record went in.

Yeah, I wanted to implement this at table level.. Basically there is a number of apps which write to various access data files on our network.. Wanted an easy way to roll out an update to capture more info about new records being created..

So much for being easy, huh?

 

[John Big Booty]

Unless you specifically need a time component to your date steer well clear of the Now() function as the time component complicates the process of comparing date values. If you try to compare two values returned by Now() it is extremely unlikely that they will ever be equal, even if they where both collect on the same day.

 

[boblarson]

Unless you specifically need a time component to your date steer well clear of the Now() function as the time component complicates the process of comparing date values. If you try to compare two values returned by Now() it is extremely unlikely that they will ever be equal, even if they where both collect on the same day.

Not a real problem if you use DateValue() around the field.

 

[John Big Booty]

Fair point, but then why store something you don't need in the first place?

 

[boblarson]

Fair point, but then why store something you don't need in the first place?

I'm not saying they need to store it but that they should consider if they potentially could need it later on down the line. If there is any possibility they should include it as it is easier to include it and then not use it than it is to go convert everything later. But if it is never going to be needed, then don't use the time. I frequently don't need the time. But when a record is created, that I usually store the date and the time for along with who created it. The issue can be whether one record was created before another and could have affected some decision but if you go back you can't tell whether it was there or not - just that it happened sometime that day.

 

[John Big Booty]

Point taken

 

[winshent]

Hi Guys

Thanks for all your input.. I'll be capturing date and time.. makes sense to me if there is no compromise on disk space.. and it will be useful..

It stores dates as double (part on left of decimal is day, part on right of decimal is time) so yes, that should be correct. I would use a date/time timestamp as you can always display only the date but it can be good sometimes to have the time a record went in.



So much for being easy, huh?

Hopefully something like this will work.. Although need to add some handling for db's which are protected.. Should work though..

  ' Uses a table named 'Databases', with field 'Path'..
  Dim db As DAO.Database, rs As DAO.Recordset
  Dim dbExt As DAO.Database, t As DAO.TableDef
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("Databases", dbOpenDynaset)
  
  rs.MoveFirst
  Do Until rs.EOF
    If FileExists(rs!path) Then
      Set dbExt = DBEngine.OpenDatabase(rs!path)
      
      For Each t In dbExt.TableDefs
        dbExt.Execute "ALTER TABLE " & t.Name & " ADD COLUMN RecordCreatedDate DATE DEFAULT Now()"
      Next t
      
    End If
  Loop

EDIT: The above code doesn't work.. you have to use ADO if you want to Create/Alter a column with a default value..