TRACK CHANGES WITH USERNAME & TIME STAMP

[ARW17]

Hi! I have a table that I put into a subform so users can fill out portions of the data. The user will review the work and write in who is responsible, the credit category, cause, and notes. I want to hide the TINITIALS (username) and STAMP (time stamp). I want to use an Event, but I can't figure out where to put it. I want the TINITIALS and STAMP to change whenever someone changes something they're able to fill in. Do I want to be on the property sheet for the specific text box (like RESPONSIBLE) or do I want to be on the property sheet for the subform? And then where do I put the VBA? I've read examples of Before Update, Before Insert on the subform or On Change on the text box itself, but none are working. My plan was to use this code once I found the right place. Can you confirm whether it's right or not too?

Me!TINITIALS = Environ("username")
Me!STAMP = Now()

My subform's name is E_ENTRY_GRADE_CRED_SUB. Do I use that instead of Me!?

Thanks in advance! You guys have really come through for me in the past!

 

[ARW17]

A subquestion to the one above is also that my initial table has NEEDS_IMPROVEMENT as a checkbox. When I put the table into a subform, it changes to No (instead of unchecked box). I'd rather have users check the box than have to write out yes/no. Can you point me to the property I need to change? Not sure if it's in the table itself (which right now shows checkbox (shown below). I can't find anywhere on the subform to change it. I screenshot what the different pieces look like for clarity. Thanks!!

 

[Micron]

I think you will have to change your form design.
1) You cannot hide one "cell" of a datasheet
2) you cannot hide one instance of a control on a continuous form.

If that's not what you meant, then that goal isn't clear. If it is, then you'd need a single form view and navigate from one record to another, hiding/showing controls in the form Current event code. Nor can you have a checkbox in a datasheet IIRC.

Also your post isn't clear about the stamping. You want some fields to be stamped one time, then others another time? Or stamp fields and then if altered later, stamp again but over-write? Or stamp again but keep a record of each stamp? The latter would require a history table.

Not sure if this will help after all of that, but how you reference a subform depends on where you're referencing it from. If from a main form, then
Forms!MainFormNameHere.SubformControlNameHere.Form

SubformControlName is the name of the subform control that contains your subform. Don't alter the word "Form" in that syntax.

 

[ARW17]

I was planning to hide the entire column with TINITIALS (username) and STAMP. I just had them visible to see if it was working.

If someone fills something in in the "RESPONSIBLE", "CRED_CATEGORY", etc. Can I write code to put T3ARF in the TINITIALS column and 11/11/20 3:55:22 PM in the STAMP field? I wrote them in on the below. I'd like to automatically populate the TINITIALS and STAMP on the second record because I wrote things in the RESPONSIBLE, CRED_CATEGORY, etc fields.

I kinda thought I might not be able to have the checkbox. Maybe I'll just replace it with a yes/no drop-down?

 

[JonXL]

For the auditing use the form's BeforeUpdate event and put the code you've created there're.

For the checkbox, right-click the control and see if you have the option to change it to a checkbox.

 

[Micron]

No! No combos (lookup fields) in a table - and you can't put them in a query even if you wanted to.

You can have a checkbox in a datasheet so I don't understand what the issue is. Then again, too much going on in this thread. Can't make sense out of the pics as one datasheet (?) doesn't look the same as the next. Perhaps one/some are tables and others are pics of queries? Also, you should be able to set a bound form field to whatever you want in any of the events you first mentioned, so I can't see what that issue is either - at least not without looking at what you tried. Perhaps a subform is involved somehow - you didn't clarify from my first post.

 

[ARW17]

It finally worked when I added the following to the Before Update of my subform:

Me.[TINITIALS] = Environ("USERNAME")
Me.[STAMP] = Now()

 

[ARW17]

For the auditing use the form's BeforeUpdate event and put the code you've created there're.

For the checkbox, right-click the control and see if you have the option to change it to a checkbox.

I don't have the option to change it to a checkbox by right-clicking. It's greyed out.

 

[xenou]

You can see that Needs_Improvement is formatted Yes/No in the form where you see it as a checkbox. See if you can do the same again.

 

[ARW17]

I only see it as a checkbox in the table. When I pull that table into a form (as a subform) it changes.