BeforeUpdate Event with MsgBox

[HOB32]

I would like to implement a BeforeUpdate Event in a field called "job".

When you open up the form for the Jobs, you see the last job entered.
People are typing over the last entry when they are trying to either search
for another job/or enter in a new job. There is a button for adding a new
job, but some people do not seem to remember that it exists.


What I want to do is this:
I want a way to protect the number from being mistakenly edited. I am not
sure if I would need to use the AfterUpdate Event or the BeforeUpdate Event.
I think the easiest thing to do (if it is possible) is to let them change the
complete number (example 2408.02) & when they click on the next box, a
message box appears asking them if they are sure that they want to change the
job number. If they click yes, the event stops and they go from there. If
they click “cancelâ€, the job number reverts to the old number (OldValue).
Then the event stops.

I think you can do this in Visual Basic. I am sure that this can be done,
but I do not understand the syntax of the commands in order to implement it.


Thanks in advance!

 

[Sprinks]

You can use the BeforeUpdate event to do what you wish. I would recommend
using the Form's BeforeUpdate event rather than each field.

Dim intResponse as Integer
If Me.Dirty Then
intResponse = MsgBox("Accept changes to record?", vbCritical _
+ vbDefaultButton2, "Confirm Record Changes")
If intResponse = vbCancel Then
Cancel = True
End If
End If

However, you may wish to use additional strategies to avoid accidental
erasure.

- Set the form's AllowEdits property in design view to False, and set it
back to True via a command button:
Me.AllowEdits = True
Then set it back to false when the user moves to a new record (in the On
Current event procedure)

- Place a lookup combo box in the form's header to facilitate looking up the
record of interest. Redo the Tab order so that this control is the first
one. Then when opening the form, the cursor will be placed in the lookup
control. Or set it there explicitly in the OnCurrent event:

Me![MyComboBox].SetFocus

Hope that helps.
Sprinks

 

[HOB32]

Thanks Sprinks, you have a lot of good ideas.

I tried your first idea & my problem with it is that it does not revert the
number back to the old job number if you cancel out of the message. Plus, I
think the people in my office might need two buttons, one cancel and one ok.

Is there any way to create a msgBox with two button options, cancel & Ok and
if you press the cancel button, the number reverts back to its previous
undedited value? The Ok button would of course, end the event.

Thanks again.

You can use the BeforeUpdate event to do what you wish. I would recommend
using the Form's BeforeUpdate event rather than each field.

Dim intResponse as Integer
If Me.Dirty Then
intResponse = MsgBox("Accept changes to record?", vbCritical _
+ vbDefaultButton2, "Confirm Record Changes")
If intResponse = vbCancel Then
Cancel = True
End If
End If

However, you may wish to use additional strategies to avoid accidental
erasure.

- Set the form's AllowEdits property in design view to False, and set it
back to True via a command button:
Me.AllowEdits = True
Then set it back to false when the user moves to a new record (in the On
Current event procedure)

- Place a lookup combo box in the form's header to facilitate looking up the
record of interest. Redo the Tab order so that this control is the first
one. Then when opening the form, the cursor will be placed in the lookup
control. Or set it there explicitly in the OnCurrent event:

Me![MyComboBox].SetFocus

Hope that helps.
Sprinks

I would like to implement a BeforeUpdate Event in a field called "job".

When you open up the form for the Jobs, you see the last job entered.
People are typing over the last entry when they are trying to either search
for another job/or enter in a new job. There is a button for adding a new
job, but some people do not seem to remember that it exists.


What I want to do is this:
I want a way to protect the number from being mistakenly edited. I am not
sure if I would need to use the AfterUpdate Event or the BeforeUpdate Event.
I think the easiest thing to do (if it is possible) is to let them change the
complete number (example 2408.02) & when they click on the next box, a
message box appears asking them if they are sure that they want to change the
job number. If they click yes, the event stops and they go from there. If
they click “cancelâ€, the job number reverts to the old number (OldValue).
Then the event stops.

I think you can do this in Visual Basic. I am sure that this can be done,
but I do not understand the syntax of the commands in order to implement it.


Thanks in advance!

 

[Rick Brandt]

You can use the BeforeUpdate event to do what you wish. I would
recommend using the Form's BeforeUpdate event rather than each field.

Dim intResponse as Integer
If Me.Dirty Then
intResponse = MsgBox("Accept changes to record?", vbCritical _
+ vbDefaultButton2, "Confirm Record Changes")
If intResponse = vbCancel Then
Cancel = True
End If
End If

