Access

  MS Access 2010+  |  Formularze  |   VBA 7.0

• Powiązane hierarchicznie pola kombi.

Często w bazie danych musimy zapisać adres zamieszkania lub siedzibę firmy bądź zakładu. Adres musi zawierać lokalizację podmiotu: województwo, powiat, gminę, miejscowość oraz ulicę (nie zawsze), numer domu i ewentualnie nr mieszkania.
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).

Struktura tabel

Aby ułatwić wprowadzanie danych powinniśmy utworzyć trzy tabele:
• tabelę "Województwa" zawierająca dane o 16 województwach.
• tabelę "Powiaty" zawierająca dane o 380 powiatach.
• tabelę "Gminy" zawierająca dane o 3771 jednostkach podziału terytorialnego.
   Nie jest to liczba gmin w Polsce, ale ilość pozycji w pliku TERC.xml,
   który obejmuje poniższe jednostki podziału terytorialnego:
  • 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

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")));

Samych gmin mamy obecnie 2478 (stan na 03.03.2018 r.), więc umieszczenie danych w jednej tabeli i wymuszenie na użytkowniku by wybrał określoną Gminę z listy pola kombi zawierającej ok 2500 pozycji stawia go przed trudnym zadaniem.


Relacje

Relacje pomiędzy tabelami

Aby umożliwić użytkownikowi szybkie i poprawne wybranie Gminy powinniśmy utworzyć trzy dynamiczne, hierarchiczne, powiązane pola kombi, tak by wybór pozycji w pierwszym polu kombi cboWojewództwa, ograniczał możliwości wyboru w drugim polu kombi cboPowiaty tylko do powiatów należących do wybranego Województwa. Po wyborze Powiatu w drugim polu kombi, lista pozycji w trzecim polu kombi cboGminy powinna zostać ograniczona do Gmin należących do wybranego wcześniej Powiatu.

Trzy kaskadowe pola kombi oparte na kwerendach.

Utwórzmy formularz frmCombo_Kwerendy na którym umieścimy trzy niezwiązane pola kombi o nazwach cboWojewodztwo, cboPowiat, cboGminy oraz trzy opisowe etykiety lblWojewodztwo, lblPowiat, lblGminy, które będą zawierały dane dotyczące aktualnie wybranego wiersza z odpowiadających im pól kombi.

Pole kombi cboWojewodztwo.

Dwukolumnowe pole kombi którego właściwość RowSource (ŹródłoWierszy) ustawiona jest na kwerendą źródłową qryWojewodztwa.
• Pierwsza ukryta kolumna jest kolumną związaną. Zawiera identyfikator województwa ID_Woj
• Druga widoczna kolumna zawiera nazwę województwa tWojewodztwo
cboWojewództwa - arkusz właściwości    Projekt kwerendy qryWojewodztwa
Arkusz właściwości pola kombi cboWojewodztwa i projekt źródłowej kwerendy qryWojewodztwa

Pole kombi cboPowiaty.

Trzykolumnowe pole kombi którego właściwość RowSource (ŹródłoWierszy) ustawiona jest na kwerendą źródłową qryPowiaty.
• Pierwsza ukryta kolumna jest kolumną związaną. Zawiera identyfikator powiatu ID_Pow
• Druga widoczna kolumna zawiera nazwę Powiatu tPowiat
• Trzecia widoczna kolumna zawiera dodatkową nazwę opisową powiatu tNazwa_Dod

Kwerenda qryPowiaty pobiera dane do kryterium wyboru wierszy z kolumny związanej ID_Woj pola kombi cboWojewodztwo przechowującej identyfikator województwa.

WHERE Powiaty.Id_Woj=[Forms]![frmCombo_Kwerendy]![cboWojewodztwo]
cboPowiaty - arkusz właściwości
Arkusz właściwości pola kombi cboPowiaty
Projekt kwerendy qryPowiaty
Projekt źródłowej kwerendy qryPowiaty

