Strange Print Results

[ASherbuck]

Hello,
I am running Access 07 and I am having a problem with my printing that is just baffling me.

I have a form that is used to view which items have been added to a que to receive a barcode. Here, quantities can be changed regarding how many of each item are without barcodes and items can also be removed.
When the user Clicks a "Print All" control the form feeds the first item in the que into a report, the report grabs the items barcode and prints through our barcode printer.
If the quantity is greater than 1 it repeats for whatever the quantity is, then when the quantity has been satisfied it moves on to a delete query that removes this item from the que.
The form moves through the entire table this way. Everything works fine accept I have an anomaly I can't put my finger on, for some reason it will print out of order and even though I've que'd the same items in the same way multiple times for testing it will sometimes work and then sometimes print out of order. It does not maintain consistency in that the same one is out of order all the time, it changes. The only consistency is that it is only 1 item that is out of order and it always appears as the 2 or 3 barcode printed. Below is the code I use on the form to accomplish the printing.

Dim stDocName as RptBarcode
DoCmd.GoToRecord , "", acFirst
For X = 1 To DCount("*", "tblPrintBarcodes")
If IsNull(Me.Quantity) Then
    MsgBox "There cannot be a quantity less than 1. " _
        & "Enter a quantity and try again."
        Exit Sub
        End If
For i = 1 To Me.Quantity
    DoCmd.OpenReport stDocName, acNormal, , "lItemID=" & Me.lItemID
    Next i
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblPrintBarcodes WHERE [ID]=[Forms]![FrmPrintBarcodes]![ID]"
Requery
Next X

 

[RuralGuy]

You did not include the SQL for your RecordSet but are you aware of the fact that unless you specify the order of the records, Access will return them in the most expedient order. You would need an OrderBy clause in your SQL to get a consistant order to the RecordSet.

 

[ASherbuck]

Hey RG, Thanks for your attention to this thread. I checked my SQL for my recordset and I actually have two order bys, one for item category and one for item name.

SELECT tblPrintBarcodes.lItemID, tblPrintBarcodes.Quantity, tblCatagoryMain.sCatagory, tblItemMain.sItemShort, tblPrintBarcodes.ID
FROM tblCatagoryMain INNER JOIN (tblItemMain INNER JOIN tblPrintBarcodes ON tblItemMain.lItemID = tblPrintBarcodes.lItemID) ON tblCatagoryMain.lCatagoryID = tblItemMain.lCatagoryID
ORDER BY tblCatagoryMain.sCatagory, tblItemMain.sItemShort;

I did notice, oddly, that the table had no order to it, as it was just item ID's and quantities. I found that when I received my single errant barcode in the second or third position that it was located in the table as the second or third record.
So I added to the append query to add the item name and category to two new fields on the table and sort them within the table. No Dice Though =/

 

[RuralGuy]

I'm not sure if this is relevant here but Reports have their own sort order that overrides anything in the RecordSource.