Kombi TreeView. Access i formularze

Kontrolka TreeView

Problem hierarchicznego prezentowania danych możemy w „prosty sposób” ☺ rozwiązać wykorzystując kontrolkę TreeView. Pozwala ona prezentować dane w postaci hierarchicznej tak jak lista folderów w Eksploratorze Windows. Można definiować nowe węzły, dodawać wiele poziomów zagnieżdżenia itp. Każdy węzeł w widoku drzewa może zawierać inne węzły podrzędnych. Można dodawać lub usuwać węzły, wyświetlić węzły nadrzędne lub węzły, które zawierają węzeł podrzędny jako rozwiniętą bądź zwinięta listę i wiele innych ułatwień.

Kontrolka TreeView - jak dodać formant ActiveX do formularza?

Otwieramy formularz w widoku projekt. Na zakładce „Projektowanie„ na karcie „Formanty”

Relacje pomiędzy tabelami

klikamy przycisk Formant OCX , aby dodać nowy formant ActiveX do formularza. Pojawi się okno dialogowe Wstawianie formantu „ActiveX”. Z listy wybieramy interesujący nas formant „ActiveX” o nazwie „Microsoft TreeView Control version 6.0”

Okno wyboru formantów OCX

Po zatwierdzeniu przyciskiem OK

Formant TreeView po dodaniu do formularza

w formularzu pojawia się formant TreeView. Na obrazku widoczny jest dodany formant TreeView w jego domyślnym rozmiarze.

Odwołanie (References) do formantu TreeView

MS Access 2007+ po dodaniu formantu TreeView do formularza automatycznie dodaje odwołanie (referencje) do kontrolki ActiveX Microsoft Windows Common Controls 6.0. Jest to plik MSCOMCTL.OCX uważany za typ pliku COM (Component Object Model), najczęściej określany jako kontrolka ActiveX.

Lokalizacja pliku MSCOMCTL.OCX w zależności od wersji MS Access

Właściwości plików MSCOMCTL.OCX

W 64-bitowym Windows MS Access odwołuje się do pliku MSCOMCTL.OCX w katalogu:: • 32-bitowy MS Access 2007 w katalogu C:\Windows\SysWOW64; • 64 bitowy MS Access 2016 w katalogu C:\Windows\system32; • 64 bitowy MS Access 2010 odmawia współpracy z formantem TreeView zgłaszając komunikaty o błędach

Okna komunikatów o błędzie w 64 bitowym MS Access 2010

Urwane referencje (Missing Reference)

Wystarczy wrzucić w Google hasło „MS Access Missing Reference„ by podjąć decyzję „temu panu dziękujemy” ☺ tj. kontrolce MSCOMCTL.OCX. Rozwiązania są banalnie proste, otworzyć w widoku projekt okno edytora VBA i usunąć urwane Referencje. Bardzo proste, zwłaszcza w pliku *.accde (*.mde).

Formant kombi w widoku drzewka (TreeView)

Projekt „TreeComboBox” opierał się będzie na trzech tabelach:

• tabela "Województwa" zawierająca dane o 16 województwach.

• tabela "Powiaty" zawierająca dane o 380 powiatach.

• tabela "Gminy" zawierająca dane o 3771 jednostkach podziału terytorialnego.

Uwaga! Nie jest to liczba gmin w Polsce, ale ilość pozycji w pliku TERC.xml,
Gmin mamy obecnie 2478 (stan na 03.03.2018 r.)

1 - gmina miejska,
2 - gmina wiejska,
3 - gmina miejsko-wiejska,
4 - miasto w gminie miejsko-wiejskiej,
5 - obszar wiejski w gminie miejsko-wiejskiej,
8 - dzielnica w m.st. Warszawa,
9 - delegatury miast: Kraków, Łódź, Poznań i Wrocław

Relacje pomiędzy tabelami

Ograniczając się tylko do Gmin (trzy pierwsze pozycje listy) mamy trzy stopnie zagłębienia (województwa/powiaty/gminy). W takim układzie mamy 16 węzłów określających województwa i dwa stopnie zagłębienia dla każdego węzła (powiaty/gminy).

