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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
|
Private Sub Update_clef_classe()
'mise a jour du menu deroulant clef_classe
Dim Text1 As String
Dim SQL2 As String
Dim SQL3 As String
'je vais vérifier qu'il existe bien un enregistrement dans la table classe
'pour l'espèce recherché
Text1 = " UNION SELECT [Clef_classe], [Nom], [Debut], [Fin] FROM [ref_classe] WHERE [Nom] = 'INDIFFERENT'"
If Not IsNull(Me!Clef_espece.Value) Then
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim clefrecordset1 As New Recordset
clefrecordset1.ActiveConnection = cnn1
SQL2 = "SELECT [Classe] FROM [ref_espece]"
SQL2 = SQL2 & " WHERE [ref_espece].[Clef_espece] = " & espece.Value
clefrecordset1.Open (SQL2)
If Not IsNull(clefrecordset1.Fields("Classe").Value) And clefrecordset1.Fields("Classe").Value = False Then
'il n'y a pas d'enregistrement j'utilise alors les valeurs par défaut
SQL3 = "SELECT [Clef_classe], [Nom] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE [Debut] IS NULL AND [Clef_espece] IS NULL "
SQL3 = SQL3 & " ORDER BY [Clef_classe], [Nom] "
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
Else
'maintenant j'ai des enregistements dans la table classe
'si j'ai rentré un age je l'utilise pour préciser la recherche
If Not IsNull(Me!Age_precis.Value) Then
Select Case Me!Sexe.Value
'cas ou le sexe est M
Case "M"
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " AND Sexe <> 'F'"
SQL3 = SQL3 & " AND " & Me!Age_precis.Value & " BETWEEN [Debut] AND [Fin]"
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
Case "F"
'cas ou le sexe est F
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " AND Sexe <> 'M' "
SQL3 = SQL3 & " AND " & Me!Age_precis.Value & " BETWEEN [Debut] AND [Fin]"
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
Case Else
'cas ou le sexe est ni M ni F
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " AND " & Me!Age_precis.Value & " BETWEEN [Debut] AND [Fin]"
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
End Select
Else
'cas ou je n'ai pas rentré d'age
'cas ou le sexe est M
Select Case Me!Sexe
Case M
'cas ou le sexe est M
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " AND Sexe <> F "
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3 & Text1
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
Case F
'cas ou le sexe est F
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " AND Sexe <> M "
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3 & Text1
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
Case Else
'cas ou le sexe est ni M ni F
SQL3 = "SELECT [Clef_classe], [Nom], [Debut], [Fin] "
SQL3 = SQL3 & " FROM [ref_classe] WHERE Clef_espece = " & espece.Value
SQL3 = SQL3 & " ORDER BY [Fin], [Debut],[Nom], [Clef_classe]"
SQL3 = SQL3 & Text1
Me!ref_classe_Nom.RowSourceType = "Table/Query"
Me!ref_classe_Nom.RowSource = SQL3
End Select
End If
End If
End If
'clefrecordset1.Close
SQL2 = ""
SQL3 = ""
Set cnn1 = Nothing
End Sub |
Partager