How to update multiple tables with one Update statement in Microsoft Access (using VBA coding and SQL)
<p>Here is my setup: I have made a Parts Order Form the contains combo boxes labeled P1-P10 (part) with resource data taken from my table [Inventory C] showing OEM# (key) and Description. The other combo boxes, containing numbers, are named QO1-QO10 respectively (for quantities ordered). On selecting/typing an entry for the individual combo boxes the code that I have in a test button updates the record that matches the part # in the combo box (P1 for example) and the corresponding selected number from the number box (QO1 for example) and updates the [USED QTY] column of my table to add the selected number the corresponding part. </p>

<pre><code>    Public Sub TestButton_Click()
        Dim SQL As String
        SQL= "UPDATE [INVENTORY C]" &amp; _
             "SET [QTY USED]=[QTY USED]-(QO1.Value)" &amp; _
             "WHERE [OEM#] = P1.Value"
        DoCmd.RunSQL SQL
        End
    End Sub
</code></pre>

<p>This code runs fine, but I have more than  those two combo boxes. Currently I have to redo this same statement nine more times within the same code segment for them all to be updated with a click. During the test after clicking the Test Button, a message will alert you that you are changing a row on your table and to press OK to continue (good!) but it does it ten times (not so good... also there is an error if you press NO so held with an error handler would be appreciated). In the form I have it set up almost like an Excel worksheet, and my users will be using multiple boxes to input information; I will embed the code into a print function so that when they press print, it will also ask them to update all records entered ONE TIME (so that on click it asks: "You are about to Update (10) records. Any changes..."). <strong>How can I code this in VBA, using SQL to UPDATE MULTIPLE RECORDS in the SAME CODE SEGMENT</strong> Can I use a switch or case statement?? This help would be much appreciated.</p>