Automatically Update Another Field After Data Editing?

[Guest]

Hello:

I use another database program where do what I want do for my co-worker is a
piece of cake via easy to use Field Rules. However, I'm not as familiar with
Access 2003, but I suspect it will also be relatively easy to do the same.

Here are the relevant fields in a co-workers database:

[Uploaded] - Yes/No
[Upload_Date] - Date/Time

There are 20,000 plus records of stored documents in the table, most of
which haven't yet been scanned and uploaded to the server.

When they scan and upload a document to the server, they will manually
change the value of Uploaded from “No†to "Yes", and then they want Access
2003 to automatically enter today's date into the currently blank
[Upload_Date] field after the value of [Uploaded] is changed from "NO" to
"YES".

Thanks for your help,
Robert

 

[Marshall Barton]

I use another database program where do what I want do for my co-worker is a
piece of cake via easy to use Field Rules. However, I'm not as familiar with
Access 2003, but I suspect it will also be relatively easy to do the same.

Here are the relevant fields in a co-workers database:

[Uploaded] - Yes/No
[Upload_Date] - Date/Time

There are 20,000 plus records of stored documents in the table, most of
which haven't yet been scanned and uploaded to the server.

When they scan and upload a document to the server, they will manually
change the value of Uploaded from “No” to "Yes", and then they want Access
2003 to automatically enter today's date into the currently blank
[Upload_Date] field after the value of [Uploaded] is changed from "NO" to
"YES".

Use the Uploaded control's AfterUpdate event procedure to
set the date field:
Me.[Upload_Date] = Now

 

[Guest]

Hi Marshall:

Thanks, that looks pretty simple but that brings up another question.

Is there a way to ensure the same result if a user isn't on the data entry
form when they enter the data?

Thanks,
Robert

 

[Marshall Barton]

Is there a way to ensure the same result if a user isn't on the data entry
form when they enter the data?

What do you mean? Are you allowing users to edit data
directly in a table's (or a query's) sheet view? If so,
that would be a huge mistake.

 

[Guest]

Hello Marshall:

Let me start by saying I truly appreciate your help.

Second, this is not my database. It is being used by a co-worker so I have
little or no control over it. I would think that all data entry/edits are
made from a form, but I don't have specific knowlege of such, so I was
curious if there was way to use Access to control data integrity independent
of a data entry form.

Third, I use another PC database program that gives developers a choice to
create Field Rules that are very powerful because they are independent of
forms. For example, if the table described in this question was a child in
another relationship, such as a One to Many Set, users could enter/edit data
directly on the screen. In other words, the same table can be used in more
than one area. With Field Rules, developers can ensure data integrity
independent of forms. In my opinion, that's very powerful stuff.

And just like Access, it also gives you the option of setting up form based
rules as you demonstrated for this question. And that's great if it's the one
and only way users can enter/edit data in a table.

In sum Marshall, I was curious. Once again, I truly appreciate your help and
expertise with Access.

Robert

 

[Marshall Barton]

Let me start by saying I truly appreciate your help.

Second, this is not my database. It is being used by a co-worker so I have
little or no control over it. I would think that all data entry/edits are
made from a form, but I don't have specific knowlege of such, so I was
curious if there was way to use Access to control data integrity independent
of a data entry form.

Third, I use another PC database program that gives developers a choice to
create Field Rules that are very powerful because they are independent of
forms. For example, if the table described in this question was a child in
another relationship, such as a One to Many Set, users could enter/edit data
directly on the screen. In other words, the same table can be used in more
than one area. With Field Rules, developers can ensure data integrity
independent of forms. In my opinion, that's very powerful stuff.

And just like Access, it also gives you the option of setting up form based
rules as you demonstrated for this question. And that's great if it's the one
and only way users can enter/edit data in a table.

In sum Marshall, I was curious. Once again, I truly appreciate your help and
expertise with Access.

Well Access provides Data Validation **expressions** at both
the field level and the table level. It can also enforce
Referential Integrity on defined Relationships.

Access does not have the equivalent of Triggers that would
run a procedure when the trigger's conditions are met. This
kind of hook is required to assign a value to one field when
another field is changed.

Bottom line is that data entry/edit must be done through a
form to get the effect that you wanted.