There is no need to test the Dirty Property in the BeforeUpdate event of a
form. If that event is fired then by definition the Form is dirty.

 

[Sprinks]

HOB32,

Sorry about that. That's what I get for posting air code that hasn't been
tested, which I rarely do. Try the following. Setting Cancel = True
prevents the user from moving off the record, Me.Undo undoes the changes.
Adding the pre-defined constant vbOKCancel to the button parameter shows the
two required buttons:

Dim intResponse As Integer
If Me.Dirty Then
intResponse = MsgBox("Accept changes to record?", vbCritical _
+ vbOKCancel + vbDefaultButton2, "Confirm Record Changes")
If intResponse = vbCancel Then
Cancel = True
Me.Undo
End If
End If

Thanks Sprinks, you have a lot of good ideas.

I tried your first idea & my problem with it is that it does not revert the
number back to the old job number if you cancel out of the message. Plus, I
think the people in my office might need two buttons, one cancel and one ok.

Is there any way to create a msgBox with two button options, cancel & Ok and
if you press the cancel button, the number reverts back to its previous
undedited value? The Ok button would of course, end the event.

Thanks again.

You can use the BeforeUpdate event to do what you wish. I would recommend
using the Form's BeforeUpdate event rather than each field.

Dim intResponse as Integer
If Me.Dirty Then
intResponse = MsgBox("Accept changes to record?", vbCritical _
+ vbDefaultButton2, "Confirm Record Changes")
If intResponse = vbCancel Then
Cancel = True
End If
End If

However, you may wish to use additional strategies to avoid accidental
erasure.

- Set the form's AllowEdits property in design view to False, and set it
back to True via a command button:
Me.AllowEdits = True
Then set it back to false when the user moves to a new record (in the On
Current event procedure)

- Place a lookup combo box in the form's header to facilitate looking up the
record of interest. Redo the Tab order so that this control is the first
one. Then when opening the form, the cursor will be placed in the lookup
control. Or set it there explicitly in the OnCurrent event:

Me![MyComboBox].SetFocus

Hope that helps.
Sprinks

I would like to implement a BeforeUpdate Event in a field called "job".

When you open up the form for the Jobs, you see the last job entered.
People are typing over the last entry when they are trying to either search
for another job/or enter in a new job. There is a button for adding a new
job, but some people do not seem to remember that it exists.


What I want to do is this:
I want a way to protect the number from being mistakenly edited. I am not
sure if I would need to use the AfterUpdate Event or the BeforeUpdate Event.
I think the easiest thing to do (if it is possible) is to let them change the
complete number (example 2408.02) & when they click on the next box, a
message box appears asking them if they are sure that they want to change the
job number. If they click yes, the event stops and they go from there. If
they click “cancelâ€, the job number reverts to the old number (OldValue).
Then the event stops.

I think you can do this in Visual Basic. I am sure that this can be done,
but I do not understand the syntax of the commands in order to implement it.


Thanks in advance!

 

[Sprinks]

Rick,

Thanks.

Sprinks

There is no need to test the Dirty Property in the BeforeUpdate event of a
form. If that event is fired then by definition the Form is dirty.

 

[Klatuu]

I would employ a slightly different strategy. First, I would not use a bound
text box for searching. There are too many problems that can arise. First,
you have to be aware that once you change the value in a bound control, it
has changed the value for the record. This is usually not what you want.

My preferred technique is to use a combo box for searching and a bound text
box to show the current value. I usually hide the bound text box.

So here are the things you need to do.

Create an unbound combo box on your form. Make it's row source a query
based on the job number field of the jobs table. Use the After Update event
to position the selected job number. Use the Not In List event of the combo
to allow the user to enter a new job number. It will help if you make the
form you use to add a new job number a Modal form. This is to allow the code
in this form to halt until the Job Number form has closed. And to keep the
combo in sync with the bound text box, use the form's Current event.

Private Sub cblJobNum_AfterUpdate()
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[JobNumber] = " & Me.cblJobNum
If rst.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

End Sub

Private Sub cblJobNum_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Job Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Call AddNew_Click ' This calls the code you use to add a new record
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[JobNumber] = " & NewData
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboJobNum.Undo
Response = acDataErrContinue
End If

End Sub

Private Sub Form_Current()

Me.cboJobNum = Me.txtJobNum

End Sub