Checking if a record already exist before adding a new record in Access 2013

Question

Hi, 

I have a table CustomerTable with the following fields

CustomerID: PK index

CustomerName

CustomerTelephone

I have a Data entry Form, to enter the customer information, with the above fields.

The tab set for the Form is:

    CustomerID : 1

    CustomerName: 2

    CustomerTelephone: 3

Before adding a new customer, I check if the CustomerID already exists.

To check  this I created a Before Update event in CustomerID field (since it is the PK index), with the following code:

      Private Sub CustomerID_BeforeUpdate(Cancel As Integer)

          Dim myR As Recordset

          Dim strSQL As String

          'to run a SQL statement to select a record with the same info

          strSQL = "SELECT CustomerID FROM CustomerTable " & _

          WHERE CustomerID = '" & CustomerID & "'"

         'to set recordset to the SQL statment

         Set myR = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

         'if count is greater than 0, then the CustomerID already exists

          If myR.RecordCount > 0 Then   

              MsgBox "A Customer with this ID is already exist"

          End If

      End Sub

Whenever the CustomerID exists, the user gets the message and automatically the cursor positions in the next field CustomerName.

I want that the cursor returns to the first position of CustomerId, for the user to check if he has made a mistake in entering the CustomerId, or to exit the form if the customer already exist.

I tried adding  this code after the MsgBox

       .....

        'if count is greater than 0, then the CustomerID already exists

          If myR.RecordCount > 0 Then   

              MsgBox "A Customer with this ID is already exist"

              Me.CustomerId.setFocus

Me.CustomerId.SelStart = 0

          End If

      End Sub

This added code gives me the Run-time error 2108:

"You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method."

What will be the vba code that I have to add for the cursor to come back to CustomerId instead of positioning in the next field.

Thank you very much for any help I can get for solving this problem.  Best regards.

Answer 1

Hi Ken,  It worked smoooootly.... Thank you very much!!!!!

Answer 2

Try:

         'if count is greater than 0, then the CustomerID already exists

          If myR.RecordCount > 0 Then   

              MsgBox "A Customer with this ID is already exist"

              Cancel = True

              Me.CustomerId.SelStart = 0   

          End If