Combo box NotInList event - why does this code not work

A

Andrew Smith

I have the following code in the NotInList event of a combo box. I'm sure
this used to work, but tonight I'm getting the default Access error message
appearing after the code runs. The code does successfully add the new data
to the table, but fails to refresh the combo box. This is in Access 2002

Any ideas?

Private Sub cboCompanyName_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset

If MsgBox("Do you want to add " & NewData, vbYesNo) = vbYes Then
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCompanies")
With rst
.AddNew
!CompanyName = NewData
!Ticker = InputBox("Ticker for " & NewData, "Ticker")
.Update
.Close
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
 
V

Vladimir

In MSDN you do as follows in ELSE clause:

Response = acDataErrDisplay ' Require the user to select
' an existing shipper.

See the whole MSDN example:

Private Sub ShipperID_NotInList(NewData As String, Response As Integer)

Dim dbsOrders As DAO.Database
Dim rstShippers As DAO.Recordset
Dim intAnswer As Integer

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add " & NewData & " to the list of shippers?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then

' Add shipper stored in NewData argument to the Shippers table.
Set dbsOrders = CurrentDb
Set rstShippers = dbsOrders.OpenRecordset("Shippers")
rstShippers.AddNew
rstShippers!CompanyName = NewData
rstShippers.Update

Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select
' an existing shipper.
End If

rstShippers.Close
dbsOrders.Close

Set rstShippers = Nothing
Set dbsOrders = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
 
A

Andrew Smith

Thanks for your reply.

My code is, in fact, perfectly correct and now works fine. The problem was
that I'd added some totally surperfluous code to the load event of the form
that screwed it up! Now I've taken this out it's working again.

I actually reposted this question, and have explained my stupidity more
fully in a reply to this second thread.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top