Modify Table Field Not Using a Control?

[Peter S.]

I would like to record when a user modifies a record by recording the date a
record was changed in a field I am calling ModifiedDate. Can this be
accomplished in an event without having to put the date in a control that is
tied to this field?

Can I directly change values in a table with an Access command or does
everything have to be done from a control? I guess I could populate a hidden
label that is tied to this field and populate it but was wondering if there
was a way to do this directly? Any pointers would be greatly appreciated!

 

[Alex Dybenko]

Hi,
you can use form's BeforeUpdate event to record modification date:

me.ModifiedDate=Now

textbox with ModifiedDate can be hidden (visible=false)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Peter S.]

I figured that this was how I could do it however can it be done WITHOUT
having a hidden textbox? Does VB allow you to access fields directly?

 

[Klatuu]

You can do it without having the field on the form, but it should be in the
form's recordset:

With Me.Recordset
.Edit
![ModifiedDate] = Now
.Update
End With

 

[Peter S.]

That's *GREAT*! The only problem is that I get an Run-time error 3426 at the
".Edit" line. I put the code in the before update event so I would think it
shouldn't be a problem?

You can do it without having the field on the form, but it should be in the
form's recordset:

With Me.Recordset
.Edit
![ModifiedDate] = Now
.Update
End With

--
Dave Hargis, Microsoft Access MVP

I figured that this was how I could do it however can it be done WITHOUT
having a hidden textbox? Does VB allow you to access fields directly?

 

[Klatuu]

Move it to the After Update event. I tested it there and it worked.
--
Dave Hargis, Microsoft Access MVP

That's *GREAT*! The only problem is that I get an Run-time error 3426 at the
".Edit" line. I put the code in the before update event so I would think it
shouldn't be a problem?

You can do it without having the field on the form, but it should be in the
form's recordset:

With Me.Recordset
.Edit
![ModifiedDate] = Now
.Update
End With

--
Dave Hargis, Microsoft Access MVP

I figured that this was how I could do it however can it be done WITHOUT
having a hidden textbox? Does VB allow you to access fields directly?

:

Hi,
you can use form's BeforeUpdate event to record modification date:

me.ModifiedDate=Now

textbox with ModifiedDate can be hidden (visible=false)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I would like to record when a user modifies a record by recording the date
a
record was changed in a field I am calling ModifiedDate. Can this be
accomplished in an event without having to put the date in a control that
is
tied to this field?

Can I directly change values in a table with an Access command or does
everything have to be done from a control? I guess I could populate a
hidden
label that is tied to this field and populate it but was wondering if
there
was a way to do this directly? Any pointers would be greatly appreciated!

 

[Albert D. Kallal]

That's *GREAT*! The only problem is that I get an Run-time error 3426 at
the
".Edit" line. I put the code in the before update event so I would think
it
shouldn't be a problem?

Actually, if you not placing a control on the form, then use:

me!ModifiedDate = date

or

me!ModifiedDate = now (now will log date + time).

me.ModifiedDate will often fail in code, especially if your changing or
setting the forms recordset via code.

So, if your having a problem with using me.ModifedDate...use the ! (bang) in
place of . (dot).

Also, make sure your code compiles before you run it.

If your code fails with using the "bang", then It is likely or possible that
your form is based on a query, and you added this new field to the table,
but is not updated the query that the form is based on. While you don't
actually have to place all the controls on the form to use the underlying
record set, the query that the form is based on must include all fields that
you intented to use in code....

So, once again you don't have to place the text box on the form to get at
the data in the table, but you should use the bang operator in place of.if
the "dot" operator if the forms record source is being changed at runtime.

 

[Peter S.]

Using Me!ModifiedDate = NOW in the BeforeUpdate did the trick, EXCELLENT
thanks!

 

[Klatuu]

Well, I learned something today, too.
As I wrote it, it worked in the After Update event, why when the OP used the
Before Update, I don't know, unless it was the Form Before Upate.