[X]My Assistant

[everettmiller]

Hey forum!

My project contains an unbound form with 3 unbound controls (one text, two combo boxes). When the users enters text in the text box, a validation is run via a Before Update event to ensure that the user has not entered any duplicate data. If duplicate data is detected then the event cancels the update, clears the text box and then returns the cursor to the textbox.

At least that is supposed to be happening. What IS happening is that Access throws an error 2115 when the event attempts to clear the contents of the text box to allow the user to try again. The error 2115 states: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field.

The textbox control is not bound to anything and does not have a validation rule. Here is the Before Update code:

CODE

Private Sub txtNew_BeforeUpdate(Cancel As Integer)
Dim strDuplicate As String
Dim Response

strDuplicate = "The name you entered for the new Section already " & _
    "exists in the database. Please try again."
    
If DCount("[Section_ID]", "tblSections", "[tblSections]![Section] = """ & Me.txtNew & """") > 0 Then
    Response = MsgBox(strDuplicate, vbExclamation + vbOKOnly, "Duplicate Sections found!")
    Cancel = True
    Me.txtNew = ""                  <======= Error occurs here
    DoCmd.GoToControl "txtNew"
End If

End Sub

I've also tried me.txtnew.value = null and [Forms]![frmNewSection]![txtNew] = " ", along with a few others. Just as soon as the code attempts to rest the value of the textbox the error occurs.

If there is duplicate data then, ideally, the code should cancel the event, clear the text box and position the cursor back into the textbox.

Thanks in advance!

Ev

[theDBguy]

Hi. I think someone already mentioned this before, but you can't change the value of a control within its BeforeUpdate event. Is that what you're trying to do? If you're just trying to set it back to what it was before, you can try using the Undo method instead.

[mempie]

If, after trying out code in the textbox.BeforeUpdate or other events, it doesn't behave elegantly, it might be easier to put your validation code in an OK button's click event. I suppose that this is a dialog box that maybe has an OK or "submit" button, and perhaps a Cancel button.