Hi
I urgently need some help in regards to an Event Procedure that I need to include 'before updating' in the first field of my form.

Problem 1
What I am trying to do is to allow a user to type a location into the first field of a form and after typing it and pressing tab or enter the system will check to see whether it is a duplicate before the user proceeds in completing the rest of the form and if it is a duplicate not allow further input until the detail is changed or deleted. An example of what the user could type in is MC Donalds-Sydney or any free form text line.

I am only a novice when it comes to Access so I do apologise if this is a silly request.

On a different form I have a similar event procedure which runs and checks whether a particular ID no is a duplicate and if it is it returns a message box. I have tried pasting the same code in and just changing the relevant table names etc... however I am getting an error message.

The previous code I have included is as follows:

Private Sub LOI_BeforeUpdate(Cancel As Integer)
On Error GoTo LOI_BeforeUpdate_Err

If DCount(LOI, "Tble_Name", "LOI='" & Me.LOI & "'") > 0 Then
MsgBox "This LOI has already been used"
End If

CNI_BeforeUpdate_Exit:
Exit Sub

CNI_BeforeUpdate_Err:
MsgBox Error$
Resume CNI_BeforeUpdate_Exit

End Sub

The error message I receive is


Syntax error (missing operator) in query expression 'Count(Smith Street)'.

Problem 2
Also in my original code for the ID number once the message box comes up saying "This CNI has already been used" the user is able to click on OK and then proceed with filling in the form without having to change the ID field. Does anyone know what extra line/s of code I would have to add to change it so that when the user click on OK the system will revert back to the ID field only and stay there until the info is changed or deleted.

Any help anyone could give me in regards to these two problems would be so greatly appreciated.

Eagerly and urgently awaiting a response.
Thanks & Regards, Michelle