Problem with parameters and a pop-up box

[AmandaH]

Hello all,

I have been asked to create a report based on a query with a
parameter. Basically the user clicks on a button on a form that runs a
query, the query asked them to enter a project number, their answer is
then brings up a report with only the requested project number.

The problem that I am having is that if the project number that is
entered doesn't exist it just opens up a blank report. What I would
like it to do is have a pop-up message box that say "No records". Does
anyone have any ideas on this one?

Thanks,

~Amanda~

 

[pietlinden]

Hello all,

I have been asked to create a report based on a query with a
parameter. Basically the user clicks on a button on a form that runs a
query, the query asked them to enter a project number, their answer is
then brings up a report with only the requested project number.

The problem that I am having is that if the project number that is
entered doesn't exist it just opens up a blank report. What I would
like it to do is have a pop-up message box that say "No records". Does
anyone have any ideas on this one?

Thanks,

~Amanda~

use the NoRecords property of the report to cancel the open event.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data to show..."
Cancel = True
End Sub

 

[Jeff Boyce]

Amanda

Consider a different approach.

What you've described requires the users to memorize project numbers.

Another approach would be to add a combo box (unbound) on your form. The
combo box lists projects (with Project Number plus more descriptive fields).
By setting the width of the first field (ProjectNumber) to "0", it won't
show on the form.

Now modify your query to look at the value in the combo box on that form
(use the following instead of the parameter prompt the query has now):

Forms!YourFormName!cboYourComboBoxName

Keep the button. When you click it, the report starts to open, then runs
the query, which looks (back) at the form to find the ProjectNumber in the
combo box.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AmandaH]

Thanks for both of your answers. The second option, using the combo
box, is a good one and I may use it on following projects. For this
one, however, the users will have the project number in front of them
as they will only be asked questions directly related to the project
number. Some of the numbers also look very similar at a quick glace so
this would also run the risk of pulling up the wrong information.

Thanks again for the input.

~Amanda~