Proper case for a combo box NotInList event (1 Viewer)

[usm01]

I have a problem getting a proper case function to work in my combo box. I have a combo box [City] with some code in the On Not in List event that adds new entries not already in the source table. The proper case function (i.e. City = StrConv(City, vbProperCase) is used in most of the other form fields and works fine, but does not have the desired effect in the combo box.
I have tried to put the proper case line in AfterUpdate event, but it does not do anything.

Not in List event Code i am using is

Private Sub City_NotInList(NewData As String, Response As Integer)
Dim strsql As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Item...")

If i = vbYes Then
strsql = "Insert Into Customer ([City]) " & _
"values ('" & NewData & "');"

CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If



The Reason i think is
combobox is storing the CityID in that field (which is rather Hidden), not the CityName, which is autonumber . and StrConv works on text.



Any Solution to this Problem.

 

[usm01]

i found the solution here is the Modified code

Dim strsql As String
Dim i As Integer
Dim Msg As String
Dim s As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Item...")
strsql = StrConv(NewData, vbProperCase)
If i = vbYes Then
s = StrConv(NewData, vbProperCase)
strsql = "INSERT INTO Customer(city) " _
& " SELECT '" & s & "';"

CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If