Adding new entries to a listbox

[judgehopkins]

I have a list of counties that our company uses and is stored in a table called Counties. I want to make a simple form that has a combo box that lists the names of all the counties AND also allows the user to enter in a new county name if necessary. I can make the combo box that lists all of the counties, but when I go to type in a new county name, it does not save the new county name.

Here was my proposed solution (other solutions are welcome):

In the combo box properties (data tab), I set the 'Limit To List' property to Yes.

Still in the properties, event tab, I double clicked in the 'On Not in List' property. [Event Procedure] which appeared along with a box with 3 dots (at the right). I clicked on the box and enter the code show below. (I use MS Access 2002 set to save as an Access 2000 file.)

 
    Dim db As Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Counties")
    rst.AddNew
        rst!Counties = NewData
    rst.Update
    Response = acDataErrAdded
    rst.Close

I also want to pop up a message box that would ask the user if they want to add a new county to the list. If yes, then above code would run. If no then I would need the line of code below.

Response = acDataErrDisplay

This would display a message that the entered value is not in the list and they must make a selection that is in the list.

When I try to use the code, the debugger stops at:

Dim db As Database

and I get an error message that says:

User-defined type not defined

What am I doing wrong?

[PinkPanther2003]

Hey there Judge,

Open a module.

Goto Tool > References

Go and select (check) Microsoft DAO 3.6 Object Libary

Then

Dim db as DAO.Database

Access 2000 and XP use ADO as the default data access library. ADO doesn't serve a database object therefore the error message.

Access 97 uses DAO as it's default data access library.

You could try rewriting the code using ADO (I'm assuming you know how to do that).

Now cause a Recordset object is served by both libaries I would recommend prefacing your data access objects with its server name eg DAO.Recordset OR ADODB.Recordset.

HTH,

Peter

[judgehopkins]

Thanks!