Events missing from form

[elizabeth]

DonLo sent me this message, below. Why would his Before Insert event would be missing? If there's anything wrong with or missing from the FAQ, I'd like to correct it.

Hi Elizabeth,

Your "Date Stamp..." (see below) faq is excellent! Thanks for posting it - it is exactly what I want to do. I followed you up to Step 6, but I'm a little unclear on the Before Insert and the Before Update events. I see a Before Update property for each of the fields, but not a Before Insert. So where does the code go?

Thanks for your help
*********************
Date stamp records and capture User name when data is added or changed

You can capture and track the name of the user and the current Date & Time every time a record is added or changed in a table. Here's how:

1. Go into design view of the table.
2. Create 4 new fields: CreatedDate, CreatedByUser, ChangedDate, ChangedByUser.
2. Set the 2 User fields to data type Text, with a field size the length of a login ID.
3. Set the 2 Date/Time fields to data type Date/Time.
4. For date only, set format to Short Date. For Date & Time set format to General.
5. Set the default value of CreatedDate to Date() for date only; Now() for date & time.
6. Go into design view of your data entry form.
7. Capture the Access loginID with CurrentUser() or NT's login with Environ("username&quot.
8. Use the Before Insert event to load the user logidID when adding a new record.
9. Use the Before Update event to load the user & datestamp when updating a record.

Here's an example of how to load a value:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UpdatedByUser = Environ("username&quot ' this is the NT loginID
Me!UpdatedByUser = CurrentUser() ' use this line if you want the Access loginID
Me!UpdatedDate = Date()
End Sub

 

[DonLo]

Elizabeth,

Thanks for posting my question. I realized after I sent it to you that I should have posted it instead, so that others could 1) answer, and 2) learn from the answer. This is such a great resource - thanks to all who make it so!

Anyway, to clarify the question: I'm using Access 97 - did BeforeInsert change names in a later version? Also, in your code your have a couple lines beginning with "Me!UpdatedByUser...." Is "Me" the name of the form? And is "UpdatedByUser" really the field name "CreatedByUser"?

Thanks for all your help

 

[elizabeth]

1. No, "Me" is not actually the name of the form, but it is a valid way of referring to the the current object. You can use the code as is, without having to substitute whatever you actually named your own form. You can find more info about it in Access/VB Help.
2. No, UpdatedByUser and CreatedByUser are two distinct fields. One is for capturing the username of the user who created the record, the other is to capture the username of the user who updated the record. CreatedByUser is only populated when you add a new record, UpdatedByUser is changed every time the record is updated, so it reflects only the last user who touched it.
3. I just realized the possible answer to your original question - Before & After Insert are available at the Form level, but not at the Field level. Could that have caused the confusion?

 

[DonLo]

Elizabeth,

Thank you. Thanks for the clarification on "Me"

As to 2., I mistakenly typed CreatedByUser, when I should have typed "ChangedByUser" I think in your faq, you call the field "ChangedByUser" at the top (point 2), but at the bottom, call it "UpdatedByUser". Anyway, I think I get your point.

3. Yes. I was looking for the event in the control properties, and I subsequently found it in the Form properties.

Anyway, I successfuly implemented part of this plan, enough to realize that username (for both CreatedBy and UpdatedBy) won't work for me, because everyone here logs into the network using the same username. I was thinking I could create a small form asking the user to enter their name with a default date value, and then query off that table to get the most recent entry into it. Then I would pull the username and date into the main form from the query, using a similar procedure to the one you outline. At first attempts, it is not working. Could the BeforeInsert and BeforeUpdate events be modified to pull the data from the value of a field in a query?

The big picture of what I'm trying to do is add the CreatedBy, CreatedDate, ChangedBy and ChangedDate to every record. It would seem like this would be a common task that must have a (somewhat) simple means to be implemented.

Thanks again.

 

[elizabeth]

You say everyone logs onto the network with the same ID. I'm not aware of any circumstances under which that is a good idea. If you are not in a position to correct this, you can still set up Access Security so that users have to log into Access under thier own name t get into your database, regardless of how they log into the network. Then the above approach would work. It would be quite a different procedure to ask the user to key in thier name every time they update a record, and also not a good idea.

 

[mrfilez]

I have to agree with Elizabeth. I have never under any circumstance seen a situation where it was a good thing to have all users using the same login. What is the point of having a login at that point???

