Default values in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

We have a staff database with primitive user level security which basically
consists of several fields that default to the user's initials and stamp the
time that the record was created/updated.

This works fine for us using forms however when records are updated using
queries we do not currently capture this information. How can we configure
the queries to capture this information?

Ian
 
Access (JET) does not provide fields to track who created/modified reach
record and when. You need to provide this information yourself, and you can
only do it if the data is updated through Access forms (not if it is updated
directly in table/query.)

Did you just want to track the *last* date of change? Or did you want a
complete trail of who changed the record when from what and to what?

For just the last change, use the BeforeUpdate event of the form to write
the user's initials and the value of Now() to the 2 fields you created for
this purpose.

If you wanted full loggint, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
 
Ian

Queries are ... SQL statements. An alternative to running a query is
running a procedure with the SQL statement in it. Note that you can modify
the SQL string to include the fields and values you now use field defaults
to set.

Another approach might be to create functions that "gather" that information
from the form's fields, and use those functions to come up with the query's
field values. An example might be (untested aircode):

Function ThisUser() as String
ThisUser = Forms!YourFormName!YourUserField
End Function
 
and you can only do it if the data is updated through Access forms
(not if it is updated directly in table/query.)
Click to expand...

A minor quibble:
You can also supply the data when you are dealing with *stored* update
queries. ChangedOn and ChangedBy fields can easily be included in the
update. (Likewise, stored append queries can include AddedOn and AddedBy
fields.)

Create a function similar to fosUserName() at
http://www.mvps.org/access/api/api0008.htm .
fosUserName() can then be used to provide the value for ChangedBy/AddedBy
fields in stored update/append queries.
Now() can be used for ChangedOn/AddedOn fields.

but, there is no way to force users to include those fields in ad hoc update
queries (assuming you allow them to make ad hoc update queries!). However,
if you make AddedBy a required field, users would have to include the field
for any ad hoc appends to succeed.

HTH,
 
Completey agreed, George.

If you have added the fields to the table, you can supply the value for
those fields in any Append or Update query.
 
Back
Top