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.

01. Procedury zdarzeń Enter pól kombi opartych na kwerendach.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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.

02. Procedury zdarzeń AfterUpdate pól kombi opartych na kwerendach.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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.

03. Procedury zdarzeń AfterUpdate pól kombi opartych na instrukcjach SQL.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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

 

 
Akceptuję Polityka prywatności

Strona ta wykorzystuje pliki cookies w celu świadczenia usług, dostosowania serwisu do preferencji użytkowników oraz w celach statystycznych i reklamowych. Mechanizm zapisu plików cookies możesz wyłączyć w ustawieniach przeglądarki. Korzystanie z serwisu bez zmiany ustawień przeglądarki oznacza, że pliki cookies będą zapisywane na Państwa urządzeniu końcowym.