VBA running query for continuous form

[tmyers]

I am not sure if this would belong in the VBA, Query or Forms sub-forum so if I got it wrong sorry!

I have run into a problem in my wire cutting program where footage is being deducted incorrectly. Below is a snip of the sub-form.

The sub-form is a continuous form to display all cuts for this particular type of wire for this order. When the wire guy selects which reel he is going to cut it from (the combo box), he typically selects all the lengths at once and then prints each one. However it appears when that is done, the module that handles the printing of the labels deducts the footage for each line every time the print button is click rather than just that particular line.

I assume this a due to the nature of a continuous form in that all controls shown are technically the same control. Should I have used a different kind of form to display this? Continuous is what made the most sense but is proving to be a troubleshooting nightmare. Below is the code for the print button for reference. Currently several lines are commented out for testing so I am not printing needlessly and opening up the report when it isn't needed.

Private Sub PrintLabelBtn_Click()

Dim i As Integer
Dim x As Integer

'if flagged complete ask if reprint is needed
If Me.SingleComplete = True Then
    If MsgBox("Print new label?", vbYesNo, "Reprint Label") = vbYes Then
        'ask if reels need updated
        If MsgBox("Do you want to deduct footage from choosen reel?", vbYesNo) = vbYes Then
            If Me.SingleCmb = 0 Then
                MsgBox "No reels selected. Please select a reel"
                Exit Sub
            End If
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "QryLogSingleCut"
                DoCmd.OpenQuery "QryUpdateCutLogLengthSingles"
                DoCmd.OpenQuery "UpdateWireRmSingle"
                DoCmd.SetWarnings True
            GoTo LinePrint
        End If
        'if reels do not need updated, jump to print
        GoTo LinePrint
    Else
        If MsgBox("Do you want to deduct footage from choosen reel?", vbYesNo) = vbYes Then
            If Me.SingleCmb = 0 Then
                MsgBox "No reels selected. Please select a reel"
                Exit Sub
            End If
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "QryLogSingleCut"
                DoCmd.OpenQuery "QryUpdateCutLogLengthSingles"
                DoCmd.OpenQuery "UpdateWireRmSingle"
                DoCmd.SetWarnings True
            Exit Sub
        Else
            'user chose not to reprint label and not update wire, exit sub
            Exit Sub
        End If
        Exit Sub
    End If
End If


If Me.SingleCmb = 0 Then
    'do nothing to prevent blank logs
   If MsgBox("No wire has been selected. Please cancel if done in error.", vbOKCancel, "Select Wire") = vbCancel Then
        MsgBox "Operation cancelled", vbOKOnly
        Exit Sub
    End If
Else

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QryLogSingleCut"
    DoCmd.OpenQuery "QryUpdateCutLogLengthSingles"
    DoCmd.OpenQuery "UpdateWireRmSingle"
    DoCmd.SetWarnings True
End If


LinePrint:
If Me.CutNum >= 1 Then
    If Me.CutNum > 1 And MsgBox("Do you want to print multiples of this label?", vbYesNo) = vbYes Then
        i = CInt(InputBox("Please enter the number of copies"))
        For x = 1 To i Step 1
            'DoCmd.OpenReport "rptSingleCutLbl", acViewPreview, , "SingleID=" & Me.SingleID
            'Reports!rptSingleCutLbl.Controls!FirstNumTxt.Value = x
            'Reports!rptSingleCutLbl.Controls!LastNumTxt.Value = i
            'DoCmd.PrintOut
        Next x
        'DoCmd.Close acReport, "rptSingleCutLbl"
    Else
        'DoCmd.OpenReport "rptSingleCutLbl", acViewPreview, , "SingleID=" & Me.SingleID
        'Reports!rptSingleCutLbl.Controls!FirstNumTxt.Value = 1
        'Reports!rptSingleCutLbl.Controls!LastNumTxt.Value = 1
        'DoCmd.PrintOut
        'DoCmd.Close acReport, "rptSingleCutLbl"
    End If
End If
   
Forms!frmWireRoom.SetFocus

Me.SingleComplete = True
Me.Requery
Me.Refresh

End Sub

 

[MajP]

I do not think there should be any code to do this. I do not know what your tables are But I would expect when I get an item in stock I store the Roll ID and Roll length
Lets say the above roll Is ID 1 with Length of 1000Ft
Then each time I select and print a label it marks it as complete. I should be able to dynamically calculate
Roll length minus sum of child record cuts. Assuming the above subform has a foreign key to ID 1.

 

[tmyers]

Attached is a snip of my tables and structure for reference if that helps Majp.
I separated all the different types like this due to how I was initially asked to keep track of everything and I found it easier to build this way. I know it likely isnt the most correct way to do it but dont know how else to manage it.

