Need help with a MsgBox

[mondoray]

Hi,

I have a report which displays employee details and his/her training history. The training history report section is controlled by a subreport and is linked to the employee ID.

The employee report production (Print Preview) is controlled by a command button on the data entry form (frmEmployees) which looks at the employee id currently displayed to produce the training history report for that employee.

When a report is produced if the employee has no training history the report displays no data except for the headings on the main report.

I need help to display a msgbox when the report command button is clicked that states: "There is no training history available for John Smith"... rather than have an empty report displayed. The employee name (John Smith)will be taken from the data entry form text boxes (FirstName and LastName) for the selected employee displayed.


Mondoray

 

[rstitzel]

Under the report's design look at the "ON NO DATA" event. You could add your msgbox code in this event. FYI

 

[mondoray]

rstitzel

Thanks for the suggestion.

Any ideas on the code. In particular to pick up the employees name for the form text boxes?

Mondoray

 

[ZmrAbdulla]

Hi,
This is the code you are asking for

'===============xxxxxxx=================='
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
'===============xxxxxxx=================='

If there is no data Report won't open.

Regards
Zameer

 

[mondoray]

Zameer,

Many thanks. I used the following code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no training history available to produce a report for " & [Forms]![frmstartup]![Employees]![FirstName] & " " & [Forms]![frmstartup]![Employees]![LastName] & "
Cancel = -1
End Sub


This code works well it picks the employees first and last names from the data input form and displays them on the message box.

After I click OK on the message box another message box appears stating: The OpenReport action was canceled.

How can I stop this? Do I need to put code into the Report preview command? If so what do I need?

Mondoray

 

[RoyVidar]

Hi!

Yes - the cancel report gives an err.number = 2501.

If you have error trapping code, consider changing the parts within the "_Err" part. If you don't have error trapping, consider "all";-)

[tt]'After the declarations
on error goto cmbOpenRpt_Err
' rest of your code
docmd.openreport "rptYourReport"
' more code?
cmbOpenRpt_Exit:
exit sub
cmbOpenRpt_Err:
if err.number<>2501 then
msgbox err.description
end if
resume cmbOpenRpt_Exit
end sub[/tt]

This should only give an error message on other errors.

HTH Roy-Vidar

 

[Prophets]

There is an Event for reports call on no data
in here:

Private Sub Report_NoData(Cancel As Integer)

msgbox &quot;There is no training history available for &quot; &_ Employeename, vbokonly,&quot;Report Aborted&quot;

cancel = true

End Sub

 

[mondoray]

Guys,

Many thanks for your replies.

Whilst I was awaiting a reply to this thread I found the code below in the Northwind Sample Database:

Err_Command157_Click:
'If action was cancelled by user, dont display error message'
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_Command157_Click
Else
MsgBox Err.Description
Resume Exit_Command157_Click
End If

It works like a dream.

Mondoray