Recherche multicritere

Commenter

Contenu du snippet

salut à tous,
je vous envoye un code sur la recherche multicritère (VB ET SQL)

envoyer vos commentaires: klexus@caramail.com

Source / Exemple : 


Option Explicit
Public massession As Workspace
Public base As Database
Public rsResultat As Recordset

Private Sub chktouscanal_Click()
If Me.chktouscanal Then
Me.cmbrechcanal.Visible = False
Label8.Visible = False
Else
Me.cmbrechcanal.Visible = True
Label8.Visible = True
End If
End Sub

Private Sub Chktouscode_Click()
If Me.Chktouscode Then
Me.cmbrechcode.Visible = False
Label7.Visible = False
Else
Me.cmbrechcode.Visible = True
Label7.Visible = True
End If
End Sub

Private Sub chktouscontact_Click()
If Me.chktouscontact Then
Me.cmbrechcontact.Visible = False
label10.Visible = False
Else
Me.cmbrechcontact.Visible = True
label10.Visible = True
End If
End Sub

Private Sub chktousprovince_Click()
If Me.chktousprovince Then
Me.cmbrechprovince.Visible = False
Label5.Visible = False
Else
Me.cmbrechprovince.Visible = True
Label5.Visible = True
End If
End Sub

Private Sub chktoussite_Click()
If Me.chktoussite Then
Me.cmbrechsite.Visible = False
Label4.Visible = False
Else
Me.cmbrechsite.Visible = True
Label4.Visible = True
End If

End Sub

Private Sub cmdaddrech_Click()
lblstat.Caption
End Sub

Private Sub cmdconsult_Click()
DBGrid1.Visible = True
End Sub

Private Sub cmdfermer_Click()
Frmsite.Show
Unload Me
End Sub

Private Sub cmdnewrech_Click()
Frame1.Visible = True
MSFlexGrid1.Visible = True
DBGrid1.Visible = False
End Sub

Private Sub cmdrech_Click()
 On Error Resume Next 'gestion des erreurs
    Call executeRequete
    Call INIFLEX
    
End Sub

Private Sub Command3_Click()
Frame1.Visible = False
End Sub

Private Sub Form_Load()
'initialisation des label, des combox et chekbox
Dim tabrechsite As Recordset
Dim ctl As Control
Set massession = DBEngine.Workspaces(0)
Set base = massession.OpenDatabase(App.Path + "\TMG18.mdb")
For Each ctl In Me.Controls
Select Case Left(ctl.Name, 3)
Case "chk"
ctl.Value = 0
Case "lbl"
ctl.Caption = "_*_*_"
Case "cmb"
ctl.Visible = True
End Select
Next ctl
Call INIFLEX
cmdaddrech.Visible = False
Command3.Visible = False
MSFlexGrid1.Visible = False
End Sub

Sub executeRequete()
Dim sql As String
Dim rsResultat As Recordset
Dim sqlText As String
     
sqlText = "SELECT distinct Codesite as [Code site], Canal, Societestation as Site, Province, [Nom contact] as [Nom du Contact] " _
& "FROM tbrechsite "

    If Not Chktouscode Then
        sqlText = sqlText & " where Codesite=" & CLng(Trim(cmbrechcode.Text))
    End If
    
    If Not chktousprovince Then
        sqlText = sqlText & "and Province='" & Trim(cmbrechprovince.Text) & "'"
    End If
    
    If Not chktouscanal Then
    sqlText = sqlText & "and Canal='" & Trim(cmbrechcanal.Text) & "'"
    End If
    
    If Not chktouscontact Then
    sqlText = sqlText & "and [Nom contact]='" & Trim(cmbrechcontact.Text) & "'"
    End If
    
    If Not chktoussite Then
    sqlText = sqlText & "and Societestation='" & Trim(cmbrechsite.Text) & "'"
    End If
    
    
 
        Set rsResultat = base.OpenRecordset(sqlText)
        Debug.Print sqlText
        Data2.RecordSource = sqlText
        Data2.Refresh
        rsResultat.MoveLast
        lblstat.Caption = "Nombres d'enrégistrement trouvés: " & " " & rsResultat.RecordCount
End Sub
 
Sub INIFLEX()
MSFlexGrid1.Cols = 5
MSFlexGrid1.Rows = 2

'définitla hauteur des colonnes
MSFlexGrid1.RowHeight(0) = 300
MSFlexGrid1.ColAlignment(-1) = 3
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
'définit la largeur de la colonne 0
MSFlexGrid1.ColWidth(0) = 1000
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = 0
MSFlexGrid1.Text = "Code Site"
'définit la largeur de la colonne 1
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.Col = 1
MSFlexGrid1.Row = 0
MSFlexGrid1.Text = "Canal"
'définit la largeur de la colonne 2
MSFlexGrid1.ColWidth(2) = 3000
MSFlexGrid1.Col = 2
MSFlexGrid1.Row = 0
MSFlexGrid1.Text = "Site"
'définit la largeur de la colonne 3
MSFlexGrid1.ColWidth(3) = 2000
MSFlexGrid1.Col = 3
MSFlexGrid1.Row = 0
MSFlexGrid1.Text = "Province"
'définit la largeur de la colonne 3
MSFlexGrid1.ColWidth(4) = 4000
MSFlexGrid1.Col = 4
MSFlexGrid1.Row = 0
MSFlexGrid1.Text = "Nom du conctact"
End Sub

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (klexus)