EDIT:
But yes you are correct. When a reel is added, it gets assigned a ReelID and the length is entered. When this process runs, it uses the ReelID and deducts the cut length from the overall length, logs the cut in a log table, updates the length accordingly then prints out a label for the cut.

 

[Pat Hartman]

You have three tables rather than one to record cuts. This will cause many problems and make the whole process more complicated.

I assume this a due to the nature of a continuous form in that all controls shown are technically the same control. Should I have used a different kind of form to display this?

Continuous forms do not work differently from single forms. EVERY form type is bound to a SINGLE record at a time. Code within the form refers to ONLY the current row.

You didn't show us your queries. It is is almost certainly one of the queries that is causing the duplication. You are probably selecting all rows in the form rather than just the row you are working on.

You said you are selecting the length immediately but printing as a group. This sounds like a design flaw and that might be what is causing the problem. When you refer to a record on ANY form, not just a continuous form, you ALWAYS reference the current record ONLY so if the current record is not what you think it is, you will get invalid results.

Where SomeID = Forms!yourmainform!yoursubform.Form!yourID === always equals the current record. So if you were to do the printing later, this would not work because the record you think is current would no longer be the current record.

 

[June7]

This is stock/inventory control. Enter transactions and calculate balances when needed. Review http://allenbrowne.com/AppInventory.html

 

[tmyers]

Here are the queries involved (in the order they run). The first two just log the cut in tblCutLog:
Write the cut to the cut log table:

INSERT INTO tblCutLog ( CutReelID, StartingLength, CutLength, TicketID )
SELECT tblSingleCut.WireReelID, tblWireRoom.CurrentLength, [CutLength]*[CutNum] AS TotalCutLength, tblSingleCut.TicketID
FROM tblSingleCut INNER JOIN tblWireRoom ON tblSingleCut.WireReelID = tblWireRoom.ReelID
WHERE (((tblSingleCut.WireReelID)=[Forms]![frmWireRoom]![frmWireSingle].[Form]![WireReelID]) AND ((tblSingleCut.TicketID)=[forms]![frmWireRoom]![TicketID]));

Do the math in the log table:

UPDATE tblCutLog SET tblCutLog.EndLength = [StartingLength]-[CutLength]
WHERE (((tblCutLog.CutReelID)=[Forms]![frmWireRoom]![frmWireSingle].[Form]![WireReelID]));

Update the wire room table to update the wire reel that was used:

UPDATE tblSingleCut INNER JOIN tblWireRoom ON tblSingleCut.WireReelID = tblWireRoom.ReelID SET tblWireRoom.CurrentLength = [tblWireRoom].[CurrentLength]-([tblSingleCut].[CutLength]*[tblSingleCut].[CutNum])
WHERE (((tblWireRoom.ReelID)=[Forms]![frmWireRoom]![frmWireSingle].[Form]![WireReelID]) AND ((tblSingleCut.TicketID)=[Forms]![frmWireRoom]![TicketID]));

To be honest, I will likely have to share the whole app for review. It is likely a hot mess that could have been better but I wasnt sure how to do it better.

EDIT:
Pat, I think you are more than likely correct in that when the print button is being clicked, the current record isn't what I think it is. How could I make sure that when dealing with a continuous form, when the button is clicked it is on the record it needs to be on? ie on the same row in which it was clicked.

 

[tmyers]

I believe this thread is relevant to the problem I am experiencing:

Continuous form - current vs selected record on cmd click

Hi, in my continuous form i have three images each with onClick events. when user presses one of these images, the code is executed for the 'current' record, not necessarily the one that the user clicked on. How can i make access acknowledge the record that the user wants? I have tried setting...

www.access-programmers.co.uk

Now that you have mentioned it, it makes sense that what I am seeing as maybe duplication is actually the process just being run on a different record then I think it is due to the current record not matching the row in which the button is being clicked. I will dig into it more.

EDIT:
This makes even more sense to be the problem as previously I had removed the record when it was printed by flagging it complete and it being "Dropped" during the requery but was asked to keep the records visible even after being printed and that is about when the problem started to occur. Granted no one told me the problem was happening for nearly a month.

 

[Pat Hartman]

Either print one record at a time or select items to be printed as a group. Grouping is a problem in a multi-user application. You will conflict with each other unless the grouping takes multiple users into consideration.

Why can't you print when you select?

 

[tmyers]

Either print one record at a time or select items to be printed as a group. Grouping is a problem in a multi-user application. You will conflict with each other unless the grouping takes multiple users into consideration.

Why can't you print when you select?

Mostly due to the nature of the process. They might select a reel only to later find the length was off and had to select another one. That is the main reason I separated printing from the act of selecting a reel of wire.

After brainstorming over the weekend, I think I may have found a fix to my problem and just need to adjust my queries with one additional criteria. Will report back once I am able to get to it and try it out.