Specifying Error Messages

[djackson]

Ok, here's the scenario for your lovely selfs:

I have a form to add records. On this form is an "Add Record" button, that of course adds the record when the data is entered.
2 of the fields on this form (CompanyName and PostCode) are related to 2 fields in the main table that are indexed, so that the combination of the 2 has to be unique. Ie. The user cannot add a record if the Company Name AND Postcode are duplicated.

6 of the fields on the form are required (including the CompanyName and PostCode fields). Therefore, the user cannot add the record any of the fields are blank.

I have managed to set it so that if either problem exists (duplicate record or missing required field), an error message pops up and the record cannot be added until the problem is rectified.

Using Err.Number, i have found that the error number is 2105 - "Cannot go to specified record", regardless of which problem is occuring.
I want to be able to tell the user exatly what the problem is, without having to say "It could be this or it could be that".

I believe this can be done with Select Case, but that works the different error numbers.
My question is, why am i only getting the one error number regardless of what the problem is?
And then, how can i differentiate between them?

I am pretty new to Access, so please forgive me if this is a daft question.

Cheers,

Dave

 

[pbaldy]

You can test behind your button before attempting the save. You can use a DCount for the first, more info here on syntax:

http://www.mvps.org/access/general/gen0018.htm

this type of thing to test for the others being empty:

If Len(Me.ControlName & vbNullString) = 0 Then

 

[djackson]

Hi Paul

Thank you very much for your response, but i still don't understand. Like i said, i am very new to this.

I tried implementing the dcount function, but wasn't entirely sure if it was working (I was just getting the same error message whether i was trying to add a duplicate or not). And i don't know where I should put the "If Len(Me.ControlName & vbNullString) = 0 Then" function that you suggested.

I have returned my On Click event procedure to how i had it. See below:

Private Sub AddRecord_Click()
On Error GoTo Err_Addrecord_click
  DoCmd.GoToRecord , , acNewRec
 
Exit_AddRecord_Click:
  Exit Sub
Err_Addrecord_click:
  MsgBox "There is an error. Please rectify"
  Resume Exit_AddRecord_Click
 
End Sub

As you can see, instead of "There is an error", i would like to be able to say "The error is...".

Could you please advise how i should use your suggestions?

Again, many thanks,

Dave

 

[pbaldy]

Let's start with the first one:

If DCount("*", "TableName", "CompanyName = '" & Me.CompanyName & "' AND Postcode = '" & Me.PostCode & "'") > 0 Then
  MsgBox "You already have this combination"
  Exit Sub
End If

DoCmd.GoToRecord , , acNewRec

Changing to your actual table and field names. This presumes the 2 fields are both text.

 

[djackson]

Sir, you are a genius!!
That works perfectly. Thank you!

I now have it that if there is a duplicate (using your very helpful suggestion), i get an error message saying that the comination exists. And if there is any other error, such as a required field is not entered, the message shows "Ensure all required fields are entered".

I don't expect there to be any other types of errors, so this is adequate. But how would I (or anyone else reading this) use the "If Len(Me.ControlName & vbNullString) = 0 Then" thing that you suggested?

Again, thank you very much

Dave

 

[pbaldy]

Same type of thing:

If Len(Me.ControlName & vbNullString) = 0 Then
  MsgBox "You missed the such and such field dummy"
  Exit Sub
End If

That tests for both Null and a Zero Length String, which are visually the same but programmatically different.