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
|
Sub Formload()
Dim Id_Arg As Integer
Dim Reference As Integer
Dim Localisation As String
Dim Reponsgbox As String
Dim NbrOccurences As Integer
Dim SQL As String
Dim rs As dao.Recordset
Dim oDb As dao.Database
Dim rs2 As dao.Recordset
SQL = "SELECT TBL_CONTACTS.ID_CONTACT,"
SQL = SQL & "TBL_ENQUETES.ENQ_P_A_CATEGORIE,"
SQL = SQL & " Max(TBL_ENQUETES.ENQ_DATE) AS "
SQL = SQL & "[Date de la dernière enquête qui doit être révisée]"
SQL = SQL & " FROM TBL_CONTACTS INNER JOIN TBL_ENQUETES "
SQL = SQL & "ON TBL_CONTACTS.ID_CONTACT = "
SQL = SQL & "TBL_ENQUETES.ID_CONTACT "
SQL = SQL & "WHERE ((( TBL_CONTACTS.CON_DDF) Is Null)) "
SQL = SQL & "GROUP BY TBL_CONTACTS.ID_CONTACT, "
SQL = SQL & "TBL_ENQUETES.ENQ_P_A_CATEGORIE "
SQL = SQL & "HAVING (((Max(TBL_ENQUETES.ENQ_DATE))"
SQL = SQL & "))<=IIf([ENQ_P_A_CATEGORIE]='FAMILLE', "
SQL = SQL & "Now()-180,Now()-365);"
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveLast
rs.MoveFirst
NbrOccurences = rs.RecordCount
Set rs = CurrentDb.OpenRecordset(SQL)
'permet d'afficher le message d'alerte avec bouton rouge si enquêtes > à 100
'sinon simple VBOKonly si inf à 100 et rien du tout si pas d'occurrences du click sur OK
If NbrOccurences > 100 Then
Reponsgbox = MsgBox("Attention, vous devez faire des enquetes", vbYesNo, "Avertissememt")
If Reponsgbox = vbYes Then
DoCmd.OpenForm "FRM_ENQESS"
End If
ElseIf NbrOccurences <= 100 Then
MsgBox "Attention, vous devez faire des enquetes", vbOKOnly, "Avertissememt"
ElseIf NbrOccurences = 0 Then
End If
Reference = 0
Localisation = "FRM_ENQUETE_A_JOUR"
Set oDb = CurrentDb()
Debug.Print SQL
Set rs2 = oDb.OpenRecordset("TBL_ARGUMENTS")
rs2.MoveLast
rs2.MoveFirst
Do Until rs.EOF = True
rs2.AddNew
rs2.Fields("ARG_REFERENCE") = Reference
rs2.Fields("ARG_LOCALISATION") = Localisation
rs2.Fields("ARG_ARGUMENT") = rs![TBL_CONTACTS]![ID_CONTACT]
rs2.Fields("ARG_DESCRIPTION") = (DateDiff("m", rs!ENQ_DATE, Now()))
rs2.Update
rs.MoveNext
Loop
Set rs2 = Nothing
Set oDb = Nothing
End Sub |
Partager