Aby pobrać z tabeli "Gminy" tylko nazwy gmin należy użyć instrukcji SQL:

SELECT Gminy.ID_Gmi, Gminy.Id_Pow, Gminy.tNazwa, Gminy.tNazwa_Dod
FROM Gminy
WHERE ((CLng(Right([ID_Gmi],1))<CLng("4")));

W tym przykładzie nie będę się ograniczał tylko do gmin, ale postaram się uwzględnić wszystkie pozycje z pliku TERC.xml zawierającym dane o 3771 jednostkach podziału terytorialnego.

Dane dotyczące miejscowości i ich położenie terytorialne (administracyjne) możemy pobrać z Krajowego Rejestru Urzędowego Podziału Terytorialnego Kraju (TERYT) ze strony Głównego Urzędu Statystycznego (GUS).

Więcej szczegółów o zapisie danych z bazy Teryt do tabel MS Access znajdziesz na stronie Krajowy Rejestr Urzędowy Podziału Terytorialnego Kraju (TERYT) i podstronach omawiających poszczególne zbiory bazy Teryt (WMRODZ, TERC, SIMC i ULIC).

Wcięcia w poszczególnych wierszach listy

Wcięć w poszczególnych wierszach listy nie zrobimy za pomocą znaku spacji Chr$(32), gdyż MS Access uznaje spacje wiodące za znaki nadmiarowe i je usuwa. Jako spację wiodącą należy użyć tzw. niełamliwej spacji Chr$(160).
Całą symbolikę elementów listy musimy oprzeć na poniższych znakach:

… † ‡ ‹ • › ˇ ˘ ¤ – — ¦ ¨ « ¬ ° ± » ×

Ja wybrałem poniższy zestaw znaków:

| | m_sIndent - pojedyncze wcięcie wiersza listy: Chr$(160) & Chr$(160) & Chr$(160) & Chr$(160)
|• | m_sPlus - węzeł po kliknięciu zostanie rozwinięty: Chr$(149) & Chr(26) & Chr$(160)
|• | m_sMinus - węzeł po kliknięciu zostanie zwinięty: Chr(17) & Chr$(149) & Chr$(160)
|× | m_sNoData - wiersz nie zawiera danych, nie może być rozwinięty: Chr$(215) & Chr$(160) & Chr$(160)
| | m_sStreet - wiersz zawiera wykaz ulic, nie może być rozwinięty: Chr$(160) & Chr$(11) & Chr$(160)
Zostanie wyświetlony formant ListBox z nazwami ulic w wybranej miejscowości.

Jak to działa?

Nie da się słowami opisać jak działa kod, który ma ok. 300 linii. Można gadać, gadać gadać, a i tak nic z tego nie będzie. Praktycznie wszystko polega na dynamicznym dodawaniu i usuwaniu pozycji listy w formancie cboTree. Jak to działa popatrz na kod źródłowy.

Ustawienie startowe i wypełnienie listy formantu kombi cboTree zawierającego 3 (trzy) kolumny:

sRowItem = !Id_Woj & ";" & m_sPlus & !tWojewodztwo & ";" & "0"

kolumna ukryta; identyfikator = Id_Woj
kolumna widoczna: nazwa województwa z prefiksem = m_sPlus & !tWojewodztwo
kolumna ukryta; stopień zagłębienia = 0

' zmienne na poziomie modułu
Private m_sPlus   As String
Private m_sMinus  As String
Private m_sIndent As String
Private m_sNoData As String

Private Sub Form_Load()
	Call funSetDefValue
End Sub

