Hi Deek,
ready for some code?
when the main form of your database opens, you will need to make sure
that the database properties are set. If you create a database by
importing objects, the database properties do not come along, so it is
good to have a default value.
On the property sheet for the OPEN event of your main form:
=SetDefaultDatabaseProperties(true)
then, in a general module:
'~~~~~~~~~~~~~~~~~~~~~ SetDatabaseProperties
Public Function SetDefaultDatabaseProperties( _
Optional bSkipMsg As Boolean = True)
If Not IsPropertyDefined("DefaultUserID") Then
Set_Property "DefaultUserID", dbLong, -1, False
End If
If Not bSkipMsg Then MsgBox "Default Database Properties are set" _
, , "Done"
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
two functions, IsPropertyDefined and Set_Property, are called -- and I
will give you that code later in this message
If you have a FE/BE database where each user has their own FE, then each
user can be assigned a long integer UserID. The tables will all have
the following tracking fields (I put them at the bottom of the structure
and also have code to add them automatically if they are missing):
datAdd, date/time, Description --> date/time record was added
datEdit, date/time, Description --> date/time record was edited
IDadd, long, Description --> User who added record
IDedit, long, Description --> User who last edited record
If the user id NOT defined, I use -1 for the userID. If you have a
table of Users with an Autonumber ID, chances are that the UserID will
be a positive value.
Use an Append query to add -1 as the UserID for a non-defined user once
other records are already in. You can also use zero (0) -- but I like
-1 since it is never there unless you specifically put it there.
~~~
so how do you add the tracking fields to the tables?
datAdd is easy ... define a Default Value
=Now()
for the field in the table design
Whenever you create a record, it will automatically be filled out with
the current date and time
~~~
the other tracking fields are updated on the form BeforeUpdate event
(and you should never put records into a table directly unless you are a
developer and you know what you are doing <smile>). Here is what you
put in the property sheet for the BeforeUpdate event of the FORM:
=FormBeforeUpdate([Form])
and here is code that goes in a general module:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FormBeforeUpdate
Public Function FormBeforeUpdate(pF As Form _
, Optional bSetParentToo As Boolean = False)
'7-8-08
On Error GoTo Proc_Err
If bSetParentToo Then
pF.Parent.datEdit = Now()
pF.Parent!IDedit _
= CurrentDb.Properties("DefaultUserID")
End If
If pF.NewRecord Then
'Date Added has a default value in the table definition
pF!IDadd _
= CurrentDb.Properties("DefaultUserID")
Exit Function
End If
pF!datEdit = Now()
pF!IDedit _
= CurrentDb.Properties("DefaultUserID")
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " FormBeforeUpdate"
Resume Proc_Exit
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
all records that are being changed are processed by the form
BeforeUpdate event -- that gives you a chance to validate required
fields and Cancel the update. It also gives you an opportunity to fill
tracking fields, such as is done here.
If you have record validation, you can call this procedure from code
behind the form using:
FormBeforeUpdate Me
If the record is a NewRecord, then only IDadd will be filled out
If the record is NOT a new record, meaning it is being modified, then
datEdit and IDedit will be filled out.
~~~
there is an optional parameter in the general code to also update the
main form if you are on a subform.
~~~~
what else you need in the general module are these procedures:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' this is a generic public function to see if
' a property is defined
'
'~~~~~~~~~~~~~~~~~~~~~ IsPropertyDefined
Public Function IsPropertyDefined( _
ByVal pPropName As String _
, Optional obj As Object _
) As Boolean
'Crystal (strive4peace2008 at yahoo.com)
'
'PARAMETERS
' Obj can be a database, a Tabledef, a Field...
' if it is missing, CurrentDb is used
'
On Error GoTo Proc_Err
IsPropertyDefined = False
Dim prp As dao.Property
If obj Is Nothing Then
Set obj = CurrentDb
End If
For Each prp In obj.Properties
If prp.Name = pPropName Then
IsPropertyDefined = True
GoTo Proc_Exit
End If
Next prp
Proc_Exit:
Set prp = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' this is a generic public function to
' define a property
'
'~~~~~~~~~~~~~~~~~~~~~ Set_Property
Public Function Set_Property( _
pPropName As String _
, pPropType As Long _
, pValue As Variant _
, Optional pSayMessage As Boolean = True _
, Optional obj As Object _
) As Byte
'Crystal (strive4peace2008 at yahoo.com)
'set up Error Handler
On Error GoTo Proc_Err
If obj Is Nothing Then
Set obj = CurrentDb
End If
obj.Properties.Append obj.CreateProperty( _
pPropName, pPropType, pValue)
If pSayMessage Then
MsgBox pPropName & " is " _
& obj.Properties(pPropName) _
& " for " & obj.Name, , "Done"
End If
Proc_Exit:
Exit Function
Proc_Err:
'property is already defined
If Err.Number = 3367 Then
obj.Properties(pPropName) = pValue
Resume Proc_Exit
End If
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " Set_Property"
Resume Proc_Exit
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~
you will need to reference a DAO Library for this code to work.
Tools, References... from a module window
If a DAO library is not checked off at the top of the list, scroll to a
DAO Library (like Microsoft DAO 3.6 Library) and check it
---------- Compile ----------
Whenever you write or paste code, your should ALWAYS compile it before
you attempt to run it.
from the menu: Debug, Compile
fix any errors on the yellow highlighted lines
keep compiling until nothing happens (this is good!)
**********************************************************
*** How to Create a Standard (General) Module ***
1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in
once the code is in the module sheet, from the menu, do -->
Debug,Compile
if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>
Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc
~~~~~~~~~~~~~~~~~`
when you set up a FE for a specific user, assign the DefaultUserID
property for the database. You can press CTRL-G to Goto the debuG
(Immediate) window. Type the following statement:
Set_Property "DefaultUserID", dbLong, 215, True
and press ENTER
WHERE
Set_Property is the procedure name you are running
DefaultUserID is the name of the property you are setting
dbLong means it is stored as a Long Integer
215 is the value (Assuming this user has been assigned a UserID of 215)
True to give a confirmation message that the property is set
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
