Problem with NotInList (2nd post)

[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

 

[Wayne Morgan]

Right off-hand, the code looks good. What error are you getting?

 

[Andrew Smith]

The code runs OK, but then I get the standard Access error that the item is
not on the list - and it's not in the list as the combo box has not
refreshed. However, it has been added to the table, and will appear in the
list if the form is closed and then reopened.

 

[Wayne Morgan]

I would try deleting and recreating the combo box. If that doesn't work, I would try a
decompile of the database.

To decompile:
Open the database with the following command line:

"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /excl /decompile

Add any other command line arguments you may currently use for user, password, system
database, etc. Once the database is open, do a Repair and Compact from the Tools menu.
Next, open the code editor (Alt+F11) and run the Compile MyDatabase command from the Debug
menu.

The decompile works well in mdb file that are in the Access 2000 format or older. There is
a bug in the 2002 file format that doesn't let the decompile work well. If the file is in
the 2002 format, create a new mdb file and import all of the objects from the current
file.

 

[Andrew Smith]

Many thanks for your help. I tried all of your suggestions and none of them
worked, then I discovered that the problem was with some code that I'd added
to the load event of the form.

This created a recordset object and then set the combo box recordset to the
recordset object. I can't remember doing this, and have no idea why I did
it, but it didn't get there by itself so I suppose it must have been me.
Anyway, take out this code and it works fine - just like it did to start
with in fact.

Sorry for wasting your time, and thanks again.

Andrew