Tạo UserForm có nhiều Dependent Comboboxes với Database từ một file excel đang đóng

[zavtra]

Dear Cả nhà,
Trong đính kèm mình có chứa 2 file excel, một file là dùng để gọi UserForm: Các Comboboxes (Group, Section, TagName, Work/Note, Type...) thuộc UserForm này PHỤ THUỘC NHAU, Ví dụ:
- Khi Show khởi tạo UserForm thì các Comboboxes sau được gán dữ liệu một cách độc lập: Group, Section và Type là 3 Combobox độc lập nhau
- Các combobox còn lại có dữ liệu phụ thuộc: TagName (SourceRange của nó được đặt tên bằng cách ghép giá trị của 2 combox Group và Section), Work/Note thì có SourceRange phụ thuộc vào giá trị của Type (trong file đính kèm mới xây dựng cho giá trị Tieutu) và giá trị của TagName.
Ví dụ khi chọn ở Type là Tieutu, và chọn ở TagName là P06101A, thì SourceRange của Work/note sẽ bị thay đổi
- Do Combobox có nhược điểm không thể MultiSelect, nên một Texbox tên là Work/Note Fill để giải quyết bài toán: Khi chọn từng giá trị của Work/Note thì các giá trị đó sẽ được lưu lại tại TextBox này ===> Đây là một TextBox giải quyết bài toán khi muốn chọn nhiều phần tử trong combobox Work/note.

Khó khăn nhờ giúp đỡ:
- Trước kia mình để Database là một sheet trong workbook gốc (Workbook có UserFrom) thì không vấn đề gì.
- HIện tại mình đưa toàn bộ Database ra một file Excel ngoài (Workbook độc lập, vì database quá nặng), viết lại Code cho UserForm để dùng ADODB truyền dữ liệu vào các Comboboxes mà KHÔNG MỞ DATABASE>>>UserForm mới này bị ảnh hưởng của Function sử dụng cho gọi Database nên không được kiểm soát tự chủ, ví dụ Khi Work/Note change, thì TextBox không nhận giá trị, chứ năng multiselect không hoạt động (Textbox: Work/Note Fill không nhận được dữ liệu); Cảm giác như bị nặng hơn trước (Khi sử dụng Database là một sheet của workbook, không sử dụng ADODB, gọi trực tiếp SourceRange theo tên đã đặt)

Kính mong mọi người giúp đỡ.
Cảm ơn nhiều a.

 

[ongke0711]

- Do Combobox có nhược điểm không thể MultiSelect, nên một Texbox tên là Work/Note Fill để giải quyết bài toán: Khi chọn từng giá trị của Work/Note thì các giá trị đó sẽ được lưu lại tại TextBox này ===> Đây là một TextBox giải quyết bài toán khi muốn chọn nhiều phần tử trong combobox Work/note.

Tại sao không dùng Listbox để phục vụ việc multiSelect.
Dùng 1 Listbox có thêm tuỳ chọn checkbox từng dòng có thể thay thế 2 control trên Form của bạn là: combo Work/Note + textbox Work/Note fill.
Nếu vẫn muốn dùng textbox Work/Note fill để hiển thị thông tin riêng biệt cho dễ theo dõi thì tôi khuyên bạn nên đổi nó thành listbox để tận dụng các thuộc tính của nó. Textbox không phải là lựa chon tốt để chèn nhiều dòng dữ liệu.
Với textbox Work/Note fill hiện tại, nếu bạn muốn xoá một dòng bất kỳ trong đó thì bạn thao tác như thế nào? có thuận tiện và khoa học không? hay dùng chuột quét rồi xoá, hay bấm Backspace từng cái để xoá?
Nếu Listbox thì chỉ cần chọn 1 dòng rồi bấm nút Xoá (tất nhiên là phải code cho nút Xoá).

ADODB truyền dữ liệu vào các Comboboxes mà KHÔNG MỞ DATABASE>>>UserForm mới này bị ảnh hưởng của Function sử dụng cho gọi Database nên không được kiểm soát tự chủ, ví dụ Khi Work/Note change, thì TextBox không nhận giá trị, chứ năng multiselect không hoạt động (Textbox: Work/Note Fill không nhận được dữ liệu);

Do code sai.
Sửa dòng code này:
If Me.WorkNotecbb.RowSource <> "" Then ==> If Me.WorkNotecbb.Value <> "" Then

Cảm giác như bị nặng hơn trước (Khi sử dụng Database là một sheet của workbook, không sử dụng ADODB, gọi trực tiếp SourceRange theo tên đã đặt)

