Krzysztof Naworyta - Formuły w Access'ie

Kraków, 5/1/2003

Formuły w Access'ie ?

Niieeeee ... Tutaj mamy kwerendy, pola wyliczeniowe ...

Na dobrą sprawę trudno sobie wyobrazić sens stosowania jakichś formuł, różnych z rekordu na rekord.
To nie Excel ! Logika tabel i relacji zmusza do jednorodności danych w pojedynczej kolumnie.

Niemniej czasami być może chcielibyśmy użytkownikowi pozwolić na wpisanie "z palca" nie wartości, ale wyrażenia, które ją obliczy.

Częstym rozwiązaniem jest udostępnienie modalnego formularza "kalkulator".
Można pobrać gotowce z wielu miejsc w sieci, np:
http://www.mvps.org/access/resources/downloads.htm
rozwiązanie Terry Krefta:
http://www.mvps.org/access/downloads/calc.zip

Podobne rozwiązanie proponują autorzy najlepszego moim zdaniem podręcznika do Access'a dla zaawansowanych i średnio zaawansowanych:
http://www.developershandbook.com/
Paul Litwin, Ken Getz i Mike Gilbert

Jego funkcjonalność można wzmocnić funkcją Eval(), która wyliczy każde zrozumiałe dla niej wyrażenie, jak np:

=Eval("Sin(34.23)/Cos(15) + Log(34)")

z globalnymi funkcjami użytkownika włącznie !

Nadal jednak tego typu rozwiązanie nie przypomina funkcjonalnością formuł Excela. Nie możemy bowiem w nim użyć w prosty sposób np: odwołania do innych pól rekordu.

Rozwiązanie jest proste! Nie funkcja Eval() ani zewnętrzny formularz "kalkulator" ale niezwiązane pole tego samego formularza, któremu przekażemy naszą formułę do właściwości ControlSource !

'(...)
zmienna = InputBox("Wprowadź wyrażenie")

If Len(zmienna)>0 Then
    Me.PoleNiezwiazane.ControlSource = "=" & zmienna
    Wynik = Me.PoleNiezwiazane.Value
    Me.PoleNiezwiazane.ControlSource = ""
    Me.PoleWynik = Wynik
End if
'(...)

Cały wic rozbija się teraz o wygodny sposób pobrania wyrażenia. Czy musimy robić to przy pomocy InputBox() czy jeszcze innego niezwiązanego pola ? Chciałoby się aby móc wpisać dowolne wyrażenie w tym samym polu do którego później ma trafić wynik - zupełnie jak w komórce Excel'a ...

Pojawiają się dwa problemy:

do pola liczbowego nie można wpisać niczego co nie jest liczbą (wyzwalany jest błąd)
dopóki nie zajdzie Pole_AfterUpdate nie można nadpisać wpisanej wartości wartością inną
(typowa dolegliwość w zdarzeniu Pole_BeforeUpdate, w którym możemy jedynie wycofać zdarzenie)

Co do pierwszego: błąd na szczęście jest do przechwycenia w Form_Error
Co do drugiego: Rozwiązanie znalazłem na stronie Lebansa: http://www.lebans.com
http://www.lebans.com/Form.htm#BeforeUpdate

Rozwiązanie dedykowane dla BeforUpdate rozbija się o dwie funkcje API:

Private Declare Function GetFocus Lib "user32" () As Long
Private Declare Function SetWindowText Lib "user32" _
       Alias "SetWindowTextA" ( _
          ByVal hwnd As Long, _
          ByVal lpString As String) _
       As Long

Pierwsza w celu pobrania uchwytu do okna aktualnej kontrolki (w Access'ie jedynie aktualna kontrolka staje się prawdziwym oknem !)
Druga w celu "chamskiego" nadpisania jej zawartości ;-)

Używając ich w zdarzeniu Form_Error, po rozpoznaniu numeru właściwego błędu, udało mi się uzyskać efekt przełącznika formuła/wartość, za jakim tak wielu początkujących Accessowców tęskni ;-)))

Nadal nie przypomina to w pełni arkusza kalkulacyjnego. Brak tutaj typowego odwołania do komórek rekordu poprzedniego/następnego. No ale bez przesady ;-)

****
Zauważmy jednak, że można użyć funkcji agregujących ! Tak ! Nic nie stoi na przeszkodzie aby wpisać wyrażenie:
= Sum([Pole2])
czy
=Sum(Pole2)/Count(ID)

ale ...
No właśnie! jest jedno "ale". Funkcje te potrzebują trochę czasu na wyliczenie !
Dlatego zwykle wynik pierwszej formuły będzie =0,
a druga formuła skończy się błędem: Dzielenie przez zero !
Dlatego wprowadziłem pętlę opóźniającą:

For i = 1 To 5
        Sleep 100
        DoEvents 
Next

której zadaniem jest "poczekać" na pojawienie się właściwego wyniku w naszym polu "kalkulatorek"
Pętla jest uaktywniana przełącznikiem na formularzu głównym.
Jeśli ktoś nie ma ochoty korzystać z funkcji agregujących, warto jej nie włączać, aby nie wprowadzać niepotrzebnego opóźnienia, ok. 1sek.

Pętla jest dobrana doświadczalnie - przypuszczam, że dla większej ilości rekordów w tabeli powinna trwać dłużej. Wprowadza to niestety pewien dyskomfort dla użytkownika ...

