Check if field exists

[Malkim92]

Hello Everyone,
I am using a code to check if the name exists in a table and if it does then gives an error message and returns back to same field and empties it out.

The form has a field called Customer_Name which is need to check in table tblCustomerInformation.

Im using this:

Private Sub Customer_Name_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Customer_ID]", "tblCustomerInformation", "[Customer_Name] = '" & Me.Customer_Name & "'")) Then
MsgBox "Name Exists!""" _
& vbCrLf & " " _
& vbCrLf & "Please Try Again.", vbExclamation
Me.Customer_Name = Null
DOB.SetFocus
Customer_Name.SetFocus
End If
End Sub

But it keeps returning a runtime error.

Any Help would be appreciated.

 

[Addyman]

What is the runtime error it returns?

 

[Malkim92]

Run-time error '-2147352567 (80020009)':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.

 

[Malkim92]

If what im asking for is not possible then is there a code to select End on the runtime error window so then it would go back to the same box on its own?

 

[spikepl]

Your code sux

You are running BeforeUpdate, which changes the value, which triggers BeforeUpdate, which changes the value .......and so Access complains.

The sequence of dealing with such problems is this:

1. The first place to look is the documentation. Press F1 and search for BeforeUpdate. Access has extensive documentation, so use it.

2. The second place to look, if the examples from F1 were inadequate, is Google. Piles of examples. Don't think that no one has run into your problem before. Assume that they have.

3. If all that fails, or you do not understand the examples, search on AWF.
site:www.access-programmers.co.uk YourSearchTerms
restricts the google search to AWF.

4. If all that fails, ask a question on AWF


The proper use of a control's BeforeUpdate is

Private Sub Customer_Name_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Customer_ID]", "tblCustomerInformation",               "[Customer_Name] = '" & Me.Customer_Name & "'")) Then
MsgBox "Name Exists!""" _
& vbCrLf & " " _
& vbCrLf & "Please Try Again.", vbExclamation
     Cancel=True ' this tells Access not to go through with the updating of the control
     Me.Customer_Name.Undo ' this throws out the newly entered value and reverts to whatever was in the field prior to entry
   
End If
End Sub

 

[Malkim92]

The thing is i have searched on google and couldnt find anything similar to my case, but ill search more.

Thanks anyway, and if there is any code that you know that is helpful that would be nice.