Kết nối với nguồn dữ liệu bên ngoài bao giờ cũng có độ trễ so với dữ liệu nằm ngay trong ứng dụng.
- Bạn nên khai báo phương thức: rsConn.CursorLocation = 3 'adUseClient để sử dụng các thư viện ngay trên file Excel đang xử lý chứ không phải trên file Database.xlsm. Bên cạnh đó nếu file của bạn không phải làm cho đa người dùng và nó cũng chỉ nằm trong mạng LAN, không chiếm băng thông gì hết thì cứ giữ Connection liên tục, chừng nào thoát Form thì mới ngắt kết nối. Những cách này cũng cải thiện tốc độ đó.
- Một cách cải thiện tốc độ thứ 2 đó là dùng phương thức "ADO Recordset Filter" để gán RowSource cho các combobox liên quan. Các combobox của bạn không phải là combobox độc lập như bạn nói đâu mà nó là Cascading (phân tầng) combobox: ComboBox con, cháu tuỳ thuộc vào dữ liệu của combobox cha/ông trước đó. Do CSDL của bạn tổ chức rời rạc, không có sự liên kết (CSDL quan hệ) nên không dùng được cái này.
Nói về ADORecordset.Filter, bạn chỉ cần load một cái ADO recordset tổng thể lên memory sau đó sẽ tuỳ từng combobox mà đưa vô điều kiện filter sau đó gán source cho Combo từ filtered recordset. Cách này khắc phục code bạn đang làm là mỗi lần thay đổi combobox lại phải gọi thủ tục kết nối, lấy recordset làm ảnh hưởng tốc độ xử lý.
Nói chung muốn cải thiện tốc độ thêm thì phải xây dụng lại CSDL thôi. Tôi nhìn vô cái file Database của bạn thì thấy sẽ mất thêm nhiều code để xử lý, để ra một đống Name range rồi mới có dữ liệu phục vụ được cho cái file Combobox test.xlsm

 

[zavtra]

- Tại sao không dùng Listbox để phục vụ việc multiSelect.>>>Mình muốn: Cho phép User điền Text khác với Data có sẵn (việc này ListBox không hỗ trợ)
Dùng 1 Listbox có thêm tuỳ chọn checkbox từng dòng có thể thay thế 2 control trên Form của bạn là: combo Work/Note + textbox Work/Note fill.
- Nếu vẫn muốn dùng textbox Work/Note fill để hiển thị thông tin riêng biệt cho dễ theo dõi thì tôi khuyên bạn nên đổi nó thành listbox để tận dụng các thuộc tính của nó. Textbox không phải là lựa chon tốt để chèn nhiều dòng dữ liệu>>> Dùng thêm TextBox Work/Note fill là để giải quyết cái nhược điểm của Combobox (Không chọn được nhiều phần tử) so với ListBox, và cái dữ liệu cuối cùng để đưa vào Cell chính là từ Textbox này.
- Với textbox Work/Note fill hiện tại, nếu bạn muốn xoá một dòng bất kỳ trong đó thì bạn thao tác như thế nào? có thuận tiện và khoa học không? hay dùng chuột quét rồi xoá, hay bấm Backspace từng cái để xoá? >>> Đúng là mình chỉ giải quyết bài toán một chiều.
Nếu Listbox thì chỉ cần chọn 1 dòng rồi bấm nút Xoá (tất nhiên là phải code cho nút Xoá).

Cảm ơn Bạn!

Bài đã được tự động gộp: 22/11/19

Do code sai.
Sửa dòng code này:
If Me.WorkNotecbb.RowSource <> "" Then ==> If Me.WorkNotecbb.Value <> "" Then

Cảm ơn Bạn!

 

[zavtra]