Pole kombi cboGminy.

Trzykolumnowe pole kombi oparte o kwerendą źródłową qryPowiaty.
• Pierwsza ukryta kolumna jest kolumną związaną. Zawiera identyfikator powiatu ID_Pow
• Druga widoczna kolumna zawiera nazwę Powiatu tPowiat
• Trzecia widoczna kolumna zawiera dodatkową nazwę opisową powiatu tNazwa_Dod

Trzykolumnowe pole kombi którego właściwość RowSource (ŹródłoWierszy) ustawiona jest na kwerendą źródłową qryGminy, z ukrytą pierwszą kolumną. Kwerenda qryGminy pobiera dane do kryterium wyboru wierszy:

WHERE Gminy.Id_Pow=[Forms]![frmCombo_Kwerendy]![cboPowiat]

z ukrytej kolumny związanej pola kombi cboPowiaty przechowującej identyfikator powiatu ID_Pow.

cboGminy - arkusz właściwości
Arkusz właściwości pola kombi cboGminy
Projekt kwerendy qryGminy
Projekt źródłowej kwerendy qryGminy

Procedury zdarzeń pól kombi.

Private Sub PoleKombi_Enter()

Zdarzenie Enter występuje zanim formant faktycznie otrzymuje fokus od innego formantu (przed zdarzeniem GotFocus) na tym samym formularzu. Zdarzenie Enter występuje tylko dla formantów na formularzu, a nie dla formantów raportu.

Widok formularza
Widok formularza frmCombo_Kwerendy

Ponieważ pola kombi są ze sobą powiązane tzn. źródło listy pola kombi zależy od wartości poprzedniego w hierarchii pola kombi, powinniśmy uniemożliwić edycję pola kombi, które ma otrzymać fokus, jeżeli wcześniejsze pole jest puste. Przykładowo: jeżeli pole kombi cboWojewództwo jest niewypełnione, edycja pól kombi cboPowiatycboGminy nie powinna być możliwa. Przy próbie wejścia (edycji) jednego z tych pól kombi fokus powinien zostać przeniesiony na puste pole kombi. W tym celu skorzystamy ze zdarzenia pól kombi Enter i właściwości OnEnter (PrzyWejściu) której przypiszemy wartość [Event Procedure] ([Procedura zdarzenia]), a w edytorze VBA wprowadzimy odpowiedni kod źródłowy procedury przekierowujący fokus do pustego pola kombi.

Private Sub cboPowiat_Enter()
	' jeżeli pole kombi cboWojewództwo jest puste
	' ustaw na nim fokus i rozwiń listę
	With Me.cboWojewodztwo
		If Len(Nz(.Value, "")) = 0 Then
			.SetFocus
			.Dropdown
		End If
	End With
End Sub

Private Sub cboGmina_Enter()
	' jeżeli pole kombi cboWojewództwo jest puste
	' ustaw na nim fokus, rozwiń listę i wyjdź
	With Me.cboWojewodztwo
		If Len(Nz(.Value, "")) = 0 Then
			.SetFocus
			.Dropdown
			Exit Sub
		End If
	End With

	' jeżeli pole kombi cboPowiat jest puste
	' ustaw na nim fokus i rozwiń listę
	With Me.cboPowiat
		If Len(Nz(.Value, "")) = 0 Then
			.SetFocus
			.Dropdown
		End If
	End With
End Sub

Private Sub PoleKombi_AfterUpdate()

Zdarzenie AfterUpdate występuje po zmianie danych w formancie lub po aktualizacji rekordu. Aby po aktualizacji pierwszego pola kombi cboWojewództwa, ograniczyć możliwości wyboru w drugim polu kombi cboPowiaty tylko do powiatów należących do wybranego Województwa, a po aktualizacji drugiego pola kombi cboPowiaty, ograniczyć listę pozycji w trzecim polu kombi cboGminy do Gmin należących do wybranego Powiatu, skorzystamy ze zdarzenia pól kombi AfterUpdate i właściwości AfterUpdate (PoAktualizacji). Właściwości tej przypiszemy wartość [Event Procedure] ([Procedura zdarzenia]), a w edytorze VBA wprowadzimy odpowiedni kod źródłowy procedury.

