Currently writing a report to produce a label based on records in a table.

Got an orders table with a label table linked by the PK, PURCHASE_ORDER and
PART_NUMBER.

Firstly, I produced a form that the user selects the order number and then
the part number for the order. The user then click a print labels button.

When the user clicks the print button this runs a procedure in a module that:

1. Creates the required amount of label records in the labels table
2. Opens the report by using the filter, providing the two values require,
P/O and P/N
3. It then runs a SQL string to update the printed flag for the record

The Problem:

When using the filter method sometimes the report comes up and somethimes
not, I've noticed that when the error occurs I can put the report into desgin
mode and back into report mode and the records are displayed on the report
correctly.

So, after checking the code I decided to link the query and get the
parameters from the forms controls. Still the same problem!

Again, created two new controls and updated them from code with the values
and link the query to the new controls, same problem.

Tried the application on another machine with the same results.

I'm trying to understand what could be going wrong...everything seems
correct, when I debug the filter its correct, when I check the forms controls
values they are correct...

Can anybody give any suggestions on where to look for this type of issue
with the reports?

The Code:

Public Sub CreateLabel_By_Order()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim frm As Form

Dim strPurchaseOrder As String
Dim strPartNumber As String
Dim strSQL As String
Dim strFilter As String

Dim intQty As Integer
Dim idx As Integer

strFilter = ""
strSQL = ""
Set frm = Forms!frm_orderbook_labelling.FRM_ORDERBOOK_LABELLING_DET.Form

With frm
   strPurchaseOrder = !PURCHASE_ORDER
   strPartNumber = !PART_NUMBER
   intQty = !Quantity
End With

Forms!frm_orderbook_labelling.Form.txtPurchaseOrder = strPurchaseOrder
Forms!frm_orderbook_labelling.Form.txtPartNumber = strPartNumber

strFilter = "((PURCHASE_ORDER = '" & strPurchaseOrder & "') And (" & _
             "PART_NUMBER = '" & strPartNumber & "'))"

OpenDatabaseConnection cnn
rst.Open "SELECT PURCHASE_ORDER_ID, PART_NUMBER_ID FROM
TBL_ORDERBOOK_LABELS;", cnn, adOpenDynamic, adLockOptimistic

cnn.BeginTrans

With rst

   DoCmd.SetWarnings (False)

   For idx = 1 To intQty
       .AddNew
       !PURCHASE_ORDER_ID = strPurchaseOrder
       !PART_NUMBER_ID = strPartNumber
       .Update
   Next idx
   
End With

cnn.CommitTrans

rst.Close
cnn.Close

Debug.Print "Filter value: " & strFilter

DoCmd.OpenReport "RPT_ORDERBOOK_PPC_LABEL_TEST", acViewPreview
'Reports!RPT_ORDERBOOK_PPC_LABEL.Filter = strFilter
'Reports!RPT_ORDERBOOK_PPC_LABEL.FilterOn = True

'Debug.Print "Report FilterOn: " & Reports!RPT_ORDERBOOK_PPC_LABEL.FilterOn
'Debug.Print "Report Filter:   " & Reports!RPT_ORDERBOOK_PPC_LABEL.Filter

strSQL = "UPDATE TBL_ORDERBOOK SET TBL_ORDERBOOK.PRINTED = True " & _
        "WHERE (TBL_ORDERBOOK.PURCHASE_ORDER ='" & strPurchaseOrder & "')
And " & _
               "(TBL_ORDERBOOK.PART_NUMBER ='" & strPartNumber & "')"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)

frm.Requery
Forms!frm_orderbook_labelling.Form.cmbPurchaseOrder.Requery

Set frm = Nothing

End Sub

regards

Marcus Lloyd