Combobox changing data problem

[slickdog]

I have a simple form that uses a Main table for customers ‘TblCustomers’ that uses a sub table ‘TblCustCity’. Tabel Cust City has two Fields ‘ID” and “City_Town’.
I have a combo box that I can pick the town from, and can also add towns. With an ‘On Not in List’ event.:

Private Sub City_Town_NotInList(NewData As String, Response As Integer)
Dim TmpQuestion As String 'Temporary storage for the question
Dim TempCity As String 'Temporary Storage for the City data

'Get confirmation that this is not just a spelling error.
TmpQuestion = "Add '" & NewData & "' as a new City/Town?" ' store the question in TmpQuestion
If MsgBox(TmpQuestion, vbYesNo + vbDefaultButton2 + vbQuestion, "City/Town Not listed") = vbYes Then

'Store the new city name and table etc into TempCity
TempCity = "INSERT INTO TblCustCity(City_Town) VALUES ('" & NewData & "');"
DBEngine(0)(0).Execute TempCity, dbFailOnError

Response = acDataErrAdded
End If
End Sub


The adding of a new town City works ok but if I already have a town city in record and try to change it then I lose the old town /city and it’s replaced with a number.

The Control Source is:
City_Town

The Row source is:
SELECT DISTINCT TblCustCity.ID, TblCustCity.City_Town
FROM TblCustCity
ORDER BY TblCustCity.City_Town;

Limit to list is yes and Allow value list edits is No.

Can anyone explain why my data changes and how to correct this.
Example
I am in one customer record and his Town is Manchester. I decide to change this to Liverpool, Liverpool not being in the list so the ‘On Not in List’ event kicks in.
My new City Liverpool is added but when I look back Manchester has gone and is replaced with its ID number.

 

[BlueIshDan]

This post is just for our eyes. I'll take a look at it after my meeting.

Private Sub City_Town_NotInList(NewData As String, Response As Integer)
    
    Dim TmpQuestion As String   ' Temporary storage for the question
    Dim TempCity As String      ' Temporary Storage for the City data

    ' Get confirmation that this is not just a spelling error.
    ' store the question in TmpQuestion
    TmpQuestion = "Add '" & NewData & "' as a new City/Town?"
    
    If MsgBox(TmpQuestion, vbYesNo + vbDefaultButton2 + vbQuestion, "City/Town Not listed") = vbYes Then

        ' Store the new city name and table etc into TempCity
        TempCity = "INSERT INTO TblCustCity(City_Town) VALUES ('" & NewData & "');"
        DBEngine(0)(0).Execute TempCity, dbFailOnError

        Response = acDataErrAdded
        
    End If

End Sub

 

[arnelgp]

have you tried requerying your combo field.

 

[slickdog]

have you tried requerying your combo field.

Yes it has changed the underlying table and say if I had Manchester at row 3 it now states 3 instead of Manchester.

 

[arnelgp]

have you checked the combo control property:
Bound Column=1
Column Count = 2 (or more)
Column Widths = 0;1;....(etc)

 

[Minty]

It's unlikely to update correctly as NewData will be null if the record already exists I think...
Add a debug.print NewData to see what it is trying to pass through