Ponieważ kwerenda qryPowiaty będąca Źródłem Wierszy (RowSource) pola kombi cboPowiaty pobiera dane do kryterium wyboru wierszy z ukrytej kolumny związanej ID_Woj pola kombi cboWojewodztwo przechowującej identyfikator województwa, a kwerenda qryGminy będąca Źródłem Wierszy (RowSource) pola kombi cboGminy pobiera dane do kryterium wyboru wierszy z ukrytej kolumny związanej pola kombi cboPowiaty przechowującej identyfikator powiatu ID_Pow, to w procedurach Private Sub PoleKombi_AfterUpdate() wystarczy wykorzystać metodę Requery aktualizującą dane określonego pola kombi, przez ponowienie kwerendy źródła danych dla pola kombi. W procedurach tych wykorzystamy dane zawarte w kolumnach poszczególnych pól kombi by zaktualizować opisowe etykiety lblWojewodztwo, lblPowiat, lblGminy, o dane dotyczące aktualnie wybranego wiersza z odpowiadających im pól kombi.

Private Sub cboWojewodztwo_AfterUpdate()
	' aktualizuj etykietę Województwo
	With Me.cboWojewodztwo
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblWojewodztwo.Caption = "Województwo"
		Else
			' aktualizuj etykietę Województwo
			Me.lblWojewodztwo.Caption = "(" & .Column(0) & ") " & .Column(1)
			' odśwież źródło wierszy pola kombi cboPowiat
			Me.cboPowiat.Requery
		End If
	End With

	' ustaw domyślne wartości etykiet
	Me.lblPowiat.Caption = "Powiat"
	Me.lblGmina.Caption = "Gmina"

	' wyczyść pola kombi cboPowiat i cboGmina
	Me.cboPowiat = ""
	Me.cboGmina = ""
End Sub

Private Sub cboPowiat_AfterUpdate()
	' aktualizuj etykietę Powiat
	With Me.cboPowiat
		' aktualizuj etykietę Powiat
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblPowiat.Caption = "Powiat"
		Else
			Me.lblPowiat.Caption = "(" & .Column(0) & ") " & _
														 .Column(1) & " (" & .Column(2) & ")"
			' odśwież źródło wierszy pola kombi cboGmina
			Me.cboGmina.Requery
		End If
	End With

	' ustaw domyślną wartość etykiety Gmina
	Me.lblGmina.Caption = "Gmina"
	' wyczyść pole kombi cboGmina
	Me.cboGmina = ""
	End Sub

	Private Sub cboGmina_AfterUpdate()
	' aktualizuj etykietę Gmina
	With Me.cboGmina
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblGmina.Caption = "Gmina"
		Else
			Me.lblGmina.Caption = "(" & .Column(0) & ") " & _
														.Column(1) & " (" & .Column(2) & ")"
		End If
	End With
End Sub
 
Formularz - kwerendy
Hierarchiczne pola kombi oparte na kwerendach

Trzy kaskadowe pola kombi oparte na instrukcjach SQL.

Jeżeli nie chcemy tworzyć kwerend i odwoływać się w tych kwerendach do pól kombi w formularzu roboczym, możemy stworzyć dynamiczne, 3 poziomowe, rozwijane powiązane listy kombi, których źródłem wierszy będą instrukcje SQL z dostosowanym warunkiem WHERE do wybranej wartości w polu kombi będącego w hierarchii o jeden stopień wyżej. Poniżej przykład przedstawiający zmianę właściwość RowSource (ŹródłoWierszy) pola kombi cboPowiat po aktualizacji pola kombi cboWojewództwa.

