Combo box (add to list)

  • Thread starter Thread starter will
  • Start date Start date
W

will

This code is used to add a name to the combo box list
whenever I run this I get the error message at the botton
of the code. It will not let me add a new name to the list
my table is named table1 and I put this event in the not
in list section of the properties on combo1 and i have
also set the peoperties to limit to list (yes)Is there
something else i am missing ?
can some one help ???

Private Sub combo1_NotInList(NewData As String, Response
As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE
Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current Field ?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 
Hi Will

Well, I can't see anything wrong with your code, but it would help to know
more about what error is occuring.

Add to your error message:
MsgBox "An error occurred. Please try again." & vbCrLf & Err.Description

Even better, put a proper error handler in your code:

Dim ...
On Error Goto ProcErr
strMsg = ...
...
If MsgBox(...) then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update
Response = acDataErrAdded
End If
ProcEnd:
On Error Resume Next
rs.Close
Exit Sub
ProcEnd:
MsgBox "An error occurred. Please try again." & vbCrLf & Err.Description
Response = acDataErrContinue
resume ProcEnd
 
Back
Top