Yes, another Combo Box question

[Guest]

I've read the discussions and tried different things until my head is
spinning, but it's still not working.
The list for my combo box is hard-coded into the RowSource property as a
value list, but I'd like my combo box to be able to store any new entered
value. From the discussions, I realise that I need some codings in the
NotInList event procedure. The problem is I don't understand anything about
the codes . I tried anyway, but it didn't work. My table is MerchantEntry and
the field name is City/Town1. Please have a look at my codes. Many thanks!

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

'Add the value to the RowSource property
Me.City_Town1.RowSource = Me.City_Town1.RowSource & ";" & NewData

'Tell Access you've added the new value
Response = acDataErrAdded


Dim db As Database
Set db = CurrentDb

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this value to the list?",
vbYesNo+vbQuestion,"Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO MerchantEntry (City/Town1) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the new value
Response = acDataErrAdded

Else

'The user clicked No - discard the new value
Me.City_Town1.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

db.Close
Set db = Nothing


End Sub

 

[Ken Snell \(MVP\)]

Using a value list means that any new values you add will be lost when the
form is closed. The revised value list is not saved when the form closes.

If you want the ability to add items to the combo box's list, you'll need to
use a table to store the values, and then you can add new items to the list
by storing them in the table.