Private Sub funSetDefValue()
Dim dbs       As DAO.Database
Dim rst       As DAO.Recordset
Dim sRowItem  As String
Dim lIndex    As Long

	' pojedyncze wcięcie wiersza listy
	m_sIndent = Chr$(160) & Chr$(160) & Chr$(160) & Chr$(160)
	' • po kliknięciu wiersz może być rozwinięty
	m_sPlus = Chr$(149) & Chr(26) & Chr$(160)
	' • wiersz może być zwinięty
	m_sMinus = Chr(17) & Chr$(149) & Chr$(160)
	' × wiersz nie zawiera danych, nie może być rozwinięty
	m_sNoData = Chr$(215) & Chr$(160) & Chr$(160)

	With Me.cboTree
		' lista zawiera 3 kolumny
		.ColumnCount = 3
		.RowSourceType = "Value List"
		' zeruj źródło wierszy
		.RowSource = ""
		.Value = ""
	End With

	Me.txtTreeValue = ""

	' zapełnij źródło wierszy listy
	Set dbs = CurrentDb
	Set rst = dbs.OpenRecordset("Wojewodztwa", dbOpenDynaset)
		With rst
			Do Until rst.EOF
				sRowItem = !Id_Woj & ";" & m_sPlus & !tWojewodztwo & ";" & "0"
				' dodaj wiersz do listy i zwiększ indeks
				Me.cboTree.AddItem sRowItem, lIndex
				lIndex = lIndex + 1
				rst.MoveNext
			Loop
		End With
		
		rst.Close
	Set rst = Nothing
	Set dbs = Nothing

End Sub

Próbowałem, ale nie da się wyjaśnić wszystkich niuansów kodu.

Przykład ten jest „fragmentem większej całości” ☺. Dla węzłów ostatniego poziomu pojawia się prefiks • informujący, że jest możliwe dalsze rozwinięcie węzła. Jest to zgodne z prawdą, ale przykład ten nie zawiera dalszych zagłębień poniżej elementów zawartych w tabeli "Gminy". W węzłach (ale nie w tym przykładzie) poniżej węzła Gminy zawarte są dane w tabeli "Miejscowosci" zawierająca 102 940 rekordów oraz dane w tabeli "Ulice" zawierające 267 028 rekordów.
W prosty sposób można to zmienić, zamieniając w funkcji funExpandGminy(...) dwa ostatnie wystąpienia zmiennej m_sPlus na zmienną m_sNoData.

Formant ComboBox w widoku TreeView

Formant ListBox i ComboBox w uproszczonym widoku TreeView

Rozwiązanie kombi TreView przedstawione powyżej, możemy uprościć, tak by rozwijanie węzłów zakończyć na węźle Gminy, tworząc przykładowe rozwiązanie bardziej czytelnym (prostszym). Rozwiązanie pierwsze, te bardziej skomplikowane posłuży jako pierwowzór dla bardziej skomplikowanego przykładu, pozwalającego przedstawić wszystkie 102 940 miejscowości w widoku drzewa (TreeView).

Formant ListBox w uproszczonym widoku TreeView

W łatwy sposób możemy zaadaptować kod dla formantu ListBox, tak by otrzymać widok TreeView. Niewiele więcej można tutaj pisać o implementacji kodu. Generalnie trzeba zamienić formant ComboBox na ListBox, z kodu usunąć instrukcje odnoszące się tylko do właściwości charakterystycznych dla formantu ComboBox np. Me.cboTree.DropDown, zmienić deklaracje As Access.ComboBox na As Access.ListBox oraz nie korzystać ze zdarzenia Timer. I to by było na tyle ☺

Formant ListBox i ComboBox w widoku TreeView

Wady projektu kombi TreView

Pierwszą podstawową wadą jest zwijanie się listy pola kombi po każdej aktualizacji tego formantu. By wybrać następną pozycję na liście, za każdym razem trzeba najechać myszką na strzałkę pola kombi by rozwinąć listę i wybrać nową pozycję (węzeł) listy pola kombi. Proste rozwiązanie typu:
Me.cboTree.Dropdown niestety nie działa. ALe można ten problem w prosty sposób obejść za pomocą „Timera” formularza:

Private Sub cboTree_AfterUpdate()
	'uruchom Timer, by rozwinąć listę
	Me.TimerInterval = 100
	
Private Sub Form_Timer()
	' zatrzymaj Timer
	Me.TimerInterval = 0
	'rozwiń listę
	Me.cboTree.Dropdown
End Sub

