No data in report (Access 2k)

Topic

New Reply

I have a VBA module, which does several things.

One of its tasks is to cycle through several reports, printing them. I’m trying to find a way to not print them if there is no data.

I know how to use the “OnNoData” Event on the report, but if I use that, it stops the module from running, cancelling all the other subsequent actions.

Presumably, what I need is some form of “IF” statement in the vba module, but I can’t think how to do it.

Any suggestions

Thanks

Reply | Quote

Replies

One way I handle no data reports is using the nodata event to print a different report but one that has a text box on it indicating that there was no data for this report. I don’t know if that would help but it will keep the printer going I think.

Reply | Quote

I’m not sure if that’ll work. I was hoping for a solution rather than a work-round.

I’ll wait till I hear from anyone else before I try this.

Reply | Quote

What do you mean “it stops the module from running, cancelling all the other subsequent actions”? The reports NotData event doesn’t stop code. Since you didn’t print the code that tries to loop through the reports, there isn’t any way to guess at what’s wrong with it.

Reply | Quote

Sorry, I didn’t explain the problem fully. I used the EventCancel to halt printing with OnNoData. However this also halts the running of the VBA module that calls the report in the first place (see below).

Hope this makes a bit more sense.

Option Compare Database

Function SendLetters()
On Error GoTo SendLetters_Err

Dim mydb As Database, myrec As Recordset
Set mydb = CurrentDb

‘**********************************
‘open letter to new member
‘**********************************

Dim strMessage As String
DoCmd.OpenReport “rptLetNewMember”, acNormal, “”, “”

strMessage = “Have all the Customer letters printed OK?”
Response = MsgBox(strMessage, vbYesNo)
If Response = vbNo Then
MsgBox “Select pages to re-print”
DoCmd.OpenReport “rptLetNewMember”, acViewPreview
Else
End If

‘**********************************
‘open letter to bank to lodge any standing orders
‘**********************************

DoCmd.OpenReport “rptLetLodgeSO”, acNormal, “”, “”
strMessage = “Have all the Bank letters printed OK?”
Response = MsgBox(strMessage, vbYesNo)
If Response = vbNo Then
MsgBox “Select pages to re-print”
DoCmd.OpenReport “rptLetLodgeSO”, acViewPreview
Else
End If

‘****************************************************************
‘Update Letters_Sent Indicator
‘****************************************************************

Set myrec = mydb.OpenRecordset(“Select tblPeople.Letters_Sent From tblpeople WHERE (((tblPeople.Letters_Sent)is null))”)

With myrec
Do While Not .EOF
.Edit
!Letters_Sent = “Y”
.Update
.MoveNext
Loop
End With

SendLetters_Exit:
Exit Function

SendLetters_Err:
MsgBox Error$
Resume SendLetters_Exit

End Function

Reply | Quote

EventCancel? Do you mean you set the Cancel argument of the NotInList event of the report to True to cancel printing? That has no effect on the code that called the the report except to raise an error that you have to trap and ignore. Since you haven’t implemented any error handling in your routine, however, the error returned by the NotInList event cancelling the print job *will* abort your code because there is no handler to deal with the “error”.

You’re probably getting a 2501 error, which should simply be trapped and followed by a resume next command to move to the next line after the openreport in your calling routine.

Reply | Quote

That’s exactly what I’m getting, (although I didn’t have a clue that it was a 2501 error).

On resume next has worked however.

Thanks for the help

Reply | Quote