Date/Time stamp in table.

[PsYc0TiC]

Okay... I have a database that multiple users access on the network. I do not have it split and it is too late to deal with that.

Moving on...

I have been manually looking in the table to try to track data changes but it is too hard now so I decided to add date/time stamps to the records to help.

I currently track the userid's of the people updating records as well as for the few who are allowed to add new records.

I added the date/time stamp to the new record entry with no problem right in the field of the table. I already had the field in the table for that and had a field in the form where I would manually choose today's date. I just removed the field from the form and set the default value to the table field "=Now()". works perfect.

The new test record then has 2 different groups that would update that record with different data using 2 different forms. I added in 2 more fields "date/time" in the table called "VisUpdate" and LabUpdate" and set their default values to "=Now()" and added in the "Before_Update" of each ones input form this: in the visual data input form I put "Me! [VisUpdate] = Now()" and in the lab data input form I put "Me! [LabUpdate] = Now()".

When I add a new test record the date/time stamp works for that one.
When I update that test record using the Visual data input form it adds the date and time of that data update perfect.
When I try to do the second (last update of the record) data update on the test record I get a "compile error: syntax error" popup and it highlights the "Me! [LabUpdate] = Now()" line int he before_update for that form.

I need help understanding why it works in the visual data input form but doesn't work in the lab data update form.

Can you only have this feature once in a record?

 

[Joe4]

Try changing your references to LabUpdate from this:

Me![LabUpdate]

to this:

Me.LabUpdate

and see if that makes any difference.

 

[PsYc0TiC]

Try changing your references to LabUpdate from this:

Me![LabUpdate]

to this:

Me.LabUpdate

and see if that makes any difference.

I get a compile error "Method or data member not found"

 

[Joe4]

So, the Form is bound to your Table, right?
Are you actually displaying the "LabUpdate" field on the Form (using "Me." only works if the field is on the Form)?
Note that if you do not want this field to be visible on the Form, just add it to the Form and change its Visible property to "No". That will allow you to reference it while not showing it.

 

[PsYc0TiC]

Maybe a bit more information.

I have 3 forms.

Form 1: adds a new record (row) to the table and populates 5 fields out of 20 (1 of which is a date/time stamp for when I created the record (row))and sets one of the fields to a certain status ready for Form 2. Only I have access to this form and only I can add new records.
Form 2: is for adding data to 8 more empty fields in the row and changes the status the previous form set to a status ready for form 3.
Form 3: is for adding the remaining data to the remaining 5 fields and changes the status used in the previous 2 forms to "complete".

The remaining 2 fields are for date/time stamps.

Field 19 is for the date/time stamp of the person that filled out their part in form 2
Field 20 is for the date/time stamp of the person that filled out their part in form 3

All of that is designed for 3 different departments to record their data parts on one project. A project is a single row of data.

The date/time stamp works for me when I start the new record because I have a field in the table called "RecEntryDate" and I put "=Now()" in the properties of that field.
The date/time stamp works for Form 2 because I have a field in the table called "VisUpdate" and the code on the first page of this thread in the "BeforeUpdate" area.
The date/time stamp does not work for Form 3 even though I have a field in the table called "LabUpdate" and the code on the first page of this thread exactly the same format as for Form 2 but with the correct field name for that data.

I suspect this might be the issue but I am lost on the solution.

I do not display the LabUpdate field on the form nor hidden... I don't display it nor hidden on the VisUpdate either but it works

I have been reading exhaustively on the subject but nowhere can I find info on multiple date/time stamps in 1 record (row).

 

[Joe4]

I do not display the LabUpdate field on the form nor hidden...

Why not try adding it and make it not Visible (like I described), and see if that solves your error?

 

[PsYc0TiC]

Why not try adding it and make it not Visible (like I described), and see if that solves your error?

Crazy as it sounded... that worked. Strange I didn't have to do that to Form 2

Thank you so much for your help... I really appreciate it.

 

[Joe4]

Crazy as it sounded... that worked.

Doesn't sound crazy at all to me, that is what I expected to happen!

Strange I didn't have to do that to Form 2

Without seeing the design of Form 2 and VBA code behind it, I really cannot say why it worked.

Thank you so much for your help... I really appreciate it.

You are welcome.

 

[xenou]

I noticed the extra space in your original post but put it up to the way the forum was displaying it ... my bad. You do need the form field to be "in your form" but it doesn't necessarily have to be "on the form" (visible or invisible). It just needs to be part of the forms record source. However, I find that adding it to the form and making it visible is a quick way to debug something like this (you can literally see what is happening).

 

[Micron]

Microsoft Access can't find the field 'LabUpdate' referred to in your expression.

What comes to mind is 3 things.
- since you overlooked the space at first, it's possible that there is a subtle 1 character deviation between what's in your code and the name of the field.
- what you see when looking at the table is a field caption and the field name is different. If you look at query design, you'll see the field name but I think you're looking at the table in datasheet view. Query datasheet view will show the caption.
- this probably isn't it, but I'd never write Me![LabUpdate] as per post 2. It would be Me.LabUpdate (the square brackets don't matter unless there are spaces or special characters in the name). Me! and Me. should work for forms and reports when referring to controls, but you're taking a chance if the reference is to something else. The reason I'd never write that is because it will only allow late bound access to the object in question. If you mistype the control name, it will not be caught when the code is compiled. The error can only be discovered when it is referenced at run time. To prove this, create a small form with a textbox on it (probably will be named Text0) and choose any event. Type msgbox Me.Text0 and compile. Then change the line to Me!Text0 and compile again. Should be no problem. Then change to Me!Text00 and compile - no problem. Change to Me.Text00 and compile and it will fail.

If you change your code to Me.LabUpdate wherever you have Me!LabUpdate and compile, if the code name isn't the same as the control, the compile will let you know.
I also don't allow form controls to have the same name as table or query fields, which is what happens when you use a form wizard. I rename all the controls after this. While this is usually to avoid "Ambiguous Name Detected", I suspect (but don't know) that using ! for form controls might be an issue when the field names are the same. Here's the best reference I have for bang ! vs dot.
http://bytecomb.com/the-bang-exclamation-operator-in-vba/