Error trapping on a form

  • Thread starter Thread starter Steve Walker
  • Start date Start date
S

Steve Walker

Access 2000 using WinXP. I am using combo boxes to collect the values I need
and then a button to run an append query which updates a table. My problem
is error trapping. When I update the table from the form the first time the
append query runs fine and adds the data to the table. If the user selects
the same values and tries to append the data again I get an error message
telling me that I cannot append which it should do as the data has already
been entered into the table.

How can I turn off this message. Looked at Echo in a macro and SetWarnings.
Had a look at converting the macro to VBA and then changing the error but to
no avail; my VBA is not good enough.

If anyone can point me in the right direction would be much obliged.

Steve
 
In your error handler you can add code similar to:

Select Case err.number
Case ???? 'Whatever the Error Number You are Getting
Resume Next 'Executes Next Instruction
Case Else
Your Regular Error Handler
End Select

While that may answer your question I think you should
check out the following code. Add this code, modified
for your application, to the "NotInList" event of your
combo box. It adds each new entry as you go along:

Private Sub cboStdResponse_NotInList(NewData As String,
Response As Integer)
On Error GoTo err_cboStdResponse_NotInList

Const stUserfunction As String
= "cboStdResponse_NotInList"

Dim Ctl As Control
Dim stNewValue As String
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qPa_AuditProgAnswers")
' Return Control object that points to combo box.
stNewValue = "Add New Standard Response of... " &
vbLf & vbLf & NewData
' Prompt user to verify they wish to add new value.
If MsgBox(stNewValue, vbOKCancel) = vbOK Then
rs.AddNew
rs!qPq_QuestionID = Me.qPq_QuestionID
rs!qPa_AnswerText = NewData
rs.Update
Response = acDataErrAdded
Me.cmdEdit.Enabled = True
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
Me.cboStdResponse.Undo
End If

exit_cboStdResponse_NotInList:
Set rs = Nothing
Set db = Nothing
Exit Sub

err_cboStdResponse_NotInList:

MyErrorHandler Me.Caption, stUserfunction,
Err.Description, Err.number
Resume exit_cboStdResponse_NotInList

End Sub
 
Back
Top