To BeforeUpdate or AfterUpdate 1

[JDRoss]

I am wondering when is the best time to check for duplicate entries, Before or After Update on a control?

When I try to set the focus on a control in the form before update, I get an error message telling me that it is not possible and to do it After update (that is a poor paraphrase of the message). I wonder if somebody could explain the difference of the two.

Regards

John

If Not rs.NoMatch = True Then
MsgBox "Collection details for " & rs!FULLNAME & " for this month have already been entered", vbExclamation, "Duplicate Record!!"
Me.Undo
Forms![Monthly Contribution]!ParishonersID.SetFocus
Cancel = True
End If

 

[KyleS]

You might be looking in the wrong direction...

If the whole record is duplicate based on one field entry, make them enter that first and on the form's BeforeInsert run your check.

As an answer to you question directly, BeforeUpdate runs before the change hit's the table and AfterUpdate runs after the data is manipulated.

Kyle

 

[SBendBuckeye]

Data validation should be done in the Before Update event because it passes a Cancel parameter to you. If you set Cancel = True, the form will return to the error control regardless of what key the user pressed or where they clicked the mouse. You can also reset the control with something like the following:

If SomeDataValidationErrorRoutineReturnsTrue Then
Cancel = True
YourFormControlName.Undo
Exit Sub 'Just in case there is other stuff to skip
End If

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[JDRoss]

Thanks KyleS & SBendBuckeye,

But I'm still a little confused. I have trapped the duplicate at this stage, which has to happen checking not just one control but three. (the user is entering contributions for a given month, I want to know if that customer has been entered already, so I have to check the month, year, and the customerid).

The crucial field here is the amount they contribute, which is the one I am checking BeforeUpdate, the others are autofilled and the customerid is selected in a combo box. When the user enters the amount and before moving on to a new record I want to check for duplicates, then I want the form to be undone if there is a dup and the focus set back to the combo control for another customer selection. Within my present code the setfocus doesn't work.

If rs.RecordCount > 0 Then
rs.FindFirst "Parishonersid = " & meparishonersid & " AND monthid = " & memonth & " AND yearid = " & meyear
'Record does not already exist if you reach the end of the file
If Not rs.NoMatch = True Then
Cancel = True
MsgBox "Collection details for " & rs!FULLNAME & " for this month have already been entered", vbExclamation, "Duplicate Record!!"
Me.Undo
Forms![Monthly Contribution]!ParishonersID.SetFocus

End If
End If

Any other ideas!


If not, I think we can live with it!

Regards

John

 

[SBendBuckeye]

John,

When you set cancel to true in a control's Before Update event, that control will automatically receive the focus, you cannot change that.

In the scenario you described above in your response, you want to use the Form Before Update event. This will allow you to undo the entire record plus set the focus on whatever control you desire. Below is a bit of sample code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If city = "testfail" Then
Cancel = True
Me.Undo
Me.lname.SetFocus 'just another control on the form
End If
End Sub

Sorry I didn't understand your problem completely with my first response. The above should get you all set to go.

FYI, control Before Update events will fire as soon as the control attempts to update, Form Before Update events will only happen when the entire record attempts to save.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[JDRoss]

Thanks SBendBuckeye,

That makes sense to have the dup check on the FormbeforeUpdate control. Thanks also for the info on cancel. And thanks to all of you who helped on this occasion.

"Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!"

Where can I do this?

 

[SBendBuckeye]

At the lower left corner of a post in a thread is the following verbage.

Mark this post as a helpful/expert post!

If you click on it you will get a confirmation message asking if you want to flag so and so's post as helpful.

Have a great day!

One more bit of information on Form Before Update code. Once you have done me.form.undo or whatever, you can then reset any default or carryover values in the code.
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!