Kết nối với nguồn dữ liệu bên ngoài bao giờ cũng có độ trễ so với dữ liệu nằm ngay trong ứng dụng.
- Bạn nên khai báo phương thức: rsConn.CursorLocation = 3 'adUseClient để sử dụng các thư viện ngay trên file Excel đang xử lý chứ không phải trên file Database.xlsm. >>> Bạn có thể giải thích kỹ hơn là hiện trạng của code mình viết và nếu thêm đoạn <rsConn.CursorLocation = 3 'adUseClient>
thì sẽ thay đổi như thế nào!
- Bên cạnh đó nếu file của bạn không phải làm cho đa người dùng và nó cũng chỉ nằm trong mạng LAN, không chiếm băng thông gì hết thì cứ giữ Connection liên tục, chừng nào thoát Form thì mới ngắt kết nối. Những cách này cũng cải thiện tốc độ đó. >>> Ý bạn là xóa 2 câu
<Set rsData = Nothing
Set rsConn = Nothing> tại Public Sub PopulateComboboxRecordset, Khi đó phải đóng toàn bộ kết nối tại Event nào của UserForm là hợp lý nhỉ?
- Một cách cải thiện tốc độ thứ 2 đó là dùng phương thức "ADO Recordset Filter" để gán RowSource cho các combobox liên quan. Các combobox của bạn không phải là combobox độc lập như bạn nói đâu mà nó là Cascading (phân tầng) combobox: ComboBox con, cháu tuỳ thuộc vào dữ liệu của combobox cha/ông trước đó. Do CSDL của bạn tổ chức rời rạc, không có sự liên kết (CSDL quan hệ) nên không dùng được cái này.
Nói về ADORecordset.Filter, bạn chỉ cần load một cái ADO recordset tổng thể lên memory sau đó sẽ tuỳ từng combobox mà đưa vô điều kiện filter sau đó gán source cho Combo từ filtered recordset. Cách này khắc phục code bạn đang làm là mỗi lần thay đổi combobox lại phải gọi thủ tục kết nối, lấy recordset làm ảnh hưởng tốc độ xử lý.>>> Đúng là mình biết đang bị vướng chỗ gọi lại các Recordset quá nhiều lần, cũng đã suy nghĩ đến hướng có cách nào load một cái một xài luôn, nhưng kiến thức về tổ CSDL còn hạn chế, cũng như chưa biết đến phương thức "ADO Recordset Filter", xây dựng lại CSDL không thành vấn đề, nhưng vấn đề cấu trúc lại như thế nào để sử dụng phương thức "ADO Recordset Filter".
Bạn có thể giúp mình ví dụ phác thảo? Hiện tại CSDL của mình đang theo hướng: Các Range chứa Data cho các Combobox con/cháu được đặt tên bằng cách ghép Value từ các Combobox Cha/Ông. Bạn giúp mình mô tả về CSDL mới nhé.
Trân trọng cảm ơn!

Nói chung muốn cải thiện tốc độ thêm thì phải xây dụng lại CSDL thôi. Tôi nhìn vô cái file Database của bạn thì thấy sẽ mất thêm nhiều code để xử lý, để ra một đống Name range rồi mới có dữ liệu phục vụ được cho cái file Combobox test.xlsm

 

[ongke0711]

>>> Bạn có thể giải thích kỹ hơn là hiện trạng của code mình viết và nếu thêm đoạn <rsConn.CursorLocation = 3 'adUseClient>
thì sẽ thay đổi như thế nào!

>>> Ý bạn là xóa 2 câu
<Set rsData = Nothing
Set rsConn = Nothing> tại Public Sub PopulateComboboxRecordset, Khi đó phải đóng toàn bộ kết nối tại Event nào của UserForm là hợp lý nhỉ?

>>> Đúng là mình biết đang bị vướng chỗ gọi lại các Recordset quá nhiều lần, cũng đã suy nghĩ đến hướng có cách nào load một cái một xài luôn, nhưng kiến thức về tổ CSDL còn hạn chế, cũng như chưa biết đến phương thức "ADO Recordset Filter", xây dựng lại CSDL không thành vấn đề, nhưng vấn đề cấu trúc lại như thế nào để sử dụng phương thức "ADO Recordset Filter".
Bạn có thể giúp mình ví dụ phác thảo? Hiện tại CSDL của mình đang theo hướng: Các Range chứa Data cho các Combobox con/cháu được đặt tên bằng cách ghép Value từ các Combobox Cha/Ông. Bạn giúp mình mô tả về CSDL mới nhé.
Trân trọng cảm ơn!

1. Nói về CursorLocation: có 2 loại là adUseClient và adUserServer. Nói theo cách đơn giản nhất là nếu dùng "adUseClient - Cursors phía Client" thì giống như bạn lấy cái recordset đó về máy tính của bạn để xử lý (lọc, sort, tiến, lùi...). Còn nếu dùng Cursor phía Server thì bạn chỉ ngồi ở máy bạn ra lệnh thì cái máy tính chứa database (server) sẽ thực hiện mọi thao tác xong trả kết quả về cho máy bạn để hiển thị lên Form, báo cáo v.v..
Dùng adUseClient còn gọi là Disconnected Recordset. Lý do là bạn đã lấy cái Recordset đó về máy tính bạn rồi nên mọi thay đổi dữ liệu sau đó sẽ không được cập nhật trên cái Recordset mà bạn đang xử lý. Đối với dữ liệu khá lớn thì việc tải toàn bô Recordset đó về máy Client sẽ tốn băng thông rất nhiều bên cạnh đó việc di chuyển tới lui trong recordset cũng chậm chút so với xử lý recordset nằm trên máy Server nhưng bù lại thì giảm tải cho phía Server. Đối với dữ liệu ít của bạn thì tôi thấy dùng adUserClient tiện hơn, lấy Recordset về máy rồi xử lý khỏi phụ thuộc máy chủ chứa file database.xlsm

