Discussion :

[Diess]

Bonjour
je dois concevoir une base de données d'aide à la gestion d'une bibliothèque.
bon j'ai pu faire avancer quelque peu, mais j'ai des soucis avec une requêtes qui est censé faire une recherche dans les champ en dessous ...
en principe seul les résultats correspondant à la recherche sont affiché mais ça ne pas
de plus je voudrais pouvoir gérer les Accès aux modifications par une demande de mot de passe
ci joint la base de donnée merci d'avance

[Diess]

voila ce que le code VBA donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub txt_Search_Change()
'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
Dim strFullList       As String
Dim strFilteredList   As String
 
 
10    If blnSpace = False Then
20      Me.Refresh 'refresh to make sure the text box changes are actually available to use
 
        'specify the default/full rowsource for the control
30      strFullList = "SELECT [tbl_Livres].ID_Donnee, tbl_Categories.Categorie, tbl_Collections.Collection, [tbl_Livres].Theme, [tbl_Livres].Titre, [tbl_Livres].MotsCles, tbl_Auteurs.Nom, tbl_Auteurs.Premons, [tbl_Livres].DatePublication, tbl_Regions.Region, [tbl_Livres].Ville, [tbl_Livres].Langue, [tbl_Livres].Statut FROM tbl_Regions INNER JOIN (tbl_Emplacements INNER JOIN (tbl_Collections INNER JOIN (tbl_Categories INNER JOIN (tbl_Auteurs INNER JOIN [tbl_Livres] ON tbl_Auteurs.ID_Auteur = [tbl_Livres].Auteur) ON tbl_Categories.ID_Categorie = [tbl_Livres].Categorie) ON tbl_Collections.ID_Collection = [tbl_Livres].Collection) ON tbl_Emplacements.ID_Emplacement = [tbl_Livres].Emplacement) ON tbl_Regions.ID_Region = [tbl_Livres].Region ORDER BY [tbl_Livres].ID_Donnee;"
 
        'specify the way you want the rowsource to be filtered based on the user's entry
40      strFilteredList = "SELECT [tbl_Livres].ID_Donnee, tbl_Categories.Categorie, tbl_Collections.Collection, [tbl_Livres].Theme, [tbl_Livres].Titre, [tbl_Livres].MotsCles, tbl_Auteurs.Nom, tbl_Auteurs.Premons, [tbl_Livres].DatePublication, tbl_Regions.Region, [tbl_Livres].Ville, [tbl_Livres].Langue, [tbl_Livres].Statut FROM tbl_Regions INNER JOIN (tbl_Emplacements INNER JOIN (tbl_Collections INNER JOIN (tbl_Categories INNER JOIN (tbl_Auteurs INNER JOIN [tbl_Livres] ON tbl_Auteurs.ID_Auteur = [tbl_Livres].Auteur) ON tbl_Categories.ID_Categorie = [tbl_Livres].Categorie) ON tbl_Collections.ID_Collection = [tbl_Livres].Collection) ON tbl_Emplacements.ID_Emplacement = [tbl_Livres].Emplacement) ON tbl_Regions.ID_Region = [tbl_Livres].Region "
50      strFilteredList = strFilteredList & "WHERE [ID_Donnee] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Categorie] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Collection] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Theme] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Titre] LIKE ""*" & Me.txt_Search.Value & "*"" OR [MotsCles] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Nom] LIKE ""*" & Me.txt_Search.Value & "*""  OR [Premons] LIKE ""*" & Me.txt_Search.Value & "*"" OR [DatePublication] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Region] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Ville] LIKE ""*" & Me.txt_Search.Value & "*"" OR [Langue] LIKE ""*" & Me.txt_Search.Value & "*"" ORDER BY [ID_Donnee];"
 
        'run the search
60      fLiveSearch Me.txt_Search, Me.lst_SearchResults, strFullList, strFilteredList, Me.txtCount
70    End If

il ya aussi un module qui effectue la recherche

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
Option Compare Database
Option Explicit
 
 
Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
                      strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
'==================================================================================
'  THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
'  ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
'  FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
'  SQL (ROWSOURCE) SHOULD BE.
'
'  ctlSearchBox       THE TEXTBOX THE USER TYPES IN TO SEARCH
'
'  ctlFilter          THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
'
'  strFullSQL         THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
'                     RESULTS ARE RETURNED
'
'  strFilteredSQL     THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
'                     YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
'                     TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
'
' ctlCountLabel       (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
'                     COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
'=====================================================================================
 
'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
  Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
  Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search
 
 
10    On Error GoTo err_handle
 
          'restore the cursor to where they left off
20        ctlSearchBox.SetFocus
30        ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
 
40        If ctlSearchBox.Value <> "" Then
                 'Only fire if they've input more than two characters (otherwise it's wasteful)
50               If Len(ctlSearchBox.Value) > iSensitivity Then
60                   ctlFilter.RowSource = strFilteredSQL
70                   If ctlFilter.ListCount > 0 Then
80                       ctlSearchBox.SetFocus
90                       ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
100                   Else
110                     If blnEmptyOnNoMatch = True Then
120                      ctlFilter.RowSource = ""
130                     Else
140                      ctlFilter.RowSource = strFullSQL
150                     End If
160                   End If
170             Else
180               ctlFilter.RowSource = strFullSQL
190             End If
 
200        Else
210           ctlFilter.RowSource = strFullSQL
220        End If
 
            'if there is a count label, then update it
230         If IsMissing(ctlCountLabel) = False Then
240           ctlCountLabel.Caption = "Résultats coorespondant: " &  Format(ctlFilter.ListCount - 1, "#,##0") & " documents"
250         End If
 
260   Exit Function
err_handle:
270   Select Case Err.Number
    Case 91 'no ctlCountLabel
       'exit
280       Case 94 'null string
       'exit
290       Case Else
300         MsgBox "Une erreur inconnue s'est produite: " & vbCrLf & Err.Description & _
            vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
310   End Select
 
 
End Function
'   ***** Code End ******

voilà jattends votre aide SVP