Rozwiązanie takie nie posiada jedną dużą wadę. Po aktualizacji pola kombi cboTree właściwość RowSource zostaje dynamicznie zmieniona, gdyż zachodzi rozwinięcie, lub zwinięcie wybranego węzła. Ilość pozycji na liście cboTree ulega zmianie. Ponieważ kursor myszy pozostaje nad obszarem rozwiniętej listy, po dynamicznym uaktualnieniu listy, pod kursorem myszy znajdować się będzie przypadkowa pozycja listy i ona zostanie podświetlona, wprowadzając użytkownika w błąd co do wyboru pozycji na liście.

Rozwiązanie tego problemu może być przesunięcie kursora myszy w poziomie poza prawą krawędź rozwiniętej listy pola kombi cboTree tak by nie nastąpiło podświetlenie przypadkowej wartości na rozwiniętej liście. Moim zdaniem takie rozwiązanie wizualnie nie będzie za bardzo irytującej dla użytkownika.
Kilka prostych funkcji API i problem rozwiązany ☺.

Funkcji GetFocus(...) - pobieramy uchwyt aktywnego okna (fokus ma formant cboTree).
Funkcji GetWindowRect(...) - pobieramy położenie i rozmiar aktywnego okna.
Funkcja ClientToScreen - przelicza uzyskane współrzędna okna na współrzędne ekranowe.
Funkcja GetCursorPos(...) - zwraca aktualne położenie kursora myszy,
Funkcja SetCursorPos(...) - przesuwa kursor myszy poza prawą krawędź rozwiniętej listy.

#If VBA7 Then
  Private Declare PtrSafe Function GetFocus Lib "user32" () As LongPtr
  Private Declare PtrSafe Function GetWindowRect Lib "user32" _
          (ByVal hwnd As LongPtr, lpRect As RECT) As Long
  Private Declare PtrSafe Function ClientToScreen Lib "user32" _
          (ByVal hwnd As LongPtr, lpPoint As POINTAPI) As Long
  Private Declare PtrSafe Function GetCursorPos Lib "user32" _
          (lpPoint As POINTAPI) As Long
  Private Declare PtrSafe Function SetCursorPos Lib "user32" _
          (ByVal x As Long, ByVal y As Long) As Long
#Else
  Private Declare Function GetFocus Lib "user32" () As Long
  Private Declare Function GetWindowRect Lib "user32" _
          (ByVal hwnd As Long, lpRect As RECT) As Long
  Private Declare Function ClientToScreen Lib "user32" _
          (ByVal hwnd As Long, lpPoint As POINTAPI) As Long
  Private Declare Function GetCursorPos Lib "user32" _
          (lpPoint As POINTAPI) As Long
  Private Declare Function SetCursorPos Lib "user32" _
          (ByVal x As Long, ByVal y As Long) As Long
#End If

Private Sub Form_Timer()
Dim rct         As RECT
Dim paCboTree   As POINTAPI
Dim paCursor    As POINTAPI
Dim lRet        As Long

#If VBA7 Then
  Dim hWind As LongPtr
#Else
  Dim hWind As Long
#End If

	' wyłącz timer
	Me.TimerInterval = 0
	' pobierz uchwyt aktywnego okna (cboTree)
	hWind = GetFocus
	' pobierz położenie i wymiar okna cboTree
	lRet = GetWindowRect(hWind, rct)
	' przelicz na położenie w/m ekranu
	lRet = ClientToScreen(hWind, paCboTree)
	' pobierz położenie kursora
	lRet = GetCursorPos(paCursor)
	' przesuń kursor w poziomie o 30 pikseli w prawo poza rozwiniętą listę
	lRet = SetCursorPos(paCboTree.x + (rct.Right - rct.Left) + 30, paCursor.y)
	'rozwiń listę cboTree
	Me.cboTree.Dropdown
 
End Sub

Rozwiązanie z przesuwaniem kursora myszy w poziomie poza prawą krawędź rozwiniętej listy pola kombi, jest dość „siermiężne”, ale spełnia swoje zadanie. Trochę nienaturalne dla użytkownika może być taka zmiana położenia kursora myszy, ale bardziej irytujące jest podświetlenie nieaktualnej pozycji, po zapełnieniu listy nowymi wierszami.
W najbliższym, nieprzewidywalnym czasie, przedstawię trochę bardziej eleganckie rozwiązanie i nie omieszkam oczywiście dać znać o tym na tej stronie ☺