Creating an automatic date change with data entry updates

[Jen_ConfusedAboutAccess]

Please forgive my lack of knowledge on writing code?? I'm trying to have an
automatic date change when data is changed within a field. I created the date
file that I named Update. I tried to follow the following instructions and
created the code below. However, it is not working and no changes can be
made to the either the table or form at the present time!
Can anyone suggest another way to do this or tell me what I'm doing wrong?

Your help is greatly appreciated...It is so frustrating to get stuck!

http://office.microsoft.com/en-us/access/HA010345351033.aspx?pid=CL100570041033

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date.
Update = Date

BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub

 

[Albert D. Kallal]

Do you want to see this updated "date" field set when any place on the form
editing occurs,
or *JUST when ONE* particular value is change on the form?

' Set bound controls to system date.
Update = Date

The above is all the code you need. I would code the above as follows:

me!Update = date

So, your final code should look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

me!Update = date

End Sub

You might actually try chaning the name of the Field "upate" you're using.
The reason is that "update" is a very generic term, and it also the name of
a command that ms-access uses.

By the way when you run your code now and edit some data on a form, what
kind of error message are you seeing?

Anway, try the above sample code...and see how it goes:

 

[Jen_ConfusedAboutAccess]

Thank you so much...I changed the field name and used your code as a guide
and it works great! You have just saved me from entering so many dates!!!
Thanks a million - really appreciate your time.
Jen

 

[Jen_ConfusedAboutAccess]

I just realised that this programming only works in the form. Is there any
way to have this work when entering directly in a table? I find it easier
sometimes to add contacts directly in the table because I copy and paste an
entry of a contact that works at the same place and just change the few
values that differ.

Thanks again for your help,

Jen

 

[Albert D. Kallal]

I just realised that this programming only works in the form. Is there any
way to have this work when entering directly in a table? I find it easier
sometimes to add contacts directly in the table because I copy and paste
an
entry of a contact that works at the same place and just change the few
values that differ.

No, not really. As a general rule most developers NEVER let users see a
table directly.

as for cut+ paste, if you use the relational abilities of ms-access, you
wind up with ONE company address, and "many" people that work at that
address. If the company changes it address, then you ONLY have to change the
one address, and all users company address then be instantly updated because
there is only ONE copy of the company address, and it is *related* to
employees.

If you design the form correctly, it will be MUCH less work (and typing)
then editing a table. Furthermoe, a table has no verification, no sub forms,
no list boxes, no code that can run to automatic update and do other types
of things for the user.

There's no question that we will often display the data in a table or column
type format, but it still suggested that you build a form to display this
information in that format. Take a look at the following screen shots, and
you'll see that I use actual forms to display the a table or, like formats.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Jen_ConfusedAboutAccess]

Thanks again - I figured that you were going to say no...I will have to learn
a little more about forms and your examples will help with that! I'm
realising quickly that my knowledge of Access is very little but it's really
exciting what you can do with this program!

Thanks,
Jen

 

[Evi]

What about creating a Datasheet form?. They behave very similarly to tables
except they can be coded.

Evi