I also agree with Elizabeth on using Access security to make everyone log into your database. You will need to create an account for yourself and give it complete admin access. Then put a password on the admin account, and remove all rights from the account. This gives you a bit more security as well as making sure that the users have to sign in under their name.

Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome.fedex.com
mrfilez@midsouth.rr.com

 

[mattpearcey]

No matter what i do Elizabeth, I cannot get the ChangedDate and ChangedByUser part to work. I have followed your FAQ and all the comments on this thread,and thread i have posted before, but i still cannot get it to work. I have the four field set up on the font page of my opening page of the record, so as to show whenthe record was enter and by whom. I have entered a new record and these fields become populated fine, and look great. I then save that record and try and go back in edit mode and change the record, then follow the same process - save and re-open. But nothing appears in the ChangedDate and ChangedByUser fields.

I have gone into the properties and added the code on after update for both fields, but still no luck. any ideas as to what i am doing wrong? Thank you for your help already.

Matt Pearcey

 

[MichaelRed]

To all,

See faq181-291 It save not only the username (still requiring Access security) as well as the complete change history. You can (easily?) know not just the last user to modify the record, but a more or less complete history of who changed what, and when they did it. This is relatively easy to implement for any Ms Access db using bound forms, maintains the complete history, and does not add fields to your database. The only 'downside' to this approach is the rapid growth of the history file in 'busy' databases. I implemented this in a call center database and found that the history table grew approximatly 3 to 5 times faster than the (Master) call table. To limit the impact of this on the 'real' db, I periodically archived all "completed" records history to a seperate db and deleted them from the live db. this was more-or-less necessary every 3 to 6 months, so I implemented it as a Monthly procedure whix ran automatically the first of each month.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[mattpearcey]

Michael

I am just about to try this code. Ive got it figure aprt from im a bit confused as to the bound forms issue. Where can find out whether my fomrs are bound or not? I have tried all the help option but it doesnt really give me any ideas, and is gettingme more confused.

Thank you for your help already.

Matt Pearcey

 

[mattpearcey]

No - i have to be very very bad at this, as i cannot get his even to appear to work.

Sorry Michael, its not you. I just need a little more patience and a lot more knowledge!


Thank you for your help already.

Matt Pearcey

 

[elizabeth]

Matt, when you say things like you are saving the record and then going back into edit mode, it sounds like you are changing the mode of the form. The code I set up was only tested on a form that allowed add and edit capabilities. Normally on this type of form you don't do anything to explicitly save a record, each record saves automatically when you exit it.

BTW, bound forms are those with a query or table named in the RowSource property.

 

[mattpearcey]

I have a swtichboard that has three options. One to open the forms in read only, one to add a new record completely, and another to edit the record (so that opens the database, showing all the records and allowing you to edit. So in other words, i have been opening it in this mode (edit) changing a record, then closing the form. Then going back into the form; there is no record of the user or time at whcih it was changed. So thats where i am a bit confused? Do you think i ought to go a better way around my swtichboard idea? Thank you for your help already.

Matt Pearcey

 

[elizabeth]

Try opening the form directly from the database Form window and see if that makes any difference. Make a change and then go to the next or previous record rather than closing the form. See if your results are any different. If not, you'll need to try stepping thru the code in debug mode (Ctl-g). This is really the best way to see what is happening.

 

[DonLo]

Hi All,

I thought I'd pass on how I solved my problem, as it might help others. First of all, regarding logging onto the network with the same username - yea, this always seemed weird to me, but that's just the way it is.

I didn't want to go the Access security route, because security is not such a big deal with this application, and a lot of random people will use the application to enter data. I didn't want to force them to remember another password, nor did I want to have to administer that. So here's what I've done, which seems to work (so far).

When someone opens the application, they go to a simple "log in" form (called OpLogIn). Just 1 important field, OpInit (Operator Initials) which is a text box. There is a command button on this form which launches a macro which minimizes this form and opens the data entry form.

On the data entry form, I've got the 4 fields that Elizabeth mentioned, and I use the following form level event procedure to populate the fields and save the values:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!ChangedDate = Now() 'using Now() for testing only
Me!ChangedByUser = Forms![OpLogIn].[OpInit]
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CreatedDate = Now() 'using Now() for testing only
Me!CreatedByUser = Forms![OpLogIn].[OpInit]
End Sub

This seems to work. I tried to pull the OpInit field out of a table, and out of a query, but neither worked, so I pulled it out of an open form instead - the form has to be open, that's why I minimized it in the macro, instead of closing it. One drawback is I don't have a complete history of changes, just the last one.

I'm sure there are better ways to do this, but it does seem to work.

Don