Hi All, I have written the below (excuse it's haphazardness). It's basically a button click which takes a tables contents, and updates another table with those contents, or creates them, based on the records existence.
However, it's only actioning one record, the record whose results are displayed on the form on which the button sits (until you move to the next record and click the button again)
What am I doing wrong please!? I would like to update/add all records from the TempPlanDataMultiple table at once!
Thanks, Ian
Code:
Private Sub Command31_Click()
Dim dbs As Database
Set dbs = CurrentDb
DoCmd.SetWarnings False
If DCount("[GLCODE]", "QLXForecastsToOutput", "GLCODE='" & F6.Value & "'") > 0 Then
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.1 = TempPlanDataMultiple.Month1 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.2 = TempPlanDataMultiple.Month2 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.3 = TempPlanDataMultiple.Month3 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.4 = TempPlanDataMultiple.Month4 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.5 = TempPlanDataMultiple.Month5 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.6 = TempPlanDataMultiple.Month6 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.7 = TempPlanDataMultiple.Month7 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.8 = TempPlanDataMultiple.Month8 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.9 = TempPlanDataMultiple.Month9 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.10 = TempPlanDataMultiple.Month10 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.11 = TempPlanDataMultiple.Month11 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.12 = TempPlanDataMultiple.Month12 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
DoCmd.RunSQL "UPDATE QLXForecastsToOutput SET QLXForecastsToOutput.`Grand Total` = TempPlanDataMultiple.Expr2 WHERE QLXForecastsToOutput.GLCODE = TempPlanDataMultiple.F6"
Else
DoCmd.RunSQL " INSERT INTO QLXForecastsToOutput (GLCODE,1,2,3,4,5,6,7,8,9,10,11,12,`Grand Total`) VALUES ('" & F6.Value & "','" & Month1.Value & "','" & Month2.Value & "','" & Month3.Value & "','" & Month4.Value & "','" & Month5.Value & "','" & Month6.Value & "','" & Month7.Value & "','" & Month8.Value & "','" & Month9.Value & "','" & Month10.Value & "','" & Month11.Value & "','" & Month12.Value & "','" & Expr2.Value & "')"
End If
MsgBox "Forecast profile update complete", vbInformation, "Forecast Updated"
DoCmd.SetWarnings True
DoCmd.Close
End Sub