Auto-complete combo box

Discuss the database features
Post Reply
Final8Demise
Posts: 3
Joined: Mon Nov 20, 2017 8:18 pm

Auto-complete combo box

Post by Final8Demise »

So database management is definitely not one of my strong suites. I have multiple tables but currently I need help trying to understand how to achieve what I'm after....if it is even possible.
I have 2 tables, tbl_Color and tbl_Options. The tbl_Color table has 3 fields: color_id (primary, auto), color (text), and options. The options are shortcut keys like ^, #, A, V, which mean certain things. I could easily just have a text field that I can input the required options but I have multiple colors that have the same set of options. Thus, I created a tbl_Options table.
So where I'm stuck is this: I would like to have a form that uses a combo-box like auto-search feature to list the colors as the user types. I would then like to have the same feature for the options. While the user types, it lists the options as they go, however, if there is no option matching what the user typed, I would like it to be saved as a new entry. Is this even possible? Everything I've thrown at it I cannot seem to make it work like this.
Last edited by FJCC on Mon Nov 20, 2017 8:58 pm, edited 1 time in total.
Reason: changed title to be descriptive
OpenOffice 2.4 / Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-complete combo box

Post by Villeroy »

You want a list box: viewtopic.php?f=13&t=91253
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Auto-complete combo box

Post by UnklDonald418 »

If I understand correctly you want to save the selected option in the options field of tbl_Color
if there is no option matching what the user typed, I would like it to be saved as a new entry.
To do that you could use a ComboBox on the form, but you wouldn't need tbl_Options.
Use a query like this for the data source for the ComboBox

Code: Select all

SELECT "options" FROM "tbl_Color" GROUP BY "options" ORDER BY "options" ASC;
Of course for this to work the option field would need to be a text field, not a Foreign Key from tbl_Color

If you use a ListBox to select a Foreign Key from tbl_Color from tbl_Options you would need a separate Form to enter a new option.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Final8Demise
Posts: 3
Joined: Mon Nov 20, 2017 8:18 pm

Re: Auto-complete combo box

Post by Final8Demise »

@Villeroy: Thank you for your response. Does a list box allow for auto-search as the user types? I was under the impression that it only looks at the first character.


@UnklDonald418: Thank you for your response.
If I understand correctly you want to save the selected option in the options field of tbl_Color
No sorry. I would like it saved into an Options table. I was able to manually input data and connect the combo box on the form, however, I could never get a new entry to save. Always would error out no matter what I tried. Part of that is because the combo box has SQL query looking for data, not inputting data.
OpenOffice 2.4 / Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-complete combo box

Post by Villeroy »

This forum has hundreds of downloadable example documents with forms and list boxes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Auto-complete combo box

Post by UnklDonald418 »

ListBox controls are used to select a value from one table and store it in another table. ComboBox controls work better if the source and destination tables are the same.
You are correct, ListBox controls
only looks at the first character.
plus they only allow choices that already exist in the source table for the query.
A separate Form is needed to add new values to the list.
You said
Part of that is because the combo box has SQL query looking for data, not inputting data.
ListBox and ComboBox controls have two parts.
For the first part, look at the Data tab of Properties: List Box dialog you will see a Data field property. That needs to be connected to the table field where you want the selected data to be saved.
The second part is the data source for the list. On the Data tab of Properties: List Box dialog the Type of list content is used to define whether you want to populate the list from a Table (requires a specially formatted table), a Query (a predefined query), Sql which is a query contained in the control, or Tablefields which are a fixed list of values.
The safest choice is Sql that way if someone messes with the predefined queries it doesn't break your ListBox control. List content is where the query is specified, click on the the ellipsis to open the Query design GUI.
ListBox controls display what is in position 0 of the query and stores what is in position 1 (the Boundfield).

Code: Select all

SELECT "option", "optionID" from "tbl_Options" ORDER BY "option";
displays a list of values stored in the "option" field of "tbl_Options" but stores the value of "optionID" into the Data field.

Likewise the Data tab of Properties: Combo Box dialog has a a Data field property, Type of list contents and List content. A Combo Box stores the displayed value (there is no Boundfield). If the Data field and the List Content are the same table and field you can add new values from the ComboBox, but if they are different then like a ListBox you would need another Form to enter new values into the list.
Ve aware that to add new values from Combo Box the table's Primary Key field must be set to AutoValue Yes.

What isn't clear to me is the relationship between tbl_Color and tbl_Options.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-complete combo box

Post by Villeroy »

UnklDonald418 wrote:ListBox controls are used to select a value from one table and store it in another table. ComboBox controls work better if the source and destination tables are the same.
You are correct, ListBox controls
only looks at the first character.
plus they only allow choices that already exist in the source table for the query.
You can type full names into a listbox unless you type very slowly. In most cases, what you store differs from what you type. You store the primary key that belongs to the typed item name in a foreign key field.

A combo lets you type and store any text you want. You must not bind it to a foreign key field.
Attachments
listbox.odt
(13.46 KiB) Downloaded 214 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply