Update Reel Length (1 Viewer)

[tmyers]

I am trying to fix a query meant to update reel lengths on my main table after the user clicks the print label button. This particular process deals with a sub-form within a sub-form and is giving me a headache.

The first sub-form has parent/child links to the main form and the second sub-form is linked to the first sub-form with parent/child. The numeric fields below the entries are just temporary so I can see the ID's while working on the query.

Sub-form #1 links:

Sub-form #2 links:

The query I am trying to use to update my table is:

UPDATE tblWireRoom, tblColorWire INNER JOIN tblCutColorWire ON tblColorWire.CutID = tblCutColorWire.ColorWireID SET tblWireRoom.CurrentLength = [tblWireRoom].[CurrentLength]-[tblCutColorWire].[CutLength]
WHERE (((tblWireRoom.ReelID)=[Forms]![frmWireRoom]![WireColorsSubCntrl].[Form]![WireColorLengthSubFrm]![WireReelID]) AND ((tblCutColorWire.ColorWireID)=[Forms]![frmWireRoom]![WireColorsSubCntrl].[Form]![WireColorLengthSubFrm]![ColorWireID]));

The above query only updates a single record from the second sub-form however and is dependent on what record in that form had focus at the time the button is pressed. I have 3 similar queries in other parts of this app and they all work fine except this one has the additional nested sub-form and I believe that is what is causing me problems.

The end result should be the table below gets the 3 reels in question updated with current length after deducting the cut. Which in this particular example, the current length of all 3 of those in the snip should be zero. However after this runs, only one of those entries will update based on whatever record in the sub-form had focus.

Can someone offer insight as to what I doing wrong/missing? This seems like it should be so simple but I haven't been able to figure it out after messing with it most of yesterday and all morning.

 

[arnelgp]

you can't have a multiple table on your update query.:

UPDATE tblWireRoom, tblColorWire ...

...
And why are you using SQL update when the 3 records are Already available for edit through the form.
you might got Error when doing the Update through SQL.

 

[tmyers]

The many-to-many table is what is causing the problem?

On the form, they are selecting a reel that they are going to cut wire from. So they select X reel with Y footage and when they print the label, it needs to deduct the length from that reel.

The overall process is:
- The user select the reel they are going to use
- They click print to print out the label
- The app logs the transaction to a log table (ID of product, ID of the reel that was used, starting length, cut length and end length)
- The reels that were used are updated to reflect their new total length

 

[tmyers]

I wasn't able to figure out how to do it via a query and gave up. I managed to solve my problem however by doing it via code and DAO. In all my testing, it provided the results I expected.

ID = [Forms]![frmWireRoom]![WireColorsSubCntrl].[Form]![WireColorLengthSubFrm]![ColorWireID]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblCutColorWire WHERE [ColorWireID] = " & ID & "")
With rs
    Do While Not .EOF
        r = ![WireReelID]
        c = ![CutLength]
        Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM tblWireRoom WHERE [ReelID] = " & r & "")
            With rs2
                If ![ReelID] = r Then
                    rs2.Edit
                    sCut = ![CurrentLength]
                    rs2![CurrentLength] = sCut - c
                    rs2.Update
                End If
             End With
    .MoveNext
    Loop
End With