Post date of database design update

  • Thread starter Thread starter Sammie
  • Start date Start date
S

Sammie

I have an unbound form which loads when I open my database.
It contains only command buttons leading to main forms on
my database (like a switchboard). I would like to
automatically post the date of my last database design
update on this page. How can I do this?
 
Well, since Access doesn't have this date (as far as I know), you will have to
store it someplace. Also, you will have to be responsible for initiating the
action to store it.

You can either store the date in a field in a table in the database or as a
custom property. I use the following two functions to create, modify, and
retrieve a date as a custom property.

Public Sub sSetVersionDate(Optional dteDate As Date)

Dim db As DAO.Database
Dim prpNew As DAO.Property
Dim errLoop As Error

If dteDate = CDate(0) Then dteDate = Date

Set db = CurrentDb()
' Attempt to set the property.
On Error GoTo Err_sSetVersionDate
db.Properties("VersionDate") = dteDate
On Error GoTo 0

Exit Sub

Err_sSetVersionDate:

' Error 3270 means that the property was not found.
If DBEngine.Errors(0).Number = 3270 Then

' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = db.CreateProperty("VersionDate", _
dbDate, dteDate)
db.Properties.Append prpNew
Resume Next
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

End Sub

Public Function fGetVersionDate()

fGetVersionDate = CurrentDb().Properties("VersionDate")

End Function
 
I'm not familiar with custom properties. Do I put this
code in my public module? Can I put a command button on my
opening form to send the modified date to a text box? What
is the code for this? Thanks.
Sammie
 
Put the code in a standard code module.

Call sSetVersionDate to create the property. You can do that in the immediate
window, or you can build a form with a button to call the code.

To get the value just use something like

SomeVariable = fGetVersionDate()

or you can just set the Control source of a control
ControlSource: =fGetVersionDate()

In other words, any place you can use the built-in function Date(), you can use
the function fGetVersionDate().
 
Works! Thank you. Will I need to Call sSetVersionDate
again the next time I change the database design, or will
it be automatic?
Thanks. Sammie
 
As I said originally, you have to call the procedure. There is no automatic
function to do this. I'm sure that with a lot of code someone smarter than I
could make it automatic (and they might be able to do it with a little code).
 
Back
Top