Cannot add new item to list

PS:  To populate the Locations table before updating the referencing table execute the following 'append' query:

INSERT INTO Locations(Location)

SELECT DISTINCT Location

FROM [YourCurrentTableNameGoesHere]

ORDER BY Location;

You should not be using a value list as the control's RowSourceType property, but Table/Query.  A value list should only be used for sets of values which are immutably fixed in the external world, e.g. days of the week or months of the year.  The locations should be stored in a table like this:

Locations

….LocationID  (autonumber PK)

….Location      (short text)

You can then use the following as the control's RowSource property:

SELECT LocationID, Location FROM Location ORDER BY Location;

The control's other properties should be:

ControlSource:    LocationID

BoundColumn:    1

ColumnCount:     2

ColumnWidths:   0

By setting the ColumnWidths value to zero this hides the first column, so that only the location names are shown, but the value of the control is the numeric LocationID.

The referencing table into which the combo box is used to insert a value should contain an LocationID column of long integer data type in place of the current text column.  Firstly add this to the table design and save the table.  Then update the table with the following 'update' query:

UPDATE [TableNameGoesHere] INNER JOIN Locations

ON [TableNameGoesHere].Location = Locations.Location

SET [TableNameGoesHere].LocationID = Locations.LocationID;

where Location is the name of the text column in the current table.

Top add a new location to the list put the following code in the control's NotInList event procedure:

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strSQL = "INSERT INTO Locations(Location) VALUES(""" & _

            NewData & """)"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDB.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

Hi Dennis, Your clarification confirms what I thought the problem was. The Location combobox is using a Value List as the Rowsource of the combo. And yes that has a limitation on the number of characters that can be in the list. A Value List should only be used when there is a short static list of items.

The way around this is to replace the Value List with a table. Create a table of Locations. It can be only one field. One way to do this easily is to go into Query Design mode and create a query on the table with only the Location field. Then open the Properties for the query and set it to Unique records. When you run the query it should give you a list of the unique locations. You can then turn the query into a Make Table query and make a table of the Locations.

Then you need to open the form in Design mode and select the combobox. Open the properties dialog and in the Data tab change the Rowsource type to Table/Query and change the Rowsource to the table you just made. 

Now there is one more thing you need to do and this is the most complex. You need to open the Events tab and click on the ellipses next to the Not-IN-List event . You need to change the code to deal with a table instead of a value list. You can find an example of the code here:

https://docs.microsoft.com/en-us/office/vba/api/Access.ComboBox.NotInList

I think it's some sort of a size limitation. It seems that that field can only contain so many characters and I've reached some sort if a limit and no more data can be added to the list of locations because it's "full". I discovered this by going into design view and looking at the properties for the "location" field. It's like this: "Alberta","Albuquerque","Atlanta","Atlantic City", etc. and I think this "string" has reached it maximum capacity but I need to be able to add more options to this list. Is there any way to allow this field to show more options? Of course I can verify this by deleting one of the existing locations and see if I can add a new one then, and I'm pretty certain that I'll be able to, but this isn't a workable solution for me. HELP! :)