user logging

[AccessMan]

I use a security file that requires user logins, and I'd like fields on a
form to be updated with the username and date/time whenever any other data is
changed on the form. I assume I would use the After Update event, but how to
I get access to the username?

Thanks!

 

[Scott McDaniel]

I use a security file that requires user logins, and I'd like fields on a
form to be updated with the username and date/time whenever any other data is
changed on the form. I assume I would use the After Update event, but how to
I get access to the username?

The CurrentUser function returns the name of the currently logged in user, assuming you're using ULS. You'd do it like
this:

Me.SomeField = CurrentUser

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[AccessMan]

Thanks Scott. This worked with the AfterUpdate event on a Data Entry form
that I created, but for some reason I was prevented from accessing a subform
that is present. Do you know why that would happen?

Also, if I enter/modify data on a subform, does the main form's AfterUpdate
event get triggered?

 

[Scott McDaniel]

Thanks Scott. This worked with the AfterUpdate event on a Data Entry form
that I created, but for some reason I was prevented from accessing a subform
that is present. Do you know why that would happen?

Do you mean that you couldn't move to it? Or that your tried to set the value of a field/textbox on your subform? If
you're having trouble referring to a subform, make sure your syntax is correct:

Me.NameOfYourSubformCONTROL.Form.Property_Or_Object

The NameOfYourSubformCONTROL refers to the actual Subform object on the main form, and is not necessarily the name of
the form being used as a subform. See here for more info on referring to subforms:

http://www.mvps.org/access/forms/frm0031.htm

Also, if I enter/modify data on a subform, does the main form's AfterUpdate
event get triggered?

The main form is updated when you move into the subform, and (I believe) is updated when the subform data is saved.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[AccessMan]

I couldn't move into the subform.

Do you mean that you couldn't move to it? Or that your tried to set the value of a field/textbox on your subform? If
you're having trouble referring to a subform, make sure your syntax is correct:

Me.NameOfYourSubformCONTROL.Form.Property_Or_Object

The NameOfYourSubformCONTROL refers to the actual Subform object on the main form, and is not necessarily the name of
the form being used as a subform. See here for more info on referring to subforms:

http://www.mvps.org/access/forms/frm0031.htm



The main form is updated when you move into the subform, and (I believe) is updated when the subform data is saved.


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Scott McDaniel]

I couldn't move into the subform.

So when you click on the subform, you can't set the focus to any of the controls?

Are you sure that the subform has the correct Master/Child links set? Review those settings.

Also make sure that the form being used as the subform is set to AllowAdditions, AllowDeletions, etc etc. These are
properties of the Form, and can be accessed in Design view.

You could also have mistakenly disabled the subform object ... check this in Design view of the main form.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Scott McDaniel]

I couldn't move into the subform.

Are you sure the subform is enabled? Also, are you sure that the form being used as a subform is set to AllowAdditions
(in case you have no child records)?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[AccessMan]

That's right, I cannot set focus on anything in the subform.

I used code as follows with the AfterUpdate event on the main form:

Me.User = CurrentUser
Me.UpdateTime = Now()

These statements executed properly and updated the fields which reside on
the main form. When I remove this code and hence the reference to it for the
AfterUpdate event, the form and subform work fine.

Data Entry is set to Yes on the main form, but not on the subform. All
properties of the subform involving data modifications, etc. are correct.

 

[Scott McDaniel]

That's right, I cannot set focus on anything in the subform.

I used code as follows with the AfterUpdate event on the main form:

Me.User = CurrentUser
Me.UpdateTime = Now()

These statements executed properly and updated the fields which reside on
the main form. When I remove this code and hence the reference to it for the
AfterUpdate event, the form and subform work fine.

Data Entry is set to Yes on the main form, but not on the subform. All
properties of the subform involving data modifications, etc. are correct.

Have you tried setting the DataEntry property of the main form to False?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[AccessMan]

Some recap to start with, and new information as well.

Added Me.User = CurrentUser to AfterUpdate of main form. After entering
data into the last main form field in the tab order, and hitting tab (to
enter the subform), the message bar at the bottom briefly displays
"Calculating ...", but focus remains on the main form field. I cannot change
focus by clicking inside the sub form either, but each time click on a
subform field the "Calculating ..." message displays. After closing the form
and inspecting the table, I observe that the Me.User = CurrentUser statement
was successful in populating the User field.

Changing Data Entry to No on the main form made no difference.

I changed the Me.User statement to Me.User = "abcdefg". There was no change
in behavior, aside from the dummy text that was updated to the User field.

Next, I deleted the Me.user statement altogether, leaving no executable
statements in the AfterUpdate code, but kept the reference to the VBA
associated with the AfterUpdate property. I could then tab into the subform
and populate it with data, but of course I no longer had my collection of the
User data.

Noticed an event procedure associated with the On Load event of the main
form (I didn't put it there), but there were no statements.

Private Sub Form_Load()

End Sub

I deleted the reference to this VBA from the On Load property of the form.
There was no change in behavior.

 

[Scott McDaniel]

Some recap to start with, and new information as well.

Added Me.User = CurrentUser to AfterUpdate of main form. After entering
data into the last main form field in the tab order, and hitting tab (to
enter the subform), the message bar at the bottom briefly displays
"Calculating ...", but focus remains on the main form field. I cannot change
focus by clicking inside the sub form either, but each time click on a
subform field the "Calculating ..." message displays. After closing the form
and inspecting the table, I observe that the Me.User = CurrentUser statement
was successful in populating the User field.

Put the code in the BeforeUpdate Event

Changing Data Entry to No on the main form made no difference.

I changed the Me.User statement to Me.User = "abcdefg". There was no change
in behavior, aside from the dummy text that was updated to the User field.

Next, I deleted the Me.user statement altogether, leaving no executable
statements in the AfterUpdate code, but kept the reference to the VBA
associated with the AfterUpdate property. I could then tab into the subform
and populate it with data, but of course I no longer had my collection of the
User data.

Noticed an event procedure associated with the On Load event of the main
form (I didn't put it there), but there were no statements.

Private Sub Form_Load()

End Sub

I deleted the reference to this VBA from the On Load property of the form.
There was no change in behavior.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[AccessMan]

Bingo! Thanks Scott.

I'm relatively new to VB, so I'd love to know why this didn't work with
AfterUpdate.

Thanks!

 

[Scott McDaniel]

Bingo! Thanks Scott.

I'm relatively new to VB, so I'd love to know why this didn't work with
AfterUpdate.

You were getting into an endless loop ... the AfterUpdate event was firing when you set the Me.User=CurrentUser ... so
in effect your just continually fired that statement, which is why you kept seeing the "Calculating ..." at the bottom
of the screen.

Beforeupdate allows you to set form fields before the update fires ... so you set the data, then update. With
AfterUpdate, you update, then set the data .... which caused the AfterUpdate to fire again, which then sets the data
again ... which again caused the AfterUpdate event to fire - and so on and so on. Generally AFterUpdate would be used to
alert the user (Msgbox "Your data was saved" or to perhaps edit data in a different table (i.e. not one on the form),
perhaps if you need to log when a new record is added or something of that nature.

Thanks!

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com