How to Trigger an Event on the Navigation of Records? (1 Viewer)

[JithuAccess]

Hello Guys,

This seems to be very simple but I am not getting a solution.

I have a field in my Form "Data Entry By". This field is a mandatory field. So I have wrote this code in the LostFocus Event of "Data Entry By"

Private Sub strDataEntryBy_LostFocus()

If IsNull(Me.strDataEntryBy) Then
    MsgBox "'Data Entry By' should not be blank", vbInformation, "Blank!!!"
    Cancel = True
End If

End Sub

But I want trigger this code when I navigate the Record. For Example if there are 10 records, when a user navigate from record 1 to record to, I want trigger this code if "Data Entry By" is blank.

I have tried this code in the Before Update event of the Form and it is not working.

Or is there any simple method for this (Except LostFocus please)

Thanks

 

[jdraw]

Can you not get the user name at logon and use that?

Environ("Username")

 

[arnelgp]

use Current event of the form.

 

[The_Doc_Man]

The best way to do this is to not care at all about that field... UNTIL the user attempts to SAVE the form when it is first created. In the FORM_BEFOREUPDATE event, test the field that you want to not be blank. If it is blank, you can cancel (set CANCEL=1) the update because the _BEFOREUPDATE event has a CANCEL parameter. That way, the form will have a CreatedBy value before you save it and you can't save it until you fill in that blank. If you do that, there will never be a need to do any other testing in any other event.

Note also that jdraw's comment might be appropriate. If you have a possibility of looking up a network ID that is meaningful for your user, or if you have a login on this DB, you have an ID that you can simply store in the form without asking. Again, that "save current user ID" step would occur in the _BEFOREUPDATE event. If you store it earlier on a new form, you might accidentally set yourself up for saving blank forms that ONLY have the user ID, so you have to be careful about WHEN you auto-save anything.

EDITED to correct a sentence that said what I meant backwards.

 

[Pat Hartman]

Doc is correct. Validation belongs in the Form's BeforeUpdate event and you MUST cancel the event (cancel = true) to stop Access from saving the bad record.

If you want to track who is making changes, this is not the way to do it. You don't ask a person to tell you who he is because you already KNOW who he is and so you just save his UserName without prompting. That is what jDraw was alluding to. Depending on the company environment, Environ("UserName") may or may not be a wise choice. I normally have a log in form and use the value from that instead. Not all of my clients use rational naming methods for their hardware.

You probably need other validation but you don't need this particular If statement.

The last two lines in all of my form's BeforeUpdate events are:

Me.LastUpdateBy = Environ("UserName") --- (although, in the cases where I use a login form, I reference that instead)
Me.LastUpdateDT = Now()

If I show these two controls on a form, they are ALWAYS locked because it isn't the user who should be updating them, it is the code written by the developer. I also set their Tab property to No so the cursor doesn't even stop in the controls when the user is tabbing.