Validation Rule - Date change update record

[ChryslerKelly]

I'm trying to set up a validation rule that would require a person to update
a date field and initial field in the form if changes were made to the
record.

Thank you

 

[George Nicholson]

If its required, then why not take it out of the user's hands and simply do
it for them?

Fewer keystrokes for data entry = happy users.

In Form_BeforeUpdate:
Me.ChangedOn = Now()
Me.ChangedBy = fosUserName

fosUserName is the user's Network Login ID as per:
http://mvps.org/access/api/api0008.htm

 

[Jeanette Cunningham]

Hi Chrysler,
assuming a bound form,
in the before update event of the form try something like this:

Me.txtTheDateField = Now()
Me.txtTheInitialField = """ & TheInitial & """

Jeanette Cunningham

 

[ChryslerKelly]

This is exactly what I'm looking for (actually I could use this in another
portion of my form as well), except it didn't work. I suspect it's not
working because it's part of a subform. Are there different arguments
required when updating a subform?

Should I put this code on the main form as well?

Thanks

 

[George Nicholson]

except it didn't work
meaning? Error message? nothing happened? machine crashed?

Should I put this code on the main form as well?

Which form has ChangedOn and ChangedBy fields (or their equivalents) in its
recordsource? That's where it belongs. If that means the Parent form, then
you might need to make the parent's Form_BeforeUpdate Public and call it
from the subform's BeforeUpdate.

 

[Pat Hartman]

Be sure that you put it in the FORM's BeforeUpdate event and make sure that
your column names are spelled properly.

 

[ChryslerKelly]

Nothing happened. It just happily went to the next record without blinking
an eye.

Changed on/by is in the subform. I put the code in the subform, but nothing
happens. The name of the fields that must be changed are Date Updated (AH)
and Initials (AH).

 

[George Nicholson]

Well, if the BeforeUpdate property of the subform is set to EventProcedure
and the code is placed within that procedure/event (with your correct field
names), then I'm not sure why it isn't working *when a change is made to the
subform*.

 

[ChryslerKelly]

George,

Thanks so much for your help. The date portion works fine. The only
problem I have now is with the fosusername module. Once I save that module,
for some reason, my queries start giving me compile errors.

Any idea?

 

[ChryslerKelly]

More:

When I try to update, it takes me to here:

Public Sub Form_BeforeUpdate(Cancel As Integer)
Me.Date_Updated__AH_ = Date
Me.Initials__AH_ = fOSUserName
End Sub

And I get an error message that says:

"Compile Error:

Expected variable or procedure, not module

I'm going to get this working yet.

 

[George Nicholson]

ahh, fosUserName needs to be in a general code module. A general code module
is one that is *not* attached to a Form, Report or other class object.

- in the VB Editor: Insert>Module. In the Properties window, rename the
module to anything *except* fosUserName. "modGeneralCode" will do.
(The module name cannnot be the same as any of its procedures/functions. Not
100% sure, but it sounds like this is the current problem, in which case,
you probably only need to rename the module.)

- copy fosUserName (and the API call) into modGeneralCode. Delete them from
anywhere else you might have put them.

Should compile now.

 

[ChryslerKelly]

George,

Thank you so much...it worked! It took me all day, but I finally got it.

Now I have about ten other things I need to get working, but this one was
BIG! Thanks again!

Kelly