Me.cboPowiat.RowSource = & _
"SELECT ID_Pow, tPowiat, tNazwa_Dod FROM Powiaty" & _
" WHERE [Id_Woj] = " & "'" & Me.cboWojewodztwo.Column(0) & "'" & _
" ORDER BY ID_Pow;"

Procedury zdarzeń pól kombi.

Private Sub PoleKombi_Enter()

Procedury zdarzeń OnEnter pozostają takie same jak w opisywanym powyżej przykładzie Trzy kaskadowe pola kombi oparte na kwerendach

Private Sub PoleKombi_AfterUpdate()

Poniżej przedstawiam zmienione procedury zdarzeń AfterUpdate pól kombi opartych na instrukcjach SQL. Źródłem wierszy poszczególnych pól kombi są instrukcje SQL z dostosowanym warunkiem WHERE do wybranej wartości w polu kombi będącego w hierarchii o jeden stopień wyżej.

Private Sub cboWojewodztwo_AfterUpdate()
Dim sRowSource As String

	' po aktualizacji ustaw źródło pola kombi cboPowiat
	With Me.cboWojewodztwo
		sRowSource = "SELECT ID_Pow, tPowiat, tNazwa_Dod FROM Powiaty" & _
								 " WHERE [Id_Woj] = " & "'" & .Column(0) & "'" & _
								 " ORDER BY ID_Pow;"
		'
		Me.cboPowiat.RowSource = sRowSource
		' aktualizuj etykietę Województwo
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblWojewodztwo.Caption = "Województwo"
		Else
			Me.lblWojewodztwo.Caption = "(" & .Column(0) & ") " & .Column(1)
		End If
	End With

	' ustaw domyślne wartości etykiet
	Me.lblPowiat.Caption = "Powiat"
	Me.lblGmina.Caption = "Gmina"

	' wyczyść pola kombi cboPowiaty i cboGmina
	Me.cboPowiat = ""
	Me.cboGmina = ""
End Sub

Private Sub cboPowiat_AfterUpdate()
Dim sRowSource As String

	' po aktualizacji ustaw źródło pola kombi cboGmina
	With Me.cboPowiat
		sRowSource = "SELECT ID_Gmi, tNazwa, tNazwa_Dod FROM Gminy" & _
								" WHERE ([Id_Pow] = " & "'" & .Column(0) & "')" & _
								" AND (CLng(Right([ID_Gmi],1)) < CLng('4'))" & _
								" ORDER BY Gminy.tNazwa;"
		Me.cboGmina.RowSource = sRowSource

		' aktualizuj etykietę Powiat
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblPowiat.Caption = "Powiat"
		Else
			Me.lblPowiat.Caption = "(" & .Column(0) & ") " & _
														 .Column(1) & " (" & .Column(2) & ")"
		End If
	End With

	' ustaw domyślną wartości etykiety Gmina
	Me.lblGmina.Caption = "Gmina"
	' wyczyść pole kombi cboGmina
	Me.cboGmina = ""
	End Sub

	Private Sub cboGmina_AfterUpdate()
	' aktualizuj etykietę Gmina
	With Me.cboGmina
		If Len(Nz(.Value, "")) = 0 Then
			Me.lblGmina.Caption = "Gmina"
		Else
			Me.lblGmina.Caption = "(" & .Column(0) & ") " & _
														.Column(1) & " (" & .Column(2) & ")"
		End If
	End With
End Sub

Formularz SQL
Hierarchiczne pola kombi oparte na instrukcjach SQL



Pobierz Do pobrania:Hierarchicznie (kaskadowe) pola kombi pobrano ()

Pięciopoziomowe pole kombi w widoku TreeView

Bardziej rozbudowany przykład pole kombi w widoku TreeView (z pięciom poziomami zagłębienia)

ComboBox TERYT TreeView
Rozbudowane pole kombi w widoku TrreView

znajduje się na stronie www.gps.accdb.pl w przykładowej bazie Zapis danych Rejestru TERYT do tabel MS Access