DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [TODAY-DIET];"
DoCmd.RunSQL "DELETE * FROM [TODAY-DIET SUB];"
aSQL = " INSERT INTO [TODAY-DIET SUB] ( DIETID, FOODID, GROUP_AMOUNT, UNITID, SORT, TOTE, FOOD, UNIT, LINENOTES, [TABLEID], MEAT, TARGETTABLE, DC_SORTORDER ) " & _
" SELECT DISTINCT [DIET TABLE].DIETID, [DIET SUB TABLE].FOODID, [DIET SUB TABLE].GROUP_AMOUNT, [DIET SUB TABLE].UNITID, [DIET SUB TABLE].SORT, [DIET SUB TABLE].TOTE, [FOOD TABLE].FOOD, [UNIT TABLE].UNIT, [DIET SUB TABLE].LINENOTES, [DIET TABLE].TABLEID, [FOOD TABLE].MEAT, IIf([MEAT]=-1,1,[TABLEID]) AS Target, [DELIVERY CONTAINER TABLE].SortOrder " & _
" FROM (([LOCATION TABLE] RIGHT JOIN ((([DIET TABLE] LEFT JOIN [DIET SUB TABLE] ON [DIET TABLE].DIETID = [DIET SUB TABLE].DIETID) LEFT JOIN [SPECIES TABLE] ON [DIET TABLE].SPECIESID = [SPECIES TABLE].SPECIESID) LEFT JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID) ON [LOCATION TABLE].LOCATIONID = [DELIVERY CONTAINER TABLE].LocationID) LEFT JOIN [FOOD TABLE] ON [DIET SUB TABLE].FOODID = [FOOD TABLE].FOODID) LEFT JOIN [UNIT TABLE] ON [DIET SUB TABLE].UNITID = [UNIT TABLE].UNITID " & _
" WHERE ((([DIET SUB TABLE].LINENOTES) Not Like ""*ZK*"") AND (([DIET SUB TABLE]." & myDay & ")=True) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET TABLE].GroupID)=1) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) OR ((([DIET SUB TABLE].LINENOTES) Is Null) AND (([DIET SUB TABLE]." & myDay & ")=True) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET TABLE].GroupID)=1) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1));"
DoCmd.RunSQL aSQL
'Debug.Print aSQL
Forms!TouchScreenWelcome.Prog1.Visible = True 'shows a progress bar
DoEvents 'necessary to ensure the progress bar is visible
bSQL = " INSERT INTO [TODAY-DIET SUB] ( DIETID, FOOD, FOODID, UNIT, UNITID, SORT, TOTE, GROUP_AMOUNT, TARGETTABLE, DC_SORTORDER, LINENOTES ) " & _
" SELECT DISTINCT [SUBENCLOSURE TABLE].GroupID, [FOOD TABLE].FOOD, [DIET SUB TABLE].FOODID, [UNIT TABLE].UNIT, [DIET SUB TABLE].UNITID, Min([DIET SUB TABLE].SORT) AS MinOfSORT, Min([DIET SUB TABLE].TOTE) AS MinOfTOTE, Sum([DIET SUB TABLE].GROUP_AMOUNT) AS SumOfGROUP_AMOUNT, IIf([MEAT]=-1,1,[TABLEID]) AS Target, [DELIVERY CONTAINER TABLE].SortOrder, [DIET SUB TABLE].LINENOTES " & _
" FROM ((((([DIET TABLE] LEFT JOIN [DIET SUB TABLE] ON [DIET TABLE].DIETID = [DIET SUB TABLE].DIETID) LEFT JOIN [SUBENCLOSURE TABLE] ON [DIET TABLE].GroupID = [SUBENCLOSURE TABLE].SE_ID) LEFT JOIN [LOCATION TABLE] ON [SUBENCLOSURE TABLE].LocationID = [LOCATION TABLE].LOCATIONID) LEFT JOIN [FOOD TABLE] ON [DIET SUB TABLE].FOODID = [FOOD TABLE].FOODID) LEFT JOIN [UNIT TABLE] ON [DIET SUB TABLE].UNITID = [UNIT TABLE].UNITID) INNER JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID " & _
" WHERE ((([DIET SUB TABLE].LINENOTES) Not Like ""*ZK*"") AND (([DIET TABLE].GroupID)<>1) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET SUB TABLE]." & myDay & ")=True) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) OR ((([DIET SUB TABLE].LINENOTES) Is Null) AND (([DIET TABLE].GroupID)<>1) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET SUB TABLE]." & myDay & ")=True) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) " & _
" GROUP BY [SUBENCLOSURE TABLE].GroupID, [FOOD TABLE].FOOD, [DIET SUB TABLE].FOODID, [UNIT TABLE].UNIT, [DIET SUB TABLE].UNITID, IIf([MEAT]=-1,1,[TABLEID]), [DELIVERY CONTAINER TABLE].SortOrder, [DIET SUB TABLE].LINENOTES, [DIET TABLE].GroupID, [DIET TABLE].CURRENT, [DIET TABLE].LABEL, [DIET TABLE].NCPREPARES, [DIET SUB TABLE]." & myDay & ";"
DoCmd.RunSQL bSQL
Forms!TouchScreenWelcome.Prog2.Visible = True
DoEvents
cSQL = " INSERT INTO [TODAY-DIET] ( DataDate, DIETID, SPECIES, DCID, [TABLEID], NOTEID, LABEL, DC, LOCATION, SortOrder, GroupID, IMAGELINK ) " & _
" SELECT Nz([Forms]![TouchScreenWelcome].[LabelDate]) AS DataDate, [TODAY-DIET SUB].DIETID, [SPECIES TABLE].SPECIES, [DIET TABLE].DCID, [TODAY-DIET SUB].TARGETTABLE, [DIET TABLE].NOTEID, [DIET TABLE].LABEL, [DELIVERY CONTAINER TABLE].DC, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].DC_SORTORDER, [DIET TABLE].GroupID, [SPECIES TABLE].IMAGELINK " & _
" FROM ((([TODAY-DIET SUB] INNER JOIN [DIET TABLE] ON [TODAY-DIET SUB].DIETID = [DIET TABLE].DIETID) INNER JOIN [SPECIES TABLE] ON [DIET TABLE].SPECIESID = [SPECIES TABLE].SPECIESID) INNER JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID) INNER JOIN [LOCATION TABLE] ON [DELIVERY CONTAINER TABLE].LocationID = [LOCATION TABLE].LOCATIONID " & _
" GROUP BY [TODAY-DIET SUB].DIETID, [SPECIES TABLE].SPECIES, [DIET TABLE].DCID, [TODAY-DIET SUB].TARGETTABLE, [DIET TABLE].NOTEID, [DIET TABLE].LABEL, [DELIVERY CONTAINER TABLE].DC, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].DC_SORTORDER, [DIET TABLE].GroupID, [SPECIES TABLE].IMAGELINK;"
DoCmd.RunSQL cSQL
Forms!TouchScreenWelcome.Prog3.Visible = True
DoEvents
dSQL = " INSERT INTO [TODAY-DIET] ( DIETID, DC, SPECIES, NOTEID, LOCATION, [TABLEID], SortOrder, DataDate, GroupID, IMAGELINK, LABEL ) " & _
" SELECT [TODAY-DIET SUB].DIETID, [SUBENCLOSURE TABLE].Subenclosure AS DC, ""GROUP DIET"" AS SPECIES, ""Group Diet"" AS NOTEID, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].TARGETTABLE, [TODAY-DIET SUB].DC_SORTORDER, Nz([Forms]![TouchScreenWelcome].[LabelDate]) AS DataDate, [SUBENCLOSURE TABLE].GroupID, ""group.jpg"" AS File, True AS LABELS " & _
" FROM [TODAY-DIET SUB] INNER JOIN ([LOCATION TABLE] INNER JOIN [SUBENCLOSURE TABLE] ON [LOCATION TABLE].LOCATIONID = [SUBENCLOSURE TABLE].LocationID) ON [TODAY-DIET SUB].DIETID = [SUBENCLOSURE TABLE].GroupID " & _
" GROUP BY [TODAY-DIET SUB].DIETID, [SUBENCLOSURE TABLE].Subenclosure, ""GROUP DIET"", ""Group Diet"", [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].TARGETTABLE, [TODAY-DIET SUB].DC_SORTORDER, [SUBENCLOSURE TABLE].GroupID, ""group.jpg"", True;"
DoCmd.RunSQL dSQL
Forms!TouchScreenWelcome.Prog4.Visible = True
DoEvents
DoCmd.SetWarnings True