Generalnie eksperymenty z funkcjami agregującymi można sobie darować. Wprowadzają wiele hazardów.
Być może ktoś z kolegów wymyśli lepszy sposób detekcji czy pole "kalkulatorek" już zostało wyliczone ???

Z ostatniej chwili:
------------------------------------------------------------------------------------
Udało się wymyślić lepsze obejście. Z racji tej, że pola wyrażeniowe wyliczane są w kolejności zgodnej z "kolejność klawisza tab", dołożyłem kolejne pole wyliczane, któremu każdorazowo ustawiam
ControlSource = "=Count([ID])",
a następnie tak długo czekam na wynik, aż jego wartość będzie większa od 0.
To gwarantuje (tak mi się przynajmniej wydaje po paru testach), że poprzedzające go pole kalkulatorek jest już wyliczone (co najwyżej z błędem powodowanym złym wyrażeniem, np: 1/0 - dzielenie przez zero)
------------------------------------------------------------------------------------

Proszę zauważyć, że nasza formuła potrafi wyliczyć nawet takie ciekawe wyrażenie jak iloczyn wartości w kolumnie:
=exp(Sum(log(Abs(nz(pole3,1)))))
(z dokładnością do znaku ;-)

****
Dodatkowo zaproponowałem szkic jak pamiętać indywidualne dla każdego rekordu formuły, nie zapisując ich w pomocniczym, dodatkowym polu tabeli, czy dodatkowej tabeli w ogóle.
Do tego celu użyłem standardowego obiektu Collection, w którym notuję instancje trywialnej klasy Formula, przechowujące klucz rekordu i wpisane w nim wyrażenie.

Pozwala to wpisać wyrażenie, wyliczyć go, przejść do innego rekordu(ów) a następnie wrócić i ewentualnie skorygować wyliczenie, używając analogicznego jak w Excelu klawisza F2.

Ponieważ kolekcja istnieje tak długo jak długo otwarty pozostaje formularz, dlatego zademonstrowałem dodatkowo sposób przepisania jej zawartości do tabeli pomocniczej oraz ponowne wypełnienie jej z tej tabeli, po ponownym otwarciu formularza.

Ktoś zapyta:

To czemu od razu nie wprowadzić dodatkowego pola przechowującego formułę ? Słusznie ! Jeśli te formuły rzeczywiście chcemy przechowywać, nie ma potrzeby stosowania kolekcji.
Rozwiązanie przede wszystkim ilustruje pewne techniki, rzadko przez nas stosowane.

Na przykład w ten sam sposób można utworzyć kolekcję Undo, służącą do wycofywania zmian w wielu rekordach tabeli. Po zamknięciu takiego formularza następowałoby naturalne w takich sytuacjach wykasowanie bufora (luźny pomysł, bez wątpienia raczej w środowisku jednego użytkownika jedynie...)
Sama idea warta jest być może osobnego przykładu realizacji, niemniej szkic rozwiązania jest ten sam !

****
Przy okazji opracowania tego tematu znalazłem niespodziewany błąd Access'a !

Pierwotnie podformularz, wraz ze swoją prywatną kolekcją formuł umieszczony był w sekcji "Szczegóły" formularza głównego.
Okazało się jednak, że podczas zmniejszania rozmiaru tego ostatniego, w momencie gdy znika sekcja "Szczegóły" podformularz jest zwalniany z pamięci !!!
A wraz z nim zwalniany jest jego recordset i wszystkie jego zmienne !
Po ponownym powiększeniu formularza głównego podformularz jest ładowany od nowa a focus ustawiany jest na pierwsze pole pierwszego rekordu !
Niestety wszystkie zmienne (a z nimi moja kolekcja) są bezpowrotnie zresetowane !

Tę dość ciekawą przypadłość łatwo ominąć przenosząc podformularz do innej sekcji, jak w moim rozwiązaniu: do stopki formularza.
Stopkę można dowolnie zawężać, wsuwając ją pod nagłówek - nie powoduje to resetowania zmiennych.
Zresetowanie podformularza następuje jedynie jeśli sami z kodu ukryjemy sekcję:

Me.Section(2).Visible = False

Z opisem tego błędu nie spotkałem się jak do tej pory nigdzie ! Tym bardziej polecam go uwadze wszystkich Accessowców !

****
Jak widać - wiele da się zrobić ! Niemniej nie należy przesadzać ;-))))
Np. funkcje agregujące wprowadzają dużo zamieszania w kodzie.

Pamiętajmy też, że ponieważ wszystko obrabiane jest w zdarzeniu Error formularza, musimy dwukrotnie użyć klawisza by opuścić pole wyliczane, np. przy pomocy klawisza Enter:
- raz {Enter}, aby wymusić próbę update, wygenerować błąd, obsłużyć go i wpisać do pola właściwą wartość (pole nie traci fokusu !)
- drugi raz {Enter}, aby przejść do następnego rekordu czy pola.

Dlatego wyjście z wyrażenia za pomocą myszki (kliknięcie gdziekolwiek indziej w obszarze formularza) nie spowoduje przeniesienia focusu, co może frustrować użytkownika.

Traktując zagadnienie nieco zabawowo, polecam Kolegów uwadze możliwość nadpisywania wartości pola w zdarzeniu BeforeUpdate oraz zabawę z kolekcjami !

Krzysztof Naworyta (5. stycznia 2003)