2. Thường thì tôi tạo đối tượng Connection riêng (phạm vi toàn cục) và Recordset riêng. Khi thực thi sẽ tạo một kết nối để đó rồi dùng connection object đó thể lấy các Recordset theo yêu cầu. Nếu không có nhiều người dùng kết nối tới máy chủ chứa database, không tốn băng thông thì cứ giữ kết nối đó, khi đóng Form sẽ đóng kết nối (sự kiện Form_Terminate).

3. Tôi thấy bạn tạo một rừng name range, nhìn choáng luôn. Chắc chắn bạn phải dùng code để tạo các name range này. Lại tốn thêm code cho riêng nó nữa. Tôi đã dựa trên cái file database của bạn và tổ chức lại cách lưu dữ liệu thô, các dữ liệu tạm gọi là có quan hệ với nhau để giúp cho việc code truy xuất dữ liệu gọn gàng, nhanh hơn. Tôi chỉ làm demo một phần để ví dụ cho code, bạn tự làm thêm cho đầy đủ dữ liệu. Và nói thêm là việc xây dựng CSDL phải cần nhiều thông tin trao đổi mới hiểu và xây dựng được nên file này tôi chỉ làm dựa trên suy luận chủ quan của tôi thôi đấy nhé.
Theo cách tổ chức CSDL như demo thì mới tận dụng được ADOrst.Filter để load dữ liệu cho các combobox được nhanh hơn và không cần khai báo nhiều Name range như file cũ của bạn. Vấn đề còn lại của bạn là làm sao nhập liệu theo đúng các bảng dữ liệu đã thiết kế.

 

[zavtra]

1. Nói về CursorLocation: có 2 loại là adUseClient và adUserServer. Nói theo cách đơn giản nhất là nếu dùng "adUseClient - Cursors phía Client" thì giống như bạn lấy cái recordset đó về máy tính của bạn để xử lý (lọc, sort, tiến, lùi...). Còn nếu dùng Cursor phía Server thì bạn chỉ ngồi ở máy bạn ra lệnh thì cái máy tính chứa database (server) sẽ thực hiện mọi thao tác xong trả kết quả về cho máy bạn để hiển thị lên Form, báo cáo v.v..
Dùng adUseClient còn gọi là Disconnected Recordset. Lý do là bạn đã lấy cái Recordset đó về máy tính bạn rồi nên mọi thay đổi dữ liệu sau đó sẽ không được cập nhật trên cái Recordset mà bạn đang xử lý. Đối với dữ liệu khá lớn thì việc tải toàn bô Recordset đó về máy Client sẽ tốn băng thông rất nhiều bên cạnh đó việc di chuyển tới lui trong recordset cũng chậm chút so với xử lý recordset nằm trên máy Server nhưng bù lại thì giảm tải cho phía Server. Đối với dữ liệu ít của bạn thì tôi thấy dùng adUserClient tiện hơn, lấy Recordset về máy rồi xử lý khỏi phụ thuộc máy chủ chứa file database.xlsm

2. Thường thì tôi tạo đối tượng Connection riêng (phạm vi toàn cục) và Recordset riêng. Khi thực thi sẽ tạo một kết nối để đó rồi dùng connection object đó thể lấy các Recordset theo yêu cầu. Nếu không có nhiều người dùng kết nối tới máy chủ chứa database, không tốn băng thông thì cứ giữ kết nối đó, khi đóng Form sẽ đóng kết nối (sự kiện Form_Terminate).

3. Tôi thấy bạn tạo một rừng name range, nhìn choáng luôn. Chắc chắn bạn phải dùng code để tạo các name range này. Lại tốn thêm code cho riêng nó nữa. Tôi đã dựa trên cái file database của bạn và tổ chức lại cách lưu dữ liệu thô, các dữ liệu tạm gọi là có quan hệ với nhau để giúp cho việc code truy xuất dữ liệu gọn gàng, nhanh hơn. Tôi chỉ làm demo một phần để ví dụ cho code, bạn tự làm thêm cho đầy đủ dữ liệu. Và nói thêm là việc xây dựng CSDL phải cần nhiều thông tin trao đổi mới hiểu và xây dựng được nên file này tôi chỉ làm dựa trên suy luận chủ quan của tôi thôi đấy nhé.
Theo cách tổ chức CSDL như demo thì mới tận dụng được ADOrst.Filter để load dữ liệu cho các combobox được nhanh hơn và không cần khai báo nhiều Name range như file cũ của bạn. Vấn đề còn lại của bạn là làm sao nhập liệu theo đúng các bảng dữ liệu đã thiết kế.

Cảm ơn bạn nhiều nhé.