• Registered: 2015-12-24
  • Posts: 55

Topic: how to speed up Combo box population linked to a parent combo box.

Hello, everybody.

i have a database with approx. 3000 entries. it has two combobox. first one is for selecting the area & second one is populated data based on combobox-1 selection. but the issue is each selection in combobox-1 has around 1000 entries which took a large amount of time to populate the second combox. is there any way to speed up the combobox population.

sample data:

combo box-1 linked table:
id   Area                   desig
1    Domestic           d
2    International       i
3    Internal               e

Combobox-2 linked table
id   zone   id_area
1    d001   1              similarly 1000+ entries
nn  i001     2
.....

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,603

Re: how to speed up Combo box population linked to a parent combo box.

https://k245.ru/wp-content/uploads/2023/11/image-8-1024x573.png
For large data sets, I abandoned the combobox,
https://k245.ru/wp-content/uploads/2023/11/image-9-1024x598.png
replacing it with two components: an input field (to display the selected value) and a button (to display the reference form). Of course, you will need scripts, examples of which can be found in this project.
https://k245.ru/en/mvdb-en/light-filtered.html

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,077

Re: how to speed up Combo box population linked to a parent combo box.

Something like this

Post's attachments

Attachment icon 10k Combo.zip 483.87 kb, 104 downloads since 2023-11-13 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,786

Re: how to speed up Combo box population linked to a parent combo box.

Hello All,
There is also an option to make the combobox non-searchable (no need for a script  - see the attachment (and the screen capture in the attachment)).
This fixes the problem of the combobox taking a long time to retrieve the relevant data - however, although you can still SORT the combobox, you lose the functionality of being able to SEARCH the combobox and this may be too much of a compromise.
Acknowledgement to Sparrow for the test program and data.
Derek.

Post's attachments

Attachment icon 10k Combo no script.zip 572.28 kb, 96 downloads since 2023-11-13 

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,603

Re: how to speed up Combo box population linked to a parent combo box.

The problem of comboboxes is appears when adding new entries to the lookup table with a large number of comboboxes. The fact is that every time an entry is edited/added to a table, UpdateDatabase() is automatically executed for this table and, as a result, all comboboxes associated with it are updated. If you have 10 editing forms and each has a combobox, then the wait time may become unacceptable. The same thing happens when you launch the application.

As an option, you can display a splash screen when the application starts before it connects to the database (in the begin end. section) to brighten up the waiting time.

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,077

Re: how to speed up Combo box population linked to a parent combo box.

Hello All,


Konstantin, you are absolutely right in the description and delays that arise when working with ComboBox.
Indeed, all combo boxes are filled when the program starts. But are all of them and in all forms needed at once when working on large projects?
I will once again attach the 10k project now with 11 forms. Each form has two comboboxes, copies of the first one.
And a form for adding a record to a table with 10k records. A combobox with 10,000 entries is set with filter id=0 by default.
This allows us not to waste time loading so many records into all combo boxes of all forms at the same time. When you select a category in the first combobox, the filter of the second combobox changes, which narrows the number of entries. When you exit the form, the comboboxes are cleared and the filter id=0 is set again for the second combobox. As a result, adding a new entry to 10k entries is not such a problematic procedure. Sometimes this trick can speed up work.
In order to feel the difference in work, you can remove id=0 and disable clearing of comboboxes when closing forms.
---
Константин, вы абсолютно правы в описании и задержках которые возникают при работе с КомбоБокс.
Действительно, все комбобоксы заполняются при старте программы. Но все ли они и на всех формах нужны сразу в работе в больших проектах?
Я еще раз приложу проект 10к теперь с 11 формами. На каждой форме по два комбобокса, копии первого.
И форма для добавления записи в таблицу с 10к записей. Комбобокс с 10000 записей по умолчанию установлен с фильтром id=0.
Это позволяет нам не тратить время на загрузку такого количества записей во все комбобоксы всех форм одновременно. При выборе в первом комбобоксе категории, меняется фильтр второго комбобокса, что сужает количество записей. При выходе из формы комбобоксы очищаются и для второго комбобокса снова устанавливается фильтр id=0. Как результат , добавление новой записи к 10к записей не столь проблематичная процедура. Иногда такая хитрость позволяет ускорить работу.
Для того, чтоб ощутить разницу в работе, можно убрать id=0 и отключить очистку комбоксов при закрытии форм.

Post's attachments

Attachment icon 10k Combo-11Form.zip 487.51 kb, 98 downloads since 2023-11-14