Value Lists vs Combo Box

[ones_zero]

Hello


I was needing to create a Combo box (Row source type - "Value List") in a table, but apparently the data I have to enter is more than allowed.
I currently have a combo drop down list box configured that is (row source type table/query)
The issue is that I'd like for the user to be able to edit the drop down list and add new data to the table when their entering their data, thats why I thought the value list would be what i needed.

Is there a way to configure this without configuring a "text box"?

Is there a way to edit a combo drop down box with a form when a user needs to enter new data thats not listed in the drop down box?

thanks for helping

[moke123]

Do a google search for Combo box not in list event.

[ones_zero]

Thanks for the guidance, I’m no expert with coding. Could you assist with it please?

I have all this data set as indicated but I need help with the coding.

- The combobox must have it's RowSource set to either a table or a query (not value list or file)

- The combobox must have the RowSource Type set to Table/Query.

- Set the Limit To List property to Yes

Here is what I have...
I have a form entry that allows users to add data to table called “work_tracker”
On this form is a combo box configured to pull data from table “Location” this allows a user to select data from this drop down list.

I don’t need to open a form, I just need the new data that’s not in the list to be added to table “Location” so the user can select it.

What would be the coding that I need to add get this new data added?
I have found some sample coding but I can’t get it to work.

I appreciate your help

Thanks

[moke123]

put this code in a standard module

Public Function AddNewToList(NewData As String, stTable As String, _
                                   stFieldName As String, strPlural As String, _
                                   Optional strNewForm As String) As Integer
On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record            

    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String

    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."

    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)

        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

        AddNewToList = acDataErrAdded                'Set response 'Data added'
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If

exit_proc:
On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function

err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc

End Function

In the property sheet for the combobox, under the event tab, select the not in list event

the code for the event is something like

Response = AddNewToList(NewData, "tblLocation", "Location", "Locations")

Note you'll need to add the correct names in this code.
AddNewToList(NewData, "YourTableName", "YourFieldName", "ThePluralforyourField")
the red argument is the name of your location table
the blue argument is the field name of the data your adding
the green argument is the plural, in your case I believe its "Locations"

thats about all you need.

[ArviLaanemets]

You create a combo with RowSourceTyp = 'Table/Query', RowSource like 'SELECT DISTINCT FieldName FROM TableName ORDER BY FieldName', where FieldName is name of field your combo is linked to (the value in ControlSource), and TableName is name of table from where you get field FieldName. LimitToList must be set 'False', BoundColumn = 1, ColumnCount = 1, ColumnWidths = '2.5' (or any valid value > '0').

When combo is clicked, you can select from full list of values entered into field FieldName in table TableName curently, or enter any new value. After you save the record with new value entered into combo, on next time you select the combo, the new value is added into RowSource list.

NB! you can use this setup only with single-column combo!

[moke123]

cross posted https://access-programmers.co.uk/for...44#post1662144

[ones_zero]

Hello
Here is the error i receive when I configure NotinList code


here is my code:

Private Sub Location_NotInList(NewData As String, Response As Integer)Response = AddNewToList(NewData, "tblLocation", "Location", "Locations")
End Sub

- Private Sub Location_NotInList is the name of my combo list. This is created when i click the 3 dots
- My table name is called "Location" do I put "tbl" in front of the name in the code?
- Location is the Field name in the table
- Locations is the plural name of my table

Can you guide me on what i need to change?

Sorry for multiple posts, I appreciate your help

[ones_zero]

I think i got it.

One quick question, after i add the "NotInList" data to the combo list. The data is added to the bottom of the list in the table.
I cant seem to get the data sorted in acceding order after new data is added.

no big deal just curious if there was a simple fix


thanks again for all your help

[moke123]

I cant seem to get the data sorted in acceding order after new data is added.

Set that in the underlying query.