Forcing users to update fields 3

[RacerGirl117]

I have a form in which there is an "Updated By" (user's intials) and and "Updated Date" (date field with a short date input mask). I have some code that forces the user to enter their initials or a date if either of the fields is null, but nothing to force the user to update those same fields if they are already filled in.

This is a bill of material form that can have many updates and if there is already initials and a date in those two fields, some users don't enter anything. I was going to change the properties of those fields to Required in the table, but since there is already data in it, it won't work (easily).

I'd like to put something in VBA behind the scenes of the form, but I'm unsure of the proper/most accurate syntax. Is there a VBA property for instances when data exists, but has not changed/been updated?

Please respond to racergirl435@aol.com

Thanks in advance,
Jessica Morgan
Fire Fighter Sales & Service Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[rickgrimes]

You are looking for the BeforeUpdate event for the form. When the user changes any data on the record, this event occurs before the changes are updated to the table.
Place your code there.

 

[RacerGirl117]

Rick,

I figured that much out but am unsure of the code I need to use. Do I name the specific fields? How do I tell the code "if a record changes in subforma, force the user to enter their initials and the date"?

Thanks,
Jessica Morgan Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[rickgrimes]

Jessica,

Sorry about not getting back to you sooner.

OK, the beforeupdate event only runs when the data of the record is changed. Therefore, if a user just views that data, the event will not happen. But, if the user changes any data on the record, the event runs. I think the code would looks something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ans As String
On Error GoTo err_formupdate

ans = InputBox("The data on the record has been updated, please enter you intials", "Update Record&quot

Me![Updated Date] = Now()
Me![Updated By] = ans


err_formupdate:
If Err.Number > 1 Then
MsgBox Err.Description, , "Update Record"
Exit Sub
End If
End Sub

Basically very simple, you may want to change the dialog with the user as you need to.

Good Luck.

Rick.

 

[RacerGirl117]

Okay, that makes more sense Rick. I was having a mental block and thinking that I needed to focus on the specific fields and not the record as a whole. I will give that a shot today and see how it works. Unbelievable that we have to do things like this to force people to do what they should be doing anyway.

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[RacerGirl117]

Rick,

Thank you very much for your help. It works. I couldn't get the error handling part to work though. I got a message saying it wasn't defined. So I just "rem'd" it out and it works fine. We'll see how much the users like being forced to do something. I marked your post as helpful.

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[rickgrimes]

Jessica,

If you are using an earlier version of Access, change the Err.Number to Error, and the Err.Description to Error$.

The Err object is part of DAO 3.6

Thanks for the star....now if only my boss could see me now! LOL.

Thanks.

 

[RacerGirl117]

Hey Rick,

I am using Access 2000, so the Err isn't the problem. I typed the code in exactly as you had it above. Here is the message I'm getting:
Compile Error:
Sub or Function not defined.

Here is the code exactly as it appears in my VBA window:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ibans As String

On Error GoTo err_formupdate

ibans = inputbox("You have made changes to this record. Please enter your initials.", "Update Record&quot
Me.Updated_By = ibans
Me.Update_Date = Now()

err_formupdate
If Err.Number > 1 Then
msgbox Err.Description, , "Update Record"
Exit Sub
End If

End Sub

I'm guessing I'm getting the message because the err_formupdate is not defined. But I don't know exactly what that means.

Thanks,
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[MoiraT]

Hi Jessica
You need a colon : after your line err_formupdate, like this
err_formupdate:
HTH
Moira T All help greatly appreciated

 

[RacerGirl117]

Doh! Thanks! Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com