Printing Batch - Test Db from Allen Browne

[richard]

Allen

I have used the database you suggested the other day to save and print a
batch at a later date
I am having a problem with the strwhere part of the print batch code in the
database.
My table can have multiple items within the same batch so as part of the
list box I have used a duplicates query to hide any multiples. I am now
struggling to get the BatchNumber to be used in the selection part of the
code for PrintBatch_click.
The SQL for the query on the list box is below

SELECT First(tblnapswork.BatchNumber) AS BatchNumberField,
First(tblnapswork.BatchDate) AS BatchDateField, Count(tblnapswork.BatchDate)
AS NumberOfDups, Count(tblnapswork.BatchNumber) AS CountOfBatchNumber
FROM tblnapswork
GROUP BY tblnapswork.BatchDate, tblnapswork.BatchNumber
HAVING (((Count(tblnapswork.BatchDate))>1) AND
((Count(tblnapswork.BatchNumber))>1))
ORDER BY First(tblnapswork.BatchNumber) DESC;

The code for running the report based on the batch is below

Private Sub cmdPrintBatch_Click()
On Error GoTo Err_handler
Dim strwhere As String
Const strcDoc = "rptDataSheetBatchReprint"

If IsNull(Me.List15) Then
MsgBox "Select a batch to print."
Else
If CurrentProject.AllReports(strcDoc).IsLoaded Then
DoCmd.Close acReport, strcDoc
End If
strwhere = "BatchNumber=" & Me.List15
DoCmd.OpenReport strcDoc, acViewPreview, , strwhere
End If
Exit_handler:
Exit Sub
Err_handler:
MsgBox "Error " & Err.Number & ":" & Err.description, vbExclamation,
".cmdPrintBatch_click"
Resume Exit_handler
End Sub

any help would be appreciated

 

[Allen Browne]

Presumably we are talking about the sample database from this article:
http://allenbrowne.com/ser-72.html

If so, you will see that it has a tblBatch that keeps track of the batch. If
you follow this example, the query will be from tblBatch.

Your example groups by BatchDate and BatchNumber, so the First() on the
first 2 fields of your query is not needed.

You also don't way what error you are getting. For example, we don't know if
anything is actually selected in the list box.

For debugging purposes, comment out the error handler so you can see the
error, i.e.:
'On Error GoTo Err_handler
Then add the line:
Debug.Print strWhere
When it fails, open the Immediate Window (Ctrl+G) and see what came out.
Hopefully you can see what is wrong.

 

[richard]

OK problem is that report will not show any data in the report when run.
Everything else appears to be working fine and I am getting no error messages

Structure is as follows

tblbatch - as per ReportPrint
tblnapswork - holds batchnumbers against records and other fields required
in report
tbltable1 - holds other fields required in report
qryDataSheetsBatchReprint - pulls together data from above three tables.

What I believe is not happening is I am not specifying the BatchNumber for
the qryDataSheetsBatchReprint to select the appropriate records.

Any thoughts would be gratefully received

 

[Allen Browne]

Suggestions to help you debug:

1. Do you have the batch number field in the query?

2. After you open the report (showing no records), open the Immediate Window
(Ctrl+G), and enter:
? Reports!rptDataSheetBatchReprint.Filter
Does it show the correct thing?

3. Create a query using qryDataSheetsBatchReprint as an input "table." Enter
a real batch number in the Criteria row under the BatchNumber field. Does it
return what you expect? If so, switch the query to SQL View (View menu), and
look at the WHERE clause. Does it match the Filter printed at Step 2 above?