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 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
|
Sub TableauDoubleEntrées()
Dim r As Recordset, sql$, f As Field, i%
CurrentDb.Execute ("drop Table Test")
Set r = CurrentDb.OpenRecordset("select Appareil From TabAppareil")
With r
.MoveFirst
While Not .EOF
If Not .AbsolutePosition > 0 Then sql = "Create Table Test(Utilisateur char(20), [" & .Fields("Appareil") & "] YesNo" Else sql = sql & ", [" & .Fields("Appareil") & "] YesNo "
.MoveNext
Wend
.Close
End With
Set r = Nothing
sql = sql & ");"
CurrentDb.Execute (sql)
sql = "INSERT INTO Test ( Utilisateur )SELECT TabUtilisateur.Utilisateur FROM TabUtilisateur INNER JOIN TabTitre ON TabUtilisateur.nTitre = TabTitre.nTitre WHERE (((TabTitre.Titre)='Technicien') AND ((TabUtilisateur.[Actif])=-1));"
CurrentDb.Execute (sql)
create_form ("select * from Test")
'sql = sql
End Sub
Public Function create_form(sql As String) As Boolean
Dim frm As Form
Dim rst As Recordset
Dim ctl As Control
Dim i%, j%
'------------------Source---------------------------
'Morsi
'http://morsi.developpez.com/?page=Form#formdynamique
'----------------------------------------------------
'--Ouvrir le formulaire en mode modification et caché
DoCmd.OpenForm "Test", acDesign, , , , acHidden
Forms![Test].RecordSource = Empty
' --suppression de tous les contrôles avant de les créer de nouveau
For i = Forms!Test.Controls.Count To 1 Step -1 ' Each ctl In Forms!Test.Controls
'If ctl.Name <> "DateduJour" And ctl.Name <> "Valider" Then DeleteControl "Test", ctl.Name
If Forms!Test.Controls(i - 1).Name <> "LabelPlanning" And Forms!Test.Controls(i - 1).Name <> "Valider" And Forms!Test.Controls(i - 1).Name <> "Valider" Then DeleteControl "Test", Forms!Test.Controls(i - 1).Name
Next
' --Source de données de mon formulaire
Forms![Test].RecordSource = sql
Set rst = CurrentDb.OpenRecordset(sql)
' --nous ne pensons pas que vous aurez plus de 100 contrôles
Dim controle(1 To 100) As Control
' --Création des contrôles
If rst.RecordCount <> 0 Then
i = 1
j = 1000
While i < rst.Fields.Count
' -- Créer le contrôle i
If rst.Fields(i - 1).Name = "Utilisateur" Then
Set controle(i) = CreateControl("Test", acTextBox)
With controle(i)
.Left = 10
.Width = 1800
.BackColor = "10081789"
End With
Else
Set controle(i) = CreateControl("Test", acCheckBox)
With controle(i)
.Left = j + 1800
.Width = 284
.Top = 57
End With
j = j + 1134
End If
controle(i).Name = "TXT_" & rst.Fields(i - 1).Name
controle(i).ControlSource = rst.Fields(i - 1).Name
i = i + 1
Wend
End If
' --Création des entêtes
j = 1000
i = 1
While i < rst.Fields.Count
Set controle(i) = CreateControl("Test", acTextBox, acHeader)
controle(i).Name = "HD_" & rst.Fields(i - 1).Name
controle(i).ControlSource = "='" & rst.Fields(i - 1).Name & "'"
If rst.Fields(i - 1).Name <> "Utilisateur" And rst.Fields(i - 1).Name <> "LabelPlanning" Then
With controle(i)
.Left = 150 + j
.Width = 1150
.Height = 700
.Top = 800
.BackColor = "10081789"
.SpecialEffect = 0
.BorderStyle = 1
.TextAlign = 2
.FontWeight = 700
End With
Else
controle(i).Visible = False '100 + j
End If
i = i + 1
j = j + 1150
Wend
rst.Close
Set rst = Nothing
' --Sauvegarder le formulaire
DoCmd.Save acForm, "Test"
DoCmd.OpenForm "Test"
End Function |
Partager