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
| Option Compare Database
Option Explicit
Private Sub ChoixSemaine_AfterUpdate()
Dim oRst As Recordset
Dim i As Integer
DoCmd.SetWarnings False
'Vidanger TPlatsOfferts
DoCmd.RunSQL "DELETE tPlatsOffertsPK FROM tPlatsOfferts;"
'Créer un recordset de tPrepaMenus pour ce lundi
Set oRst = CurrentDb.OpenRecordset("SELECT * FROM tPrepaMenus WHERE Lundi=#" & Format(Me.ChoixSemaine, "mm/dd/yyyy") & "#;", dbOpenDynaset)
'N.B. 16 colonnes, la 5e : "Entree", la 6e "Plat1", etc.
'Rappel : les index commencent à 0 !
'Peupler TPlatsOfferts
Do While Not oRst.EOF
For i = 4 To 15
If Not IsNull(oRst(i)) Then _
DoCmd.RunSQL ("INSERT INTO tPlatsOfferts ( tPlatsFK ) SELECT " & oRst(i) & " AS Expr1;")
Next i
oRst.MoveNext
Loop
'Affecter la source au formulaire
Me.Section("détail").Visible = True
Me.RecordSource = "SELECT tPlats.tPlatsPK, tPlats.Plat, tPlatsOfferts.NbrePlats FROM tPlats INNER JOIN tPlatsOfferts ON tPlats.tPlatsPK = tPlatsOfferts.tPlatsFK ORDER BY tPlats.Plat;"
'Sortir proprement
DoCmd.SetWarnings True
oRst.Close
Set oRst = Nothing
End Sub |