MSAccess VBA AfterUpdate SQL code is running for every record instead of the current record

Question

I have an access database that I will be using to track orders and to track inventory levels. When I attempt to add the parts on my order form (sbfrmOrderDetails) to my inventory table (tblInventory) my VBA code does not execute as planned.

Please note that I have stripped down the code and the tables to just the relevant information/values. The code posted below does work, just not as intended. I explain in much more detail below.

Form Structure

I created my Order form (frmOrder) as a Single Form. This form is referred to in my later code to determine the order number using the txtOrderID control. When I link my subform, the linked master field is OrderID.

Within this form is my Order Details subform (sbfrmOrderDetails) as a continuous form. Every control is bound, and it is linked to the parent form. The linked child field is OrderID.

Photo 1: This photo may better illustrate my form:

Photo 1: This photo may better illustrate my form.

Table Structure

The relevant tables I have are structured like so:

TableName: tblOrders
TableColumns: OrderID

TableName: tblOrderDetails
TableColumns: ID|InvID|Qty|OrderID|DeliveryStatus

TableName: tblInventory
TableColumns: ID|InvID|Qty|OrderID

Intended Action

The action I am trying to take occurs in the subform and is supposed to be isolated to the current record. When the user changes the ComboBox (Combo1 bound control to tblOrderDetails.DeliveryStatus), my VBA code will execute an 'INSERT INTO' SQL string that adds the InvID and the Qty from the current record into the inventory table (tblInventory).

VBA Code for Combo1 AfterUpdate Event (On sbfrmOrderDetails)

Private Sub Combo1_AfterUpdate()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb

If Me.Combo1.Value = "Delivered" Then
   strSQL = "INSERT INTO [tblInventory] ([InvID],[Qty])" _ 
      & "SELECT " & Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtInvID] & " AS InvID," & Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtQty] & " AS Qty " _ 
       & "FROM tblOrderDetails WHERE ((tblOrderDetails.OrderID)=(" & Forms![frmOrder]![txtOrderID] & "));" 

   Debug.Print strSQL
   db.Execute strSQL, dbFailOnError
Else
   'Other event
End If
End Sub

Intended Results

When Combo1 (bound control) is changed from null to “Delivered” on record ID #11 only, it is supposed to add a single new record.

Photo 2: Intended Results:

Photo 2: Intended Results

Actual Results

When Combo1 (bound control) is changed from null to “Delivered” on record ID #11 only, it is adding a new record for every record populated in the subform.

Please refer to Photo 2 above to compare the Intended Results to the Actual Results.

You can see that the quantity from records 12 and 13 are transferred over under the InvID from record 11.

Please refer to Photo 1 to view the sample data and also to Photo 2 above to see the Actual Result of the code.

I suspect that since this is a continuous form that has Parent/Child linking, the form is running the VBA code once for every record (instead of one time for the current record).

Can I alter my VBA code to only run this code once on the current record as is intended? I am hoping this is the best approach to complete this task.

Way too much information. Just show us the query/code where the problem is and give a one paragraph description. If you can't do that, it might mean that you're not ready yet to ask a question here. — Tim Biegeleisen, May 25 '17 at 01:56
My apologies, I am newer to this community and I am still learning the ropes. The primary issue is with the AfterUpdate event posted above. It is working error free, but the issue is that instead of inserting one record into the table as intended, it is inserting multiple records. — ezioassassin2012, May 25 '17 at 02:18
Clearly a lot of effort went into that post, but pretty much every formatting device was misused! If you want a heading, use a heading and not a code snippet. If you want to show code, don't also make it a quote unless it really is _also_ a quote. Don't space everything out vertically with non-breaking spaces, it just takes longer for people to scroll through it. Don't add please-help-me begging in the title. Keep it succinct and free of chat as much as possible. Hope that helps, and welcome to Stack Overflow. — halfer, May 25 '17 at 06:16

score 0 · Answer 1 · answered May 25 '17 at 02:02

0

tblOrders.OrderID --> this table has not been referenced in the statement and vba should throw an error

    strSQL = "INSERT INTO [tblInventory] ([InvID],[Qty])" _ 
          & "SELECT " & Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtInvID] & " AS InvID," & Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtQty] & " AS Qty " _ 
          & "FROM tblOrderDetails WHERE ((tblOrders.OrderID)=
(" & Forms![frmOrder]![txtOrderID] & "));"            
       Debug.Print strSQL

answered May 25 '17 at 02:02

RoMEoMusTDiE

4,739
1
17
26

Thank you maSTAShuFu, I made a mistake porting over that code! Sorry for the confusion. I adjusted my original post to reflect this change. The code runs error free on my test database, and inserts the SQL entry into the table. The primary problem is that it is inserting too many records. – ezioassassin2012 May 25 '17 at 02:15
I suggest to have a button to Insert rows after change the status of the combo box rather than doing it through the change event – RoMEoMusTDiE May 25 '17 at 02:29

score 0 · Accepted Answer · answered May 25 '17 at 08:19

The output of Debug.Print strSQL would have been helpful (see How to debug dynamic SQL in VBA ), but it would be something like this:

INSERT INTO tblInventory (InvID, Qty)
SELECT 14 AS InvID, 2 AS Qty 
FROM tblOrderDetails 
WHERE tblOrderDetails.OrderID = 5

You are inserting two constant values, so you may as well use the INSERT ... VALUES (...) syntax, which by definition only inserts one record:

INSERT INTO tblInventory (InvID, Qty)
VALUES (14, 2)

The reason your statement inserts multiple records is because of WHERE tblOrderDetails.OrderID = 5. Multiple records (all on the subform) satisfy this clause.

You would have to specify the OrderDetails ID instead, to get only one record:

INSERT INTO tblInventory (InvID, Qty)
SELECT 14 AS InvID, 2 AS Qty 
FROM tblOrderDetails 
WHERE tblOrderDetails.ID = <Forms![frmOrder]![sbfrmOrderDetails].Form![txtID]>

Andre, thank you for explaining this so well. I now understand why my code was behaving as it was. I adjusted my code as you suggested and it worked perfectly! I appreciate the link to the how-to on debugging, I will definitely be reading up on this. Also, thank you for adjusting my original post, it is much cleaner. — ezioassassin2012, May 25 '17 at 12:31