Do While Endless Loop

[cebrower]

I use the following code to print labels to a Dymo label printer. When I step through it with F8 is works perfectly. When I click the command button to print the labels it prints an endless number of labels for the first record in the record set. Any ideas why this happens?

DoCmd.OpenForm "frmLabelHalf"
If Forms!frmLabelHalf.Recordset.EOF Then
MsgBox ("There are no Std-Half WallTers on this order.")
DoCmd.Close acForm, "frmLabelHalf"


Exit Sub
End If


Dim NumLabels As Integer


Do While IsNull(Forms!frmLabelHalf!Description) = False
NumLabels = Forms!frmLabelHalf!QtyOrdered
Do While NumLabels > 0
DoCmd.OpenReport "rptLabelHalf"
NumLabels = NumLabels - 1
Loop
On Error Resume Next
DoCmd.GoToRecord , , acNext
Loop


DoCmd.Close acForm, "frmLabelHalf"

[Welshgasman]

Well the number appears to come from the form record?
I would have thought you would also need to close the report each time?

[cebrower]

This is the report in design view. It prints the data in 2 fields from the first record. It prints the number of labels shown in the "QtyOrdered" field on the form, then the form moves to the next record and prints again, etc. until it has moved past the last record.

[davegri]

Double post. See next post.

[davegri]

While debugging, On Error Resume Next will hide errors. Comment that out and see what happens.
Docmd.gotorecorrd,,acNext is obviously failing and you stay at the first record forever.

[Micron]

You don't specify the object type in the GoTo so maybe when you're stepping through it's the form (the code window can make the object related to the code the active object) whereas when you just run it, it's the report that's the active object.
EDIT - forgot to mention that this sort of thing is better done with a recordset IMO. Then you don't have to keep hitting an object (the rs would be faster, albeit perhaps not so much that you'll notice), nor do you need to worry about which window/object is active.

[cebrower]

I removed On Error Resume Next and now I get run-time error 2105 You can't go to the specified record. I've tried it with the form in form view and in datasheet view. I also changed the query to a make table query and used the resulting table as the form's record source. I KNOW there is another record. I've used this same code with variations in other places with no issues.

[moke123]

Just noticed Microns edit as I was about to post. I also wondered why you were moving through the form rather than the forms recordset.

I would try something along the lines of

    Dim rs As DAO.Recordset
    Dim i As Integer


    Set rs = Forms("frmLabelHalf").Recordset


    If rs.BOF And rs.EOF Then MsgBox "There are no Std-Half WallTers on this order.": GoTo MyExit


    rs.MoveFirst


    Do Until rs.EOF


        For i = 1 To rs.QtyOrdered
            DoCmd.OpenReport "rptLabelHalf"   'where clause?
        Next i


        rs.MoveNext
    Loop


MyExit:


    Set rs = Nothing


    DoCmd.Close acForm, "frmLabelHalf"

[Welshgasman]

I've used this same code with variations in other places with no issues.

As soon as you change something, you can introduce an error. The number of times I've read 'but it works elsewhere'
Yes, but not as you have it now, or in the same circumstances.

[cebrower]

Thanks moke123. That worked.

[cebrower]

Oops, not quite solved. Using the code from moke123 when I try to compile it stops on the line "For i = 1 To rs.QtyOrdered" and I get the error message "Method or data member not found".

[davegri]

if QtyOrdered is a field in the recordset, then it should be

For i = 1 to rs!QtyOrdered

Notice bang instead of pop.

[cebrower]

Yup, that fixed it.

[moke123]

Oops. Aircode