Warsztat: Liczba porządkowa (Lp) dla rekordu.
LiczPorz.zip (realizacja Lp na formularzu)
Autor: Krzysztof Pozorek
Baza w formacie MsAccess 97
33kB, 25-08-2000
Royally Free

RecNumberA2k.zip (realizacja Lp na formularzu, uzupełnienie)
Autor: Robert Krawiec
Baza w formacie MsAccess 2000
39kB, 02-05-2003

BudujAutoNr.zip (odbudowa ciągłej numeracji w tabeli)
Autor: Krzysztof Pozorek
Baza w formacie MsAccess 97
33kB, 24-08-2000
Royally Free

Opis problemu:

Idea liczby porządkowej Lp jest sprzeczna z zasadami relacyjności baz danych (właśnie ta relacyjność powoduje, ze żaden rekord nie jest trzeci, czy piąty, tylko równoprawny), dlatego trudno o jakieś naprawdę dobre rozwiązanie w tym zakresie. Niemniej jednak i na to znalazły się sposoby. Możliwości numerowania rekordów w zasadniczy sposób zależą od tego, w jakim obiekcie accessowym chcemy umieścić to nasze Lp.

Raporty

W raportach uzyskanie liczby porządkowej jest najprostsze. Wystarczy utworzyć pole tekstowe z wartością =1 i ustawić tam sumę bieżącą.

Formularze

W formularzach nie ma możliwości ustawienia sumy bieżącej. Tutaj uzyskanie Lp jest znacznie bardziej skomplikowane, ponieważ w przeciwieństwie do raportu, na formularzu może się zmieniać porządek sortowania, można kasować i dopisywać rekordy - a nasze Lp musi się automatycznie przenumerować. Aby uzyskać efekt Lp, należy napisać swoją funkcje, którą wstawiamy do pola tekstowego jako źródło formantu. Znam dwie zasady budowania takich funkcji:
a) metoda zliczania rekordów na formularzu,
b) metoda wykorzystujące właściwości recordsetu AbsolutePosition lub PercentPosition.

Która z tych metod jest lepsza - nie wiem. Zależy to od konkretnych zastosowań. Sposób a) wymaga istnienia klucza unikalnego, trudno go też polecać dla zestawów rekordów rzędu tysięcy, bo działa dość wolno, z kolei metoda b) gorzej się zachowuje w momencie dodawania nowego rekordu, bo wtedy jeszcze nie istnieje odpowiedni wiersz w recordsecie, do którego można się odwołać (próbą rozwiązania tego problemu jest program RecNumberA2k Roberta Krawca). 

Poniżej podam przykłady obu rozwiązań.

'******************************
'Przykład metody a)
'Działa świetnie, ale wymaga unikalnego klucza ID.
'******************************
Public Function Lpz(F As Form, ID)
On Error GoTo Koniec
Dim Rs As Recordset, ZliczID As Long
    Set Rs = F.RecordsetClone
    Rs.MoveFirst
    ZliczID = 1
    Do Until (id = Rs!id) Or Rs.EOF
        ZliczID = ZliczID + 1
        Rs.MoveNext
    Loop   
Koniec:
    If Err Then
        If Not IsNull(id) Then
            Lpz = ZliczID
        End If
    Else
        Lpz = ZliczID
    End If
End Function

'********************
'Przykład metody b)
'Działa szybciej niż metoda a), ale gorzej
'zachowuje się przy dodawaniu rekordów.
'********************
Public Function Lp(F As Form)
On Error Resume Next
Dim Rs As Recordset
    Set Rs = F.RecordsetClone
    Rs.Bookmark = F.Bookmark
    If Err = 0 Then
        If Rs.AbsolutePosition < Rs.RecordCount Then
            If (Rs.AbsolutePosition = Rs.RecordCount + F.Dirty) Then
                Lp = F.CurrentRecord
            Else
                Lp = Rs.AbsolutePosition + 1
            End If
        End If
    End If
End Function

Oto przykłady wywołania tych funkcji:

'W polu tekstowym na formularzu jako źródło formantu piszemy
=Lpz([Form];[ID])
'lub
=Lp([Form])

Rozpakuj LiczPorz.zip, aby zobaczyć działanie tych funkcji w praktyce. Obie funkcje zostały wykorzystane do uzyskania Lp na tym samym formularzu ciągłym, co ułatwia porównywanie obu metod.

Kwerendy

W kwerendach są bardzo niewielkie możliwości wyświetlenia liczby porządkowej. Jedyny sposób polega na zastosowaniu podkwerendy (lub funkcji) z określonym warunkiem, która zlicza np. rekordy z ID mniejszym od ID w rekordzie bieżącym. A oto przykład takiej kwerendy z polem Lp:

'Załóżmy, że jest tabela Imieniny i chcemy 
'utworzyć Lp o numeracji zgodnej z polem Data
SELECT *, (SELECT Count(*) 
	FROM Imieniny As T1 
	WHERE T1.Data<=Imieniny.Data) AS LP
	FROM Imieniny;

Niestety, rozwiązanie to ma jedną zasadniczą wadę - nie można zmieniać porządku sortowania, ani zakładać filtrów, bo cała numeracja się psuje. Jednak w wielu przypadkach uzyskany efekt zupełnie wystarcza.
Oddzielna sprawa, to zagadnienie szybkości działania. Funkcja Count(*) jest dobrze optymalizowana za pomocą zastosowanej w Accessie technologii Rushmore. Jednak mimo to nie jest to konstrukcja szybka dla dużych zestawów rekordów.

Tabele

W tabelach nie ma możliwości wyświetlenia liczby porządkowej. Pewną namiastką Lp jest oczywiście pole typu Autonumer, ale prawie zawsze zawiera ono dziury w numeracji. Dziury w Autonumerze powstają zwykle z dwóch powodów:
- skasowanie rekordu,
- rezygnacja z zapisu nowego rekordu po rozpoczęciu edycji.
Jeśli liczy się tylko ciągłość numeracji, a nie powiązania referencyjne - można zastosować funkcję OdbudujAutoNr() przedstawioną dalej, ale zwykle raz nadanego numeru w tabeli nie wolno(!) zmieniać i wtedy nie można go użyć w charakterze Lp.

Public Function OdbudujAutoNr()
On Error Resume Next
Dim fld As Field, idx As Index
Dim dbs As Database, tbldf As TableDef
    Set dbs = CurrentDb
    Set tbldf = dbs.TableDefs!Imien
    tbldf.Indexes.Delete "Klucz główny"     'usuwa stary indeks na polu ID
    tbldf.Fields.Delete "ID"                'usuwa stare pole ID
    tbldf.Fields.Refresh
    Set fld = tbldf.CreateField("ID", dbLong)   'tworzy nowe pole ID
    fld.Attributes = fld.Attributes + dbAutoIncrField   'ustawia Autonumer
    tbldf.Fields.Append fld
    Set idx = tbldf.CreateIndex("Klucz główny") 'tworzy nowy indeks,
    idx.Primary = True                          'klucz unikalny
    idx.Fields.Append idx.CreateField("ID")
    tbldf.Indexes.Append idx
    OdbudujAutoNr=Err
End Function

Przykład BudujAutoNr.zip pokazuje sposób użycia tej funkcji do odbudowania ciągłej numeracji w polu typu Autonumer. (Dodatkowo znajdziesz tam roczny wykaz imion, który przed kilku laty w porywie zaparcia wklepałem do komputera. Mam wrażenie, że wyświetlanie imienin w rogu aplikacji zawsze jakoś sympatycznie wpływa na odbiorcę naszego oprogramowania i czasem warto zrobić coś takiego)