combo box question

[eelsar]

I have a combo box where I added to the "Value List" a few names of cities. How do I make it possible that when the user enters a name of a city that doesn't exist in the drop down value list of the combo box,it gets added to the list so when future users use this combo box this city is on the drop down list.

thank you.

 

[wemeier]

You can't.... at least the way you've set it up. There is a way to add a city to the Values list programmatically, but it only lives for the life of the active form. Once the form is closed it goes away.

It would be better to set up a small table with the names of the cities and base your combo box off the table. Then, when someone enters a city that is not on the list, add coding to the OnNotInList event to add the city to the table. This will make it available permanently.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[fneily]

Yeah, do the above - create a table with cities. Then on the comboboxes AferUpdate event putcombobox name is city)
Me![City].Requery

Combo and list boxes are intialized when the form is opened. So if someone types in a new city, you must requery (reinitialize) the combbox.

Neil

 

[eelsar]

To add code to the OnNotInList event sounds like it would work - could someone help me out with the code it would be very appreciated.

thank you

 

[JeremyNYC]

Here's some generic code you can use. You'll have to set Limit To List to Yes for the cbo. And, unless you're going to use my standard error handler, you'll want to replace the Call ErrorTrap... line with something of your own.

Public Function AddListItem(sTblNm As String, sFieldName As String, snewdata As String) _
As Integer
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo Error
Dim sSql As String
Dim lngID As Long

If vbYes = MsgBox("This does not match any existing entries. Do you want to permanently" _
& " add a new entry?", vbQuestion + vbYesNo, "New Data&quot Then
sSql = "INSERT INTO " & sTblNm & " (" & sFieldName & &quot VALUES ('" & snewdata _
& "')"
Call db.Execute(sSql, dbFailOnError)
AddListItem = acDataErrAdded
Else
AddListItem = acDataErrContinue
End If
Exit Function
Error:
Select Case Err.Number
Case 3022 ' dupe
Call MsgBox("This name is already in the drop-down list.", vbOKOnly + vbInformation, _
"Data Conflict&quot
Case Else
Call ErrorTrap(Err.Number, Err.Description, "AddListItem&quot
End Select
End Function

That bit goes in a standard module. Then, in the NotInList event of your combo box, put code something like this (same warning about the errorTrap call):

Private Sub cmbBank_NotInList(NewData As String, Response As Integer)
On Error GoTo Error
Response = AddListItem("tblBank", "BankName", NewData)
Exit Sub
Error:
ErrorTrap Err.Number, Err.Description, "cmbBank NotInList", "frmAccount"
End Sub

Hope this helps.

Jeremy

PS: Watch out for wrapped lines.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.