Add free text to dropdown list

[HansN]

Hello,

I have in my form a dropdown list for "size", and the options in the list are: XS, S, M, L, XL.

I would now also like to add an option for free text in this dropdown, for example to be able to type: "4mm"

Is it possible to create such a dropdown list, where there are some predetermined options to choose from, but also an option to enter my own text?



Thanks!

[pbaldy]

One option is to set the Limit to List to No, which should allow the user to type in free text.

[June7]

Is this dropdown a combobox with ValueList as source?

If you want to allow these 'free text' inputs to be included in list for future use, couple options with combobox set for Table/Query as source:

1. lookup table as source for list and combobox NotInList event can be used to add new entries - combobox LimitToList Yes

2. use the data table as source for list: SELECT DISTINCT Size FROM datatable; - combobox LimitToList No

[HansN]

Thanks, I will try these suggestions!

[Welshgasman]

I would go with the NotInList option.

[Edgar]

Hans, I'd say you have two options that depend on your context.

Option 1. If your dropdown list is set to a list, not a table/query, then just set Limit To List to No and you can enter any value in your dropdown list, including those in the list.

Option 2. If your dropdown list comes from a table/query, if it's a lookup table, then you could implement a popular approach which involves choosing a record labeled "Other" which then displays a textbox where you can write that "other" option. To do that, you need to:

1. add a column to the table where you're storing the size, name it "OtherSize" or something similar
2. add the record "Other" to your sizes table
3. add a hidden textbox to your form, that is, set its visible property to No by default
4. in the change event of your combo box, add the code to change its visible property to yes when "Other" is selected
5. in the current event of the form, make sure that textbox is displayed when "Other" is selected, keep it hidden if "Other" is not selected
6. when "Other" is selected, also make sure to set focus to the textbox now that is visible
7. in the before update event of the form, ensure that if "Other" is selected, the textbox is not empty and viceversa

Your users are probably familiar with this approach, it's widely used, although it is some work. Check the attached for more info.

[June7]

Whether combobox source is list or table, can have LimitToList set to No to enter anything into field.

Are you suggesting having 2 fields for this input?

[Edgar]

Whether combobox source is list or table, can have LimitToList set to No to enter anything into field.

Absolutely. However, allow me to remind you that, regardless of what LimitToList is set to, if the column has referential integrity applied, the constraint won't let you add values that don't belong to the list.

Are you suggesting having 2 fields for this input?

Yes. I'm suggesting having 2 fields/columns for the input of size in the context of this database and many other kinds of scenarios. Like Zip codes, countries, states, cities, genders, religions, medical conditions, pretty much anything with a "type of ___" that can have additional options that do not appear in the default list.

Having 2 fields is used when you want to maintain the constraints. It's up to the developer to determine if it's optimal for the database.

[ArviLaanemets]

One possible way to do this is:
1. Set LimitToList to No (NB! As I remember, to make this to work properly the RowSource of combo must be single-column one!);
2. Set RowSource of combo as like "SELECT DISTINCT SourceField FROM FormSourceTable ORDER BY 1".

Now in your combo you can select any value inserted into SourceField previously. You can enter any new value into combo too, and next time you select a value in your combo, this value is displayed in selection list too.

[June7]

Was suggested in post #3.

[moke123]

I have a table, tblTaskList, which consists of 2 fields - TaskLstID and strTask.

The combo box (named txtTask) rowsource is "SELECT tblTaskList.TaskLstID, tblTaskList.strTask FROM tblTaskList;"

The bound field in the combo is 2, the text field, not 1 the primary key field.
Limit to list is no. The combo is bound to a field in tblTasks.

When a user types in a new task that is not in the list I use an after update event to mimick the not in list event.

Private Sub txtTask_AfterUpdate()

    If DCount("*", "tblTaskList", "strTask = """ & Me.txtTask & """") = 0 Then

        Select Case MsgBox(Chr(34) & UCase(Me.txtTask) & Chr(34) & " is not in the list." & vbNewLine & vbNewLine & "Do you want to save it for future use?", vbYesNo, "Confirm Save")

            Case vbYes
                CurrentDb.Execute ("Insert into tblTaskList(strTask) values(""" & Me.txtTask & """)"), dbFailOnError
                Me.txtTask.Requery
            Case vbNo

        End Select

    End If

End Sub

[HansN]

Thanks for all the great advice, I will try your suggestions!