Help please! - Updating table contents with another tables contents

[ham355]

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

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

[Robeen]

The problem is that your code is not looping through the records in your QLXForecastsToOutput table.
One option would be for you to:
1. Research using a 'Recordset' object [Dim rs As Recordset . . .] and then
2. 'Loop' through the rows of data using the Recordset and THEN
3. Within the Loop - add logic like the If Then Else statement you have in there already to Update/Insert - depending on values that each record returns.

[Robeen]

Here's a simple example of using a recordset to loop through records in a table and then 'do something'.
In the 'do something' portion, I have an If statement that just displays a value from the current record using a message box. You would put your logic in there instead.
I'm already using strSQL to read the first Table - you can use strSQL2 & strSQL3 to build your Insert and Update strings.

Function Get_DB_Values()
'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
' . . . just for testing purposes.
' . . . You should use your actual Table & field names & set your variables to match the data types of your table fields.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim  strSQL, strSQL2, strSQL3, strField1 As String
Dim intField2 as Integer
On Error GoTo Error_Handle
   
Set db = CurrentDb
strSQL = "Select * From [Table1]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF

'This gets the values from the current record into variables.
strField1 = rs![Field1]
intField2 = rs![Field2]

'I put this little If Then here so you can look at one value.
'substitute this with your own logic.
If intField2 = 5 Then
    'MsgBox strBranch
End If
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
        
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I hope this helps! All the best! Let us know if you have questions

[ham355]

Thank you Robeen, spot on! I have constructed a simple loop and it works! Thanks again, I appreciate it!

[Robeen]

Happy to help. All the best!

[Robeen]

P.S. Mark this thread as solved if it has indeed solved your problem!