BeforeUpdate Event

[rube]

I have two controls that I need Required. I have tried using the BeforeUpdate event to check for Null values.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ItemDescription) Then
MsgBox "You Must Enter an Item Description", vbCritical, _
"Canceling Update"
Me.ItemDescription.SetFocus
Cancel = True
End If

End Sub

When a user hits the Close Button, the code runs with the message, but the Close Action continues. There are about 3 ways a user could update via close, or "New Record" navigation. Is there any way to do this without writing the same code for all three buttons?

Thanks.

 

[Jack Cowley]

In your close button remove the DoCmd.Close, set a flag and then call the Before Update event instead of closing the form. In the Before Update event you will know if the user has tried to close the form and you can respond accordingly. You can do the same for other buttons, but remove the navigation buttons and add your own so you have control over what happens.

hth,
Jack

 

[rube]

BeforeUpdate

Thanks for the Reply. I am not sure if this is what you meant, or there was an easier way of doing it. I certainly appreciate your time.

Public Function RequiredFlag()
If IsNull(Me.ItemDescription) Then
MsgBox "Item Description Required", _
vbCritical, _
"Canceling Update"
Me.ItemDescription.SetFocus
RequiredFlag = 0
ElseIf Not Me.txtVendor > 0 Then
MsgBox "Vendor Required", _
vbCritical, _
"Canceling Update"
Me.txtVendor.SetFocus
RequiredFlag = 0
Else
RequiredFlag = 1
End If

End Function

 

[Jack Cowley]

This is what I had in mind...

1. Create a variable in a module with code like this:

Public Flag as Integer

2. In the On Click event of the Close button use code like this:

Flag = 1
Call Form_BeforeUpdate(-1)

3. In the Before Update event of the form put code like this in the appropriate place in your existing code:

If Flag = 1 Then
Flag = 0
DoCmd.Close
End If

Using the Flag variable you can set it to various values and by doing so you will know which control on your form called the Before Update event. For example, if you have a New Record button you can use Flag = 2 in its code. Then in the Forms Before Update event you can tell from the value in the Flag variable which control called the event and you can respond accordingly. In the code above if the Flag does not = 1 then it will go to the next line of code after the End If and if the next line is If Flag = 2 Then... I think you get the idea.

I hope that I have cleared this up, but if I have not just let me know....

Jack

 

[rube]

One More Question

I am not sure I completely understand. I understand the concept of using flags, and performing actions based on this and rejecting bad data. What I dont understand is if I am using a ComboBox, say "cboEmployee", and on the BeforeUpdate Command it doesn't meet a certain criteria, so I want to undo that entry. Me.cboEmployee.Undo does not appear to be working, as it still goes to the next control.

I guess I do not see how you would use this to force the user to choose valid criteria before allowing the entry. I know how to use the LimitToList, but I am trying to avoid that in this instance, and want to be able to designate the criteria, if it fails, give a message, undo the entry and return the focus to the same control.

If I may ask you to explain this a little further, I would greatly appreciate it.

 

[jaydwest]

Try adding the following code at the beginning of the butClose_Click event.

If me.dirty then
Docmd.RunCommand acCmdSaveRecord
End If

Also add error trapping on both the Form_Before Update and butClose_Click to trap for error 2501 = Event was cancelled. You might try something like this.


Private Sub Form_BeforeUpdate
On Error got Err_Proc


...

Exit sub

Err_Proc:
if err = 2501 then Exit Sub
msgbox Err
Exit Sub

End Sub

-------

Good Luck! If you have any questions please let me know

 

[Jack Cowley]

Hmmm. How can a combo box have wrong data? If you are allowing the user to select data from a combo box and they are to select only specific values from that combo box then I would filter the combo box so they cannot make a wrong selection.

I guess we are both a bit confused about what the other has in mind.

Also, to clear a combo box set it to null:

Me.cboEmployee = Null

I am not sure what it is you are wanting to do, but there is a solution, I just don't know what it is from what you have said... Don't give up though!

Jack

 

[rube]

Wrong Data

Thanks for the Response Jack and Jay.

Say I have a message table/form.

When an Employee puts in their Name, say John Smith, as John types "J" it will prefill saving the employee time, they hit enter and it pulls their messages.

Now if we have a Temp for one day or a week, I do not necessarily want to have to add them as an Employee, which is why I did not LimitToList. But if they only put in their First Name instead of whole name, (or whatever criteria), if I check this in the BeforeUpdate or AfterUpdate, it is too late, it has already been entered.

This is what I do not understand, isn't it too late at that point to "go back" and keep the focus on the Combo Box without the data being entered? (And thanks for the Null point).

 

[jaydwest]

The data is entered on the screen but is not committed to the underlying table until the record is Updated. If you interupt the save process in the BeforeUpdate event, you can prevent the data from being saved.

We have been using the code I sent you for many years and it does exactly what you want.

If you cancel the update in the BeforeUpdate event, the data is not saved. We typically display a message like:

"Last Name could not be found. Enter a Last Name or press {Esc}{Esc} to clear the record."

If you have any other questions, let me know.

 

[rube]

BeforeUpdate

I appreciate the Replies. I got your (both) coding to work.

One thing I was trying to do was give immediate feedback, without waiting for them to hit a Button or try to save the Record.

I used the following code...

Private Sub txtBox_BeforeUpdate(Cancel As Integer)
If Me.Test <> "Criteria" Then
DoCmd.CancelEvent
MsgBox "Doesn't Equal Criteria"
Me.txtBox.Undo
End If
End Sub

This works when they hit enter, tab, hit a button or navigate. Should I not use this code because the other way is better? I do not want to get in the habit of writing or using poor code.

Thanks,

Rube.

 

[jaydwest]

Your code will work only if the user enters an invalid value in a reqired field. If he/she simply bypasses the field, the field is not updated and the your code will execute.

 

[rube]

Jay,
thanks for all the input.

Rube