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 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196
| Private Sub CreateForm(prm_tTableName As String)
'Créer un formulaire pour la table
Dim tTableName As String: tTableName = prm_tTableName
Dim tFormName As String: tFormName = "frm_" & Mid(tTableName, InStr(tTableName, "_") + 1)
Dim oAccessObject As AccessObject: For Each oAccessObject In CurrentProject.AllForms
If oAccessObject.Name = tFormName Then
GoTo Exit_CreateForm
End If
Next oAccessObject
Dim tTempFormName As String
Dim oDB As DAO.Database: Set oDB = CurrentDb
Dim oTable As DAO.TableDef: Set oTable = oDB.TableDefs(tTableName)
Dim oField As DAO.Field
Dim oFrm As Form
Dim oTextBox As TextBox
Dim oCtrl As Control
Dim oComboBox As ComboBox
Dim oCheckBox As CheckBox
Dim oLabel As Label
'=== Crée la query source
' au moins une ébauche
Dim tQueryName As String: tQueryName = "qry_" & Mid(tFormName, 5)
Dim oQuery As DAO.QueryDef
Dim sSQL As String
sSQL = "SELECT [" & prm_tTableName & "].*"
sSQL = sSQL & " FROM [" & prm_tTableName & "]"
For Each oField In oTable.Fields
Select Case oField.Name
Case "txt_NomCourt_FR", "txt_NomCourt"
sSQL = sSQL & " ORDER BY [" & prm_tTableName & "].[num_OrdreTriAff_Nz], [" & prm_tTableName & "].[" & oField.Name & "];"
Exit For
Case Else
'ne rien faire
End Select
Next oField
Set oQuery = oDB.CreateQueryDef(tQueryName, sSQL)
'--- Crée la query source
'=== Crée le formulaire
Set oFrm = Application.CreateForm()
tTempFormName = oFrm.Name
Call DoCmd.Save(acForm, tTempFormName)
Call DoCmd.Close(acForm, tTempFormName)
Call DoCmd.Rename(tFormName, acForm, tTempFormName)
Call DoCmd.OpenForm(tFormName, acDesign)
Set oFrm = Forms(tFormName)
oFrm.RecordSource = tQueryName
oFrm.AllowFormView = False
oFrm.AllowDatasheetView = True
oFrm.AllowLayoutView = False
Dim nIRow As Long
'Créer les champs
For Each oField In oTable.Fields
If oField.Name Like "*_Nz" Then
GoTo NextField
End If
If oField.Name Like "uid_*" Then
Set oComboBox = Application.CreateControl(tFormName, acComboBox)
oComboBox.ColumnCount = 2
oComboBox.ColumnWidths = "0;10cm"
oComboBox.RowSource = "qry_Choix" & Mid(oField.Name, 5)
oComboBox.ListWidth = 5950
Set oCtrl = oComboBox
Else
Select Case oField.Type
Case dbBoolean
Set oCheckBox = Application.CreateControl(tFormName, acCheckBox)
Set oCtrl = oCheckBox
Case Else
Set oTextBox = Application.CreateControl(tFormName, acTextBox)
Select Case oField.Type
Case dbDouble
oTextBox.TextAlign = 3 'à droite
oTextBox.Format = "#,##0.000000"
Case dbLong
oTextBox.TextAlign = 3 'à droite
oTextBox.Format = "#,##0"
Case dbDate
oTextBox.Format = "yyyy-mm-dd"
End Select
Set oCtrl = oTextBox
End Select
End If
oCtrl.Name = oField.Name
oCtrl.ControlSource = oField.Name
oCtrl.Top = nIRow * 500
oCtrl.Left = 2500
oCtrl.Height = 320
Set oLabel = Application.CreateControl(tFormName, acLabel, , oCtrl.Name)
oLabel.Name = "lab_" & oCtrl.Name
oLabel.Top = oCtrl.Top
oLabel.Height = oCtrl.Height
oLabel.Caption = oCtrl.Name
oLabel.Width = 2000
nIRow = nIRow + 1
NextField:
Next oField
'--- Créer les champs
'=== Change les entêtes
For Each oCtrl In oFrm.Controls
If oCtrl.ControlType = acLabel Then
Set oLabel = oCtrl
Select Case oLabel.Caption
Case "UID": oLabel.Caption = "Id."
Case "txt_Code": oLabel.Caption = "Code"
Case "txt_NomCourt": oLabel.Caption = "Nom Court"
Case "txt_NomCourt_FR": oLabel.Caption = "Nom Court (fr)"
Case "txt_NomCourt_GB": oLabel.Caption = "Nom Court (en)"
Case "txt_NomLong": oLabel.Caption = "Nom Long"
Case "txt_NomLong_FR": oLabel.Caption = "Nom Long (fr)"
Case "txt_NomLong_GB": oLabel.Caption = "Nom Long (en)"
Case "num_Val_Reel": oLabel.Caption = "Val. (Réel)"
Case "num_Val_Entier": oLabel.Caption = "Val. (Entier)"
Case "dat_Val_Date": oLabel.Caption = "Val. (Date)"
Case "bool_Val_VraiFaux": oLabel.Caption = "Val. (Vrai/Faux)"
Case "txt_Val_Texte": oLabel.Caption = "Val. (Texte, max 255 car.)"
Case "txt_Val_Texte_FR": oLabel.Caption = "Val. (Texte (fr), max 255 car.)"
Case "txt_Val_Texte_GB": oLabel.Caption = "Val. (Texte (en), max 255 car.)"
Case "txt_Val_Memo": oLabel.Caption = "Val. (Mémo)"
Case "txt_Val_Memo_FR": oLabel.Caption = "Val. (Mémo (fr))"
Case "txt_Val_Memo_GB": oLabel.Caption = "Val. (Mémo (gb))"
Case "num_Val_ListeValeur": oLabel.Caption = "Val. (Liste Val.)"
Case "dat_DebutValidite": oLabel.Caption = "Dt. Début"
Case "dat_FinValidite": oLabel.Caption = "Dt. Fin"
Case "num_OrdreTriAff": oLabel.Caption = "Ordre Aff."
Case "bool_EstActif": oLabel.Caption = "Actif"
Case "txt_CodeUsager": oLabel.Caption = "Usager"
Case Else
'ne rien faire
End Select
End If
Next oCtrl
'--- Change les entêtes
Call DoCmd.RunCommand(acCmdSelectAll)
Call DoCmd.RunCommand(acCmdStackedLayout)
Call DoCmd.RunCommand(acCmdControlPaddingNarrow)
oFrm.Caption = "Réf. [" & tTableName & "]"
oFrm.Width = 0
oFrm.Section(acDetail).Height = 0
Call DoCmd.Save(acForm, tFormName)
Set oTable = Nothing
oDB.Close: Set oDB = Nothing
Set oFrm = Nothing
Call DoCmd.Close(acForm, tFormName)
Exit_CreateForm:
Exit Sub
End Sub |
Partager