Come fornire una query personalizzata in base a maschera con il VBA
Impariamo come fornire all'utente una query personalizzata in base ai dati di una maschera per effettuare una ricerca con il VBA Visual Basic for Application
Supponiamo che si voglia effettuare una ricerca più complessa nella maschera frmBooks, basata su criteri come il prezzo, il nome dell’autore o iltipo di libro, invece di usare soltanto il titolo del libro.
Si potrebbe spiegare agli utenti come si usano le funzionalitàFiltro in base a maschera per costruire la ricerca, oppure utilizzare il Filtro in base a maschera per costruire facilmente vari criteri OR su test semplici.
Ma se, per esempio, si vogliono trovare tutti i libri che trattino o di Microsoft Access o di Microsoft Excel, non c’è modo di organizzare questa ricerca con le funzionalità standard di filtro.
In realtà, quando si definisce un filtro per una sottomaschera utilizzando il Filtro in base a maschera (come nel caso della sottomaschera Categorie in frmBooks), si filtrano soltanto le righe della sottomaschera: non si troveranno i libri che hanno soltanto una riga corrispondente nella sottomaschera.
Questo esempio nel database Libri esegue una ricerca semplice in base a uno qualunque fra vari criteri di categoria.
Se si vuole cercare una voce che soddisfi tutti i vari criteri, si deve costruire una query complessa che non soltanto cerca una corrispondenza sulle categorie, ma le conta e si accerta che il conteggio sia uguale al numero dei criteri specificati.
Per un esempio di questo tipo di ricerca complessa, si veda la mascherafrmCollegeSearch nel database College.mdb (frmCercaCollege nella versione italiana).
L’unica soluzione, quindi, è quella di predisporre una query in base a maschera personalizzata che fornisca le opzioni per esplorare tutti i campi importanti e costruire poi con uncodice VBA la clausola Where che serve per risolvere il problema dell’esplorazione delle categorie.
Per cominciare, aprire l’applicazione Libri. (Se se ne è usciti passando alla finestra Database, si può riavviare l’applicazione aprendo frmCopyright.)
Registrarsi, fare clic sul pulsante Libri nel pannello comandi principale e quindi fare clic sulpulsante Cerca nella finestra di dialogo Selezione libri.
Dovrebbe comparire la maschera frmBookSearch, riprodotta qui nella figura (figura 1).
Proviamo a selezionare gli autori il cui cognome comincia con la lettera S, i libri con un prezzo compreso fra 15 e 50 dollari e libri su Microsoft Access o Microsoft Excel (dalla casella di riepilogo a discesa Tipi di libri).
Quando si fa clic sui pulsante Cerca, si dovrebbe vedere la maschera frmBooks che si apre, visualizzando tre libri.
Per vedere come avviene questo, è necessario esplorare la struttura della maschera frmBookSearch.
Spostiamoci nella finestra Database e apriamo la maschera in visualizzazione Struttura. Si dovrebbe vedere una finestra come quella della figura (figura 2).
Si osservi che la maschera non è associata ad alcuna origine di record.
I controlli devono essere non associati per accettare qualunque valore che l’utente possa introdurre nei criteri.
Parte del segreto di questa maschera consiste nella casella combinata TypeList e nel suo evento Dopo aggiornamento (AfterUpdate).
Ogni volta che si seleziona una nuova classificazione di tipo nella casella combinata, viene eseguita la seguente routine evento:
Private Sub TypeList_AfterUpdate()
' E' stato selezionato un nuovo codice tipo,
' quindi lo aggiunge alla lista di ricerca
If IsNothing(Me!TypeCodes) Then
Me!TypeCodes = Me!TypeList
Me!Types = Me!TypeList.Column(0)
Else
Me!TypeCodes = Me!TypeCodes & ", " & Me!TypeList
Me!Types = Me!Types & " OR " & Me!TypeList.Column(0)
End If
End Sub
Questo codice crea una clausola IN che contiene i codici dei tipi corrispondenti. Si osservi che questo codice non verifica se è stata selezionata due volte la stessa classificazione; si potrebbe aggiungere questa funzionalità per migliorare il modo di lavorare del codice.
Questo codice immagazzina la stringa IN in un controllo casella di testo nascosto nella maschera e che si chiama TypeCodes. (La sua proprietà Visibile è impostata su No.)
Per vedere come funziona tutto questo, selezionare il controllo TypeCodes, impostare la sua proprietà Visibile su Sì e passare alla visualizzazione Maschera.
Quando si seleziona una nuova classifficazione, si potranno vedere i contenuti di entrambi i controffi che vengono aggiornati.
Un altro approccio potrebbe consistere nel dichiarare una variabile stringa Static all’interno della routine evento per immagazzinarvi la lista dei codici. Se si procede in questo modo, la variabile deve essere di tipo Static, altrimenti verrebbe reimpostata ogni volta che viene chiamata questa routine.
Il grosso del lavoro si svolge quando si fa clic sul pulsante Cerca. Il codice della routine evento per l’evento clic sul pulsante Cerca è presentato qui di seguito.
Private Sub cmdSearch_Click()
Dim db As Database, rst As Recordset
Dim lngCount As Long, intRtn As Integer, strANameComp As String
Dim intI As Integer, strISBN As String 'Parametri di ricerca immessi (almeno speriamo)... 'Cancella la stringa di ricerca globale gstrWhereBook = ""
' e ne analizza una nuova:
If Not IsNothing(Me!ISBNNumber) Then
strISBN = Me!ISBNNumber
'L’ISBN ha una maschera di input per aiutare i ‘utente,
'ma questi potrebbe immettere ogni sorta
'di numeri e trattini. Il codice seguente esegue un’analisi supplementare per ripulirlo!
'Prima, rimpiazza tutti gli spazi in ISBN con punti interrogativi
intI = 1
Do Until intI > Len(strISBN)
If Mid$(strISBN, intI, 1) = " " Then
If intI = 1 Then
strISBN = "?" & Mid$(strISBN, intI + 1)
Else
strISBN = Left$(strISBN, intI - 1) & "?" & Mid$(strISBN, intI + 1)
End If
End If
' Controlla se ci sono trattini nei posti giusti/sbagliati
If intI = 2 Or intI = 8 Or intI = 12 Then
' Se sta nelle posizioni 2, 8, o 12, allora il trattino è OK
If Mid$(strISBN, intI, 1) = "-" Then
Else
'Altrimenti lo toglie di mezzo!
strISBN = Left$(strISBN, intI - 1) & "?" & Mid$(strISBN, intI)
End If
Else
If Mid$(strISBN, intI, 1) = "-" Then
If intI = 1 Then
strISBN = "?" & strISBN
Else
' Verifica che non ci si ano troppi trattini'
If intI > 12 Then
'Rimpiazza il trattino se va oltre la posizione 12
strISBN = Left$(strISBN, intI - 1) & "?" & Mid$(strISBN, intI + 1)
Else
'Scarica il trattino se nella posizione <=12
strISBN = Left$(strISBN, intI - 1) & "?" & Mid$(strISBN, intI)
End If
End If
End If
End If
intI = intI + 1
Loop
Me!ISBNNumber = Left$(strISBN, 13)
'Ora il risultato dovrebbe essere un ISBN pulito nella forma:
'?-?????-???-?
'nel quale l’utente potrebbe aver specificato cifre per uno o più dei punti interrogativi.
gstrWhereBook = "[ISBNNumber] Like " & Chr$(34) & Me!ISBNNumber
'Aggiunge “*“ in coda, per buona misura,..
If Right$(Me!ISBNNumber, 1) = "*" Then
gstrWhereBook = gstrWhereBook & Chr$(34)
Else
gstrWhereBook = gstrWhereBook & "*" & Chr$(34)
End If
End If
'*** Controlla se è stato chiesto un titolo
If Not IsNothing(Me!Title) Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "[Title] Like " & Chr$(34) & Me!Title
Else
gstrWhereBook = gstrWhereBook & " AND [Title] Like " & Chr$(34) & Me!Title
End If
If Right$(Me!Title, 1) = "*" Then
gstrWhereBook = gstrWhereBook & Chr$(34)
Else
gstrWhereBook = gstrWhereBook & "*" & Chr$(34)
End If
End If
'*** Vede se c’è un nome o un cognome di autore
If Not IsNothing(Me!LastName) Then
strANameComp = "[LastName] Like " & Chr$(34) & Me!LastName
If Right$(Me!LastName, 1) = "*" Then
strANameComp = strANameComp & Chr$(34)
Else
strANameComp = strANameComp & "*" & Chr$(34)
End If
End If
If Not IsNothing(Me!FirstName) Then
If IsNothing(strANameComp) Then
strANameComp = "[FirstName] Like " & Chr$(34) & Me!FirstName
Else
strANameComp = strANameComp & " AND [FirstName] Like " & Chr$(34) & Me!FirstName
End If
If Right$(Me!FirstName, 1) = "*" Then
strANameComp = strANameComp & Chr$(34)
Else
strANameComp = strANameComp & "*" & Chr$(34)
End If
End If
If Not IsNothing(strANameComp) Then ' Abbiamo costruito un altro confronto?
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "[ISBNNumber] IN (Select ISBNNumber From qryBookAuthors Where " & strANameComp & ")"
Else
gstrWhereBook = gstrWhereBook & " AND [ISBNNumber] IN (Select ISBNNumber From qryBookAuthors Where " & strANameComp & ")"
End If
End If
If Not IsNothing(Me!LowPrice) Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "[SuggPrice] >= " & Me!LowPrice
Else
gstrWhereBook = gstrWhereBook & " AND [SuggPrice] >= " & Me!LowPrice
End If
End If
'* * * Imposta il confronto sul prezzo
If Not IsNothing(Me!HighPrice) Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "[SuggPrice] <= " & Me!HighPrice
Else
gstrWhereBook = gstrWhereBook & " AND [SuggPrice] <= " & Me!HighPrice
End If
End If
'* * * Imposta la verifica su “contiene disco”
If Me!Disk Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "([Disk] Is Not Null)"
Else
gstrWhereBook = gstrWhereBook & " AND ([Disk] Is Not Null)"
End If
End If
'Imposta la verifica su Esaurito
If Me!OutOfPrint Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "(Not [OutOfPrint])"
Else
gstrWhereBook = gstrWhereBook & " AND (Not [OutOfPrint])"
End If
End If
' Costruisce la stringa IN per i tipi di codice
If Not IsNothing(Me!TypeCodes) Then
If IsNothing(gstrWhereBook) Then
gstrWhereBook = "[ISBNNumber] IN (Select ISBNNumber From qryBookCategories Where CategoryID IN (" & Me!TypeCodes & "))"
Else
gstrWhereBook = gstrWhereBook & " AND [ISBNNumber] IN (Select ISBNNumber From qryBookCategories Where CategoryID IN (" & Me!TypeCodes & "))"
End If
End If
' Se non ci sono criteri, non è richiesta alcuna attività!
If IsNothing(gstrWhereBook) Then
MsgBox "No criteria specified.", vbExclamation, "Books"
Exit Sub
End If
'*** Cerca in base alla stringa che è stata costruita
' Nasconde questa maschera e attiva la clessidra
Me.Visible = False
DoCmd.Hourglass True
If IsLoaded("frmBooks") Then ' If books form already open,
' allora si limita a filtrarla
Forms!frmBooks.SetFocus
DoCmd.ApplyFilter , gstrWhereBook
If Forms!frmBooks.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Books meet your criteria", vbExclamation, "Books"
DoCmd.ShowAllRecords
If gintIsAdmin Then
Forms!frmBooks!cmdAddNew.Visible = True
Else
Forms!frmBooks!cmdAddNew.Visible = False
End If
Forms!frmBooks!cmdShowAll.Visible = False
Me.Visible = True
Exit Sub
End If
If IsNothing(Me!TypeCodes) And (Not IsNothing(strANameComp)) Then
'Se non sono state indicate categorie dei libri, ma si fa una ricerca sul nome,
'attiva l’opzione per visualizzare l’autore
Forms!frmBooks!tabDisplay = 1
End If
Forms!frmBooks!cmdAddNew.Visible = False
Forms!frmBooks!cmdShowAll.Visible = True
DoCmd.Hourglass False
Else
' Trova se ci sono libri che soddisfano la clausola Where
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT DISTINCTROW " & _
"tblBooks.ISBNNumber " & _
"FROM tblBooks" & _
" WHERE " & gstrWhereBook & ";")
' Se non ne trova, lo dice e torna
'a rendere visibile il controllo
If rst.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Books meet your criteria", vbExclamation, "Books"
gstrWhereBook = ""
Me.Visible = True
rst.Close
Exit Sub
End If
' Si porta sull’ultima riga per ottenere un conteggio accurato dei record
rst.MoveLast
lngCount = rst.RecordCount
DoCmd.Hourglass False
'Se sono più di 10, chiede se può bastare vedere un riepilogo
If lngCount > 10 Then
intRtn = MsgBox("More than 10 books meet your criteria. " & _
"Click Yes to see a summary list for all " & lngCount & _
" books found, " & _
"No to see complete data on all that match, " & _
"or Cancel to try again.", vbInformation + vbYesNoCancel, "Books")
Select Case intRtn
Case vbCancel ' Cancel - Try again
Me.Visible = True
Exit Sub
Case vbYes ' Yes - show summary form
DoCmd.OpenForm FormName:="frmBookSummary", WhereCondition:=gstrWhereBook
DoCmd.Close acForm, Me.Name
Forms!frmBookSummary.SetFocus
Exit Sub
End Select
End If
' Risposta No oppure non più di 10, mostra dettagli completi
DoCmd.OpenForm FormName:="frmBooks", WhereCondition:=gstrWhereBook
Forms!frmBooks!cmdAddNew.Visible = False
Forms!frmBooks!cmdShowAll.Visible = True
If IsNothing(Me!TypeCodes) And (Not IsNothing(strANameComp)) Then
' Se non ci sono categorie dei libri, ma si è fatta una ricerca su un nome,
' attiva l’opzione per visualizzare gli autori
Forms!frmBooks!tabDisplay = 1
End If
End If
' Chiude e abbiamo finito
DoCmd.Close acForm, Me.Name
End Sub
La prima parte della routine lavora con i dati immessi nel campo Numero ISBN per verificare che questo abbia un formato corretto.
Sebbene per questo campo esista una maschera di input per aiutare l’utente, la maschera deve essere definita in modo da consentire all’utente di immettere il carattere jolly “?“ ovunque nella stringa.
In questo modo si consente loro di immettere anche trattini in eccesso e altri caratteri. Inoltre, dal momento che la casella di testo non è associata, non esiste una maniera semplice per impedire all’utente di immettere più di 13 caratteri.
(Si potrebbe fissare un limite scrivendo una routine per l’evento TastoGiù, che elimini eventuali digitazioni eseguite dopo la tredicesima posizione.)
Questo codice esamina, un carattere alla volta, quel che è stato immesso.
Se trova un trattino in una posizione scorretta, spinge il testo verso destra e inserisce un carattere jolly punto interrogativo.
Se trova qualcosa che non sia un trattino nelle posizioni 2, 8 e 12, sposta la stringa verso destra e inserisce un trattino.
Infine, preleva iprimi 13 caratteri e vi aggiunge in fondo un carattere jolly asterisco (*), i vari segmenti di codice successivi “assemblano” gradualmente una stringa WHERE esaminando uno per volta i controlli non associati.
Se il campo corrispondente è una stringa, il codice crea un test usando ilpredicato LIKE, in modo che qualunque cosa l’utente immetta possa confrontarsi con qualunque parte del campo nella tabella sottostante, ma non tutti i campi sono stringhe.
Per i due campi del prezzo, il codice costruisce un semplice confronto per “>=“ o “<=“ Se non vi è un disco allegato al libro, il campo [Disk] sarà Null.
Per determinare se un libro è esaurito, basta un semplice test Vero/Falso.
Quando la funzione aggiunge una clausola mentre costruisce la stringa WHERE, inserisce laparola chiave AND fra le clausole se ne esistono già altre.
Siccome l’origine dei record sottostante per lamaschera frmBooks non contiene informazioni dirette sull’autore o sulle categorie, la routine deve costruire unpredicato usando una sottoquery se si chiede una ricerca in base al nome di un autore o a una categoria.
Nel primo caso, la routine esamina entrambi i campi nome e cognome e costruisce due clausole di confronto separate per estrarre ilnumero ISBN.
La sottoquery trova la lista dei numeri ISBN da una query che correla tblBookAuthor (che contiene il campo ISBNNumber) con tblAuthors (nella quale si possono trovare i campi con i nomi degli autori).
Per una ricerca in base alla categorie, la sottoquery usa una query che correla tblBookCategories (che ha ISBNNumber) con tblCategories (che ha il campo CategorylD).
La parte finale della routine costruisce un semplice recordset sulle tabelle usate da entrambe le maschere frmBooks e frmBooksSummary, applicando la clausola WHERE costruita dal codice nella prima metà della routine.
Se non trova alcun record, si serve della funzione MsgBox per informare l’utente, dandogli poi la possibilità di tentare di nuovo.
La prima volta che in un codice di programma si apre un oggetto recordset, la sua proprietà RecordCount è 0 se il recordset è vuoto ed è un valore maggiore di 0 se il recordset contiene qualche record.
Laproprietà RecordCount di un oggetto Recordset contiene soltanto un conteggio del numero delle righe visitate e non il numero delle righe che stanno effettivamente nel recordset.
Per cui, se trova qualche riga, la routine si porta sull’ultima riga del recordset temporaneo per ottenere un conteggio accurato.
Quando il conteggio dei record è superiore a 10, la routine consente all’utente di esaminare in una maschera frmBookSummary un riepilogo dei record trovati, esaminare tutti i dati nella maschera completa frmBooks o di provare un’altra volta.
Qui abbiamo un buon esempio di utilizzo della funzione MsgBox non soltanto per visualizzare qualche dato variabile, ma anche per rispondere a una scelta dell’utente. Ora esamineremo come funziona la maschera frmBookSummary.
Selezionare da un elenco di riepilogo
Come abbiamo visto nella procedura Search_Click, se più di 10 righe corrispondono ai criteri immessi, l’utente può fare una scelta.
Per vedere questa caratteristica all’opera, si chieda nella maschera frmBookSearch di eseguire una ricerca sul valore Advanced come Tipo di libro. (figura 3).
Il risultato dovrebbe presentarsi come nella figura che segue, nella quale si segnala che sono stati trovati21 libri classificati come Advanced.
Quando si fa clic su Sì, la routine Search_Click apre la maschera frmBookSummary, come si vede nella figura (figura 4).
Si può scorrere fino a una riga qualsiasi, mettere il focus su quella riga (accertandosi che l’indicatore di selezione di riga punti a quella riga) e poi fare clic sul pulsante Mostra dettagli per aprire la maschera frmBooks e visualizzare i dettagli per il libro selezionato.
Come si vede, questo è un sistema molto efficiente per aiutare l’utente a restringere una ricerca fino a un libro molto specifico.
Dietro al pulsante Mostra dettagli c’è una semplice routine VBA, illustrata qui sotto, che utffizza il valore corrente del campo ISBNNumber nella maschera per costruire un filtro per il metodo OpenForm.
Private Sub Details_Click()
' Ne è stato scelto uno dalla lista, quindi apre l’elenco dei libri filtrato...
' (Attivato anche se si fa doppio clic in un punto
' qualunque della riga)
gstrWhereBook = "[ISBNNumber] = """ & Me!ISBNNumber & """"
DoCmd.OpenForm FormName:="frmBooks", WhereCondition:=gstrWhereBook
DoCmd.Close acForm, Me.Name
Forms!frmBooks.SetFocus
Forms!frmBooks!cmdAddNew.Visible = False
Forms!frmBooks!cmdShowAll.Visible = True
End Sub
Collegarsi a dati correlati in un’altra maschera
Un altro modo di gestire ulteriori dati correlati è quello di visualizzare i dettagli di quei dati in un’altra maschera.
La sottomaschera fsubAuthorBooks ha un pulsante di comando su ciascuna riga per collegare l’utente ai dettagli completi del libro selezionato in quella riga.
Quando si fa clic su questo pulsante, il codice VBA apre la maschera frmBooks del libro selezionato nella sottomaschera, come si vede nella figura (figura 5).
Questo è un modo semplice per visualizzare tutti i particolari di uno specifico libro mentre si stanno esaminando dati sugli autori.
Si può trovare un analogo pulsante Dettagli nella maschera fsubBookAuthors, che consente di esaminare i dettagli su un autore mentre si lavora sui dati dei libri.
Esempi di accesso ai dati con VBA Microsoft Access
Tutorials su Microsoft Access
Dr.Maurizio Cucchiara
autore del corso:
"Corso Base di Microsoft Access"
disponibile on line su questo sito
MC2 E-Learning S.a r.l.
Formazione per il Web
http://www.mc2.it/html/access.html
Cerca sul web con Google: