Enter date when a record is modified without using a form.

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

Guest

Access 2002
Is there any way to automatically enter date and time when a record is
modifed directly in the table. I can't limit data entry to forms.

Is there a way to do this in a module? I'm thinking there might be a way to
access the Validate event for the table control directly. I know this can be
done in VB with a DataList or DataGrid bound control.
 
no, you have to do this via form, there is no way to detect data changes in
a table directly (well, you can of course query every min to check
changes...)
 
Hi, Mike.
Is there any way to automatically enter date and time when a record is
modifed directly in the table.
Click to expand...

Yes. Modern client/server databases use triggers to automatically insert a
date/time stamp after data modification. Since Jet doesn't support triggers,
you'll need to spend money purchasing a database server, such as SQL Server
or Oracle, for the back end or spend time upsizing to an Access ADP, which
supports triggers.
I can't limit data entry to forms.
Click to expand...

You underestimate your skills and cleverness. Remove all tables from the
Access database application front end and the users have zero choice but to
use the forms that you provide for data entry. Besides, Access was designed
for data entry via bound forms, so it has a rich event model available for
data validation and data manipulation. Avoid the use of forms and you might
as well build the front end with another tool and forego the RAD benefits of
Access.
Is there a way to do this in a module?
Click to expand...

As Alex mentioned elsethread, you could query the current table at specific
time intervals to compare with data saved from the last time the table was
queried in order to detect any new changes in the table. This would be
extremely inefficient, resource intensive, increase database bloat, and not
be very accurate. This is not a good way to accomplish the task. Use the
form events and form control events instead.
I'm thinking there might be a way to
access the Validate event for the table control directly.
Click to expand...

There is no table "Validate event." There is no table control that can be
manipulated with a public interface through Windows COM. Table constraints
and field constraints are checked for validation whenever the user
manipulates the data, but there is nothing you can access programmatically to
prevent, manipulate, or track these database engine processes.
I know this can be
done in VB with a DataList or DataGrid bound control.
Click to expand...

You're comparing apples and oranges. The DataList control and DataGrid
controls are ActiveX libraries built to handle recordsets of data, with
exposed Windows COM interfaces for programmatic manipulation of that data.
The table you see in Access is manipulated via the built-in database engine,
Jet, with the Jet API, which is completely proprietary and doesn't expose the
interfaces for checking table constraints and field constraints.

If you want the programmatic control of a DataList or DataGrid bound control
for a recordset, then Access is not the correct tool for you to use to build
the front end of your database application. Use VB instead, but you'll
forego the benefits of building an Access front end to your data.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
 
Hi Mike,
Try using this in the code on your form.

Private Sub FieldData_IsBeingEnteredIn_LostFocus()
Call EnterDate
End Sub

'this will be a hidden field on your form
Public Sub EnterDate()
txtDate.Value = Now()
End Sub

by doing this, anytime a user leaves the field a date will be auto populated
for you. You can set up a seperate table that will track the user(if you use
user login) and the date last modified. Just change the code to direct the
date entry to the other table instead. You can use the same LostFocus event
to send the name of the form, the user and the date changed to anywhere you
want. I hope this helps.
 
Back
Top