Report Parameters and Filters

[Marcus Lloyd]

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

 

[Duane Hookom]

It looks like you are setting a filter following the OpenReport method. Have
you tried placing the filter in the OpenReport:
DoCmd.OpenReport "RPT_ORDERBOOK_PPC_LABEL_TEST", acViewPreview, , strFilter

 

[Marcus Lloyd]

Yes, sorry Duane. The code I put in has been amended to work with a parameter
from the query/form.

Since then I've tried creating a new app file, but get the same result no
data in the report.

cheers

Marcus

 

[Duane Hookom]

Why are you adding records to the label table? Is this only for printing
multiple labels? If so, there are easier methods.

Does the record source of RPT_ORDERBOOK_PPC_LABEL_TEST filter out records
where the PRINTER = False?

 

[Marcus Lloyd]

Hi Duane,

The reason for creating unique labels in a table is for tracibility, so when
the label is scanned at our despatch area we can control the shipments. It
also stops two labels of the same value being scanned. We have issues where
operators will scan one label multiple times, thus losing the control of the
history and shipments.

No the report is not filtered by the printed field, only by purchase order
and part number. These are unique fields.

I'm trying to find out whether its a problem with the application thats been
developed or an Access quirk. The only possibility I can think of after many
hours of looking is that I'm referencing two FK's in the label table and not
the LABEL_ID which is the PK, this is generated by an autonumber and
formatted "00000000".

So that is why I tried to set the parameters in reports query from controls
on the form.

cheers

Marcus

 

[Duane Hookom]

For additional trouble-shooting, I would add a breakpoint to the code and
step through it to find out where it has issues. If stepping through the code
works, you may have a timing issue.

 

[Marcus Lloyd]

Hi Duane,

Yes, stepping through the code does work, as I tested this before, and
whilst developing the code it tended to be how the routine was run.

So, about timing issues, how do I get around that? Where do I begin looking?

cheers

Marcus

 

[Marcus Lloyd]

PS:

I've tested the reports with a pause in, I had an old function I wrote for
FTP files to pause to allow time for the file to arrive. So, I started at 10
secs and every report was correct. I'm now testing 5 secs, and again its OK.

So, how do you address timing issues?

 

[Duane Hookom]

I would either break the code into smaller procedures and run them
individually or maybe add code that loops until the count of label records
equals the expected number.