Avoid SQL Server error messages in linked Access 1

[petermeachem]

using Access 97 linked to sql 6.5/7
If I try to add a duplicate record, I get a huge message box full of square brackets and gibberish, which the users dont like. Is there a better way of handling this than checking for duplicates in the key field after update event. This still doesn't work properly as the table, quite properly strongly objects to a blank primary.

 

[Cagliostro]

Is simple, you can not insert duplicate of primary keys. You can remove keys and all will be ok. John Fill

 

[petermeachem]

I despair sometimes.

I know you cannot have duplicate primaries. Thats what it means.

The users are typing in say customers. Suppose they make a mistake and type a code in that is already entered?

How can I avoid the big sql error message.

You just take indexes off to avoid error messages then do you. Good oh.

 

[Cagliostro]

You can also add an autoincrement column and set it to primary key and everything will be fine. John Fill

 

[MOP]

Oh to be a programmer, Life is so easy..... <smile>
Seriously, Maybe the primary key is there for a reason..... to PREVENT duplicate entries from being added to the database.
If you are adding the record using code you can trap all errors and write them to an error log for later analysis.

I use this code:
On Error GoTo AssayErr

<Insert statement here>

AssayErr:
Dim errX As Error
If Errors.count > 0 Then
For Each errX In Errors
' Debug.Print &quot;ODBC Error&quot;
' Debug.Print errX.Number
' Debug.Print errX.description
Errtab.AddNew
Errtab![errortext2] = &quot;Error no &quot; & errX.Number
Errtab![errortext1] = errX.description
Errtab![Operation] = &quot;Import&quot;
Errtab![id] =<Failed insert statement>
Errtab.Update
Next errX
End If
Resume Next

This will loop through all errors at all error levels. EG ODBC error then SQL error etc







********Byte me ********

 

[Cagliostro]

MOP,
The problems was what was required to allow inserting of duplicates, even they are part of key. It means what the design is not completly the needed design. Read please more attentive the questions. John Fill

 

[petermeachem]

Sorry, I seemed not to get notification of the above.

I don't think any of you read/understood the problem. I repeat.

If I try to add a duplicate record, I get a huge message box full of square brackets and gibberish, which the users dont like. Is there a better way of handling this than checking for duplicates in the key field after update event. This still doesn't work properly as the table, quite properly strongly objects to a blank primary.

As am example, suppose the table is a list of animals, unique names you see.
The happy users are typing into an Access continuous form. No code at all.

Suppose the table already contains cow, pig, goat and someone types in pig by mistake.

They get a whacking great sql error box.

I don't want that.

Afer update on error does not work as the error turns up before that event fires.

I could run a query in before update to check for duplicates but that would be stupid.

Any other bright ideas?

 

[Guest_imported]

Private Sub Form_Error(DataErr As Integer, Response As Integer)

This module will trap a duplicate key error, and SQL errors resulting from missing required fields.
Put this in the OnError event of the form.
Dim wError As Error
Dim strMsg As String

Select Case DataErr
Case 3022

Response = acDataErrContinue
MsgBox &quot;Count Number &quot; & Me!CountNumb & &quot; already exists, please try again.&quot;
Me!CountNumb.SetFocus
'Response = acDataErrContinue

Exit Sub
Case 3146
'Check for Required Fields
If IsNull(Me!RcwDesc) Then
Response = acDataErrContinue
MsgBox &quot;RCW Description is required.&quot;
Me!RcwDesc.SetFocus
Exit Sub
End If
Case 3162
'Check for Required Fields
MsgBox &quot;Can't assign a null value to the current field.&quot;
Response = acDataErrContinue

Exit Sub


Case Else
MsgBox &quot;Unknown Data Error &quot; & Str(DataErr)
End Select

'For Each wError In DBEngine.Errors
'
' 'Check for Duplicate Key
' If wError.Number = 3305 Then
'' MsgBox &quot;Count Number &quot; & Me!CountNumb & &quot; already exists, please try again.&quot;
'' Me!CountNumb.SetFocus
'' Response = acDataErrContinue
''
'' Exit Sub
'
' End If
'
'Next wError

End Sub

 

[petermeachem]

Thanks, I hadn't tried form error. Cheers Peter Meachem
peter@accuflight.com