If no records show criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I have a report based on a query; the user should enter a criteria from the
query when runs the report; for example [enter store number].

I would like to show the store number in the report when there are no
records; or some message, like "No records for that store"

How do I do that?

Thanks,

Dan
 
You can't reference the [] parameter from a query if there are no records
returned. You should use a reference to a control on a form for your
criteria rather than a parameter prompt. You could then use a text box like:
=IIf([HasData] = 0, "There are no records for store: " &
Forms!frmA!txtStoreID , "")
 
Thanks Duane; I will try...

Dan

Duane Hookom said:
You can't reference the [] parameter from a query if there are no records
returned. You should use a reference to a control on a form for your
criteria rather than a parameter prompt. You could then use a text box like:
=IIf([HasData] = 0, "There are no records for store: " &
Forms!frmA!txtStoreID , "")

--
Duane Hookom
MS Access MVP


D said:
Hi:

I have a report based on a query; the user should enter a criteria from
the
query when runs the report; for example [enter store number].

I would like to show the store number in the report when there are no
records; or some message, like "No records for that store"

How do I do that?

Thanks,

Dan
Click to expand...
Click to expand...
 
Hi Duane:

I get an error:#Name?; the form that I am referring the name of the store is
the amin form; does not have any relation with the query or the report;
should the criteria come from a combo box/input form?

Thanks,

Dan

Duane Hookom said:
You can't reference the [] parameter from a query if there are no records
returned. You should use a reference to a control on a form for your
criteria rather than a parameter prompt. You could then use a text box like:
=IIf([HasData] = 0, "There are no records for store: " &
Forms!frmA!txtStoreID , "")

--
Duane Hookom
MS Access MVP


D said:
Hi:

I have a report based on a query; the user should enter a criteria from
the
query when runs the report; for example [enter store number].

I would like to show the store number in the report when there are no
records; or some message, like "No records for that store"

How do I do that?

Thanks,

Dan
Click to expand...
Click to expand...
 
Put a textbox in the Detail section of your report called txtStoreNoData:
Control Source: = "No records for Store " & [Enter Store Number]
Visible: No


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' check if any records
If Me.Report.HasData = 0 Then
Me.StoreNoData.Visible = True
Else
Me.StoreNoData.Visible = False
End If
End Sub

HTH,
Debbie


| Hi:
|
| I have a report based on a query; the user should enter a criteria from the
| query when runs the report; for example [enter store number].
|
| I would like to show the store number in the report when there are no
| records; or some message, like "No records for that store"
|
| How do I do that?
|
| Thanks,
|
| Dan
|
|
 
YOu need to replace the parameter with a reference to your control on your
form. The form must be open.

If this doesn't work, come back with the error and the control source.

--
Duane Hookom
MS Access MVP
--

D said:
Hi Duane:

I get an error:#Name?; the form that I am referring the name of the store
is
the amin form; does not have any relation with the query or the report;
should the criteria come from a combo box/input form?

Thanks,

Dan

Duane Hookom said:
You can't reference the [] parameter from a query if there are no records
returned. You should use a reference to a control on a form for your
criteria rather than a parameter prompt. You could then use a text box
like:
=IIf([HasData] = 0, "There are no records for store: " &
Forms!frmA!txtStoreID , "")

--
Duane Hookom
MS Access MVP


D said:
Hi:

I have a report based on a query; the user should enter a criteria from
the
query when runs the report; for example [enter store number].

I would like to show the store number in the report when there are no
records; or some message, like "No records for that store"

How do I do that?

Thanks,

Dan
Click to expand...
Click to expand...
Click to expand...
 
You can add a combo box to your form or even open another form with a combo
box to select a value and then use the value of the combo box to filter the
report and display your text.

BTW: Don't waste your time trying to reference the parameter value as
DebbieG suggests. It just won't work if the report's record source does not
return any records.

--
Duane Hookom
MS Access MVP


D said:
Hi Duane:

Thanks! the only way that works is if the form is on the same record/store
that the report runs for.

Anyway this is a solution for now.

Dan

Duane Hookom said:
As I stated in my first reply in this thread:
"You can't reference the [] parameter from a
query if there are no records returned."

So the following expression will not work:
= "No records for Store " & [Enter Store Number]

I don't recall stating that a form should be on the same record that you
run
the report for.

--
Duane Hookom
MS Access MVP
--

D said:
Hi Duane:

I had the form open before; but I did not know that should be on the
same
record that you run the report for.

Thanks a lot; I will try Debie's idea.

Dan
:

YOu need to replace the parameter with a reference to your control on
your
form. The form must be open.

If this doesn't work, come back with the error and the control source.

--
Duane Hookom
MS Access MVP
--

Hi Duane:

I get an error:#Name?; the form that I am referring the name of the
store
is
the amin form; does not have any relation with the query or the
report;
should the criteria come from a combo box/input form?

Thanks,

Dan

:

You can't reference the [] parameter from a query if there are no
records
returned. You should use a reference to a control on a form for
your
criteria rather than a parameter prompt. You could then use a text
box
like:
=IIf([HasData] = 0, "There are no records for store: " &
Forms!frmA!txtStoreID , "")

--
Duane Hookom
MS Access MVP


Hi:

I have a report based on a query; the user should enter a
criteria
from
the
query when runs the report; for example [enter store number].

I would like to show the store number in the report when there
are
no
records; or some message, like "No records for that store"

How do I do that?

Thanks,

Dan
Click to expand...
Click to expand...
Click to expand...
 
Hi Debbie:

Thanks and appreciated!; but is not working: #error; am I missing something?

The way that works, at least for now is to refer a text box to the store
field from a form but the form should be open on the record that you run the
report for. Please see my communication with Duane.

Dan
 
You need to create a form like Duane Hookom suggests. It needs an unbound text
box called something like GetStoreNumber and a command button that previews the
report. Then in your query change

[Enter Store Number]
to
[Forms]![TheNameOfTheForm]![GetStoreNumber]

And in your report change

& [Enter Store Number]
to
& [Forms]![TheNameOfTheForm]![GetStoreNumber]

and everything should work OK.

Sorry to lead you down the wrong path,
Debbie


| Hi Debbie:
|
| Thanks and appreciated!; but is not working: #error; am I missing something?
|
| The way that works, at least for now is to refer a text box to the store
| field from a form but the form should be open on the record that you run the
| report for. Please see my communication with Duane.
|
| Dan
|
|
| "DebbieG" wrote:
|
| > No. In the design view of the report, double-click on gray bar that states
| > "Detail". That should open the Properties dialog box. In there, click on
the
| > Event tab. Double-click in On Print which should show [Event Procedure].
Click
| > the button on the right with 3 dots (...). This should take you to "Private
Sub
| > Detail_Print. Copy the code below (not the Private Sub and End Sub lines)
and
| > paste after your "Private Sub Detail_Print.
| >
| > Debbie
| >
| >
| > | > | Hi Debbie:
| > |
| > | Thanks! the private sub would be on "open" report?
| > |
| > | Dan
| > |
| > | "DebbieG" wrote:
| > |
| > | > Put a textbox in the Detail section of your report called
txtStoreNoData:
| > | > Control Source: = "No records for Store " & [Enter Store Number]
| > | > Visible: No
| > | >
| > | >
| > | > Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
| > | > ' check if any records
| > | > If Me.Report.HasData = 0 Then
| > | > Me.StoreNoData.Visible = True
| > | > Else
| > | > Me.StoreNoData.Visible = False
| > | > End If
| > | > End Sub
| > | >
| > | > HTH,
| > | > Debbie
| > | >
| > | >
| > | > | > | > | Hi:
| > | > |
| > | > | I have a report based on a query; the user should enter a criteria
from
| > the
| > | > | query when runs the report; for example [enter store number].
| > | > |
| > | > | I would like to show the store number in the report when there are no
| > | > | records; or some message, like "No records for that store"
| > | > |
| > | > | How do I do that?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | Dan
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
 
EXACTLY!

--
Duane Hookom
MS Access MVP
--

DebbieG said:
You need to create a form like Duane Hookom suggests. It needs an unbound
text
box called something like GetStoreNumber and a command button that
previews the
report. Then in your query change

[Enter Store Number]
to
[Forms]![TheNameOfTheForm]![GetStoreNumber]

And in your report change

& [Enter Store Number]
to
& [Forms]![TheNameOfTheForm]![GetStoreNumber]

and everything should work OK.

Sorry to lead you down the wrong path,
Debbie


| Hi Debbie:
|
| Thanks and appreciated!; but is not working: #error; am I missing
something?
|
| The way that works, at least for now is to refer a text box to the store
| field from a form but the form should be open on the record that you run
the
| report for. Please see my communication with Duane.
|
| Dan
|
|
| "DebbieG" wrote:
|
| > No. In the design view of the report, double-click on gray bar that
states
| > "Detail". That should open the Properties dialog box. In there,
click on
the
| > Event tab. Double-click in On Print which should show [Event
Procedure].
Click
| > the button on the right with 3 dots (...). This should take you to
"Private
Sub
| > Detail_Print. Copy the code below (not the Private Sub and End Sub
lines)
and
| > paste after your "Private Sub Detail_Print.
| >
| > Debbie
| >
| >
| > | > | Hi Debbie:
| > |
| > | Thanks! the private sub would be on "open" report?
| > |
| > | Dan
| > |
| > | "DebbieG" wrote:
| > |
| > | > Put a textbox in the Detail section of your report called
txtStoreNoData:
| > | > Control Source: = "No records for Store " & [Enter Store
Number]
| > | > Visible: No
| > | >
| > | >
| > | > Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
| > | > ' check if any records
| > | > If Me.Report.HasData = 0 Then
| > | > Me.StoreNoData.Visible = True
| > | > Else
| > | > Me.StoreNoData.Visible = False
| > | > End If
| > | > End Sub
| > | >
| > | > HTH,
| > | > Debbie
| > | >
| > | >
| > | > | > | > | Hi:
| > | > |
| > | > | I have a report based on a query; the user should enter a
criteria
from
| > the
| > | > | query when runs the report; for example [enter store number].
| > | > |
| > | > | I would like to show the store number in the report when there
are no
| > | > | records; or some message, like "No records for that store"
| > | > |
| > | > | How do I do that?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | Dan
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
Click to expand...
 
Thanks Debbie!

DebbieG said:
You need to create a form like Duane Hookom suggests. It needs an unbound text
box called something like GetStoreNumber and a command button that previews the
report. Then in your query change

[Enter Store Number]
to
[Forms]![TheNameOfTheForm]![GetStoreNumber]

And in your report change

& [Enter Store Number]
to
& [Forms]![TheNameOfTheForm]![GetStoreNumber]

and everything should work OK.

Sorry to lead you down the wrong path,
Debbie


| Hi Debbie:
|
| Thanks and appreciated!; but is not working: #error; am I missing something?
|
| The way that works, at least for now is to refer a text box to the store
| field from a form but the form should be open on the record that you run the
| report for. Please see my communication with Duane.
|
| Dan
|
|
| "DebbieG" wrote:
|
| > No. In the design view of the report, double-click on gray bar that states
| > "Detail". That should open the Properties dialog box. In there, click on
the
| > Event tab. Double-click in On Print which should show [Event Procedure].
Click
| > the button on the right with 3 dots (...). This should take you to "Private
Sub
| > Detail_Print. Copy the code below (not the Private Sub and End Sub lines)
and
| > paste after your "Private Sub Detail_Print.
| >
| > Debbie
| >
| >
| > | > | Hi Debbie:
| > |
| > | Thanks! the private sub would be on "open" report?
| > |
| > | Dan
| > |
| > | "DebbieG" wrote:
| > |
| > | > Put a textbox in the Detail section of your report called
txtStoreNoData:
| > | > Control Source: = "No records for Store " & [Enter Store Number]
| > | > Visible: No
| > | >
| > | >
| > | > Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
| > | > ' check if any records
| > | > If Me.Report.HasData = 0 Then
| > | > Me.StoreNoData.Visible = True
| > | > Else
| > | > Me.StoreNoData.Visible = False
| > | > End If
| > | > End Sub
| > | >
| > | > HTH,
| > | > Debbie
| > | >
| > | >
| > | > | > | > | Hi:
| > | > |
| > | > | I have a report based on a query; the user should enter a criteria
from
| > the
| > | > | query when runs the report; for example [enter store number].
| > | > |
| > | > | I would like to show the store number in the report when there are no
| > | > | records; or some message, like "No records for that store"
| > | > |
| > | > | How do I do that?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | Dan
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
Click to expand...
 
Thanks Duane!

Duane Hookom said:
EXACTLY!

--
Duane Hookom
MS Access MVP
--

DebbieG said:
You need to create a form like Duane Hookom suggests. It needs an unbound
text
box called something like GetStoreNumber and a command button that
previews the
report. Then in your query change

[Enter Store Number]
to
[Forms]![TheNameOfTheForm]![GetStoreNumber]

And in your report change

& [Enter Store Number]
to
& [Forms]![TheNameOfTheForm]![GetStoreNumber]

and everything should work OK.

Sorry to lead you down the wrong path,
Debbie


| Hi Debbie:
|
| Thanks and appreciated!; but is not working: #error; am I missing
something?
|
| The way that works, at least for now is to refer a text box to the store
| field from a form but the form should be open on the record that you run
the
| report for. Please see my communication with Duane.
|
| Dan
|
|
| "DebbieG" wrote:
|
| > No. In the design view of the report, double-click on gray bar that
states
| > "Detail". That should open the Properties dialog box. In there,
click on
the
| > Event tab. Double-click in On Print which should show [Event
Procedure].
Click
| > the button on the right with 3 dots (...). This should take you to
"Private
Sub
| > Detail_Print. Copy the code below (not the Private Sub and End Sub
lines)
and
| > paste after your "Private Sub Detail_Print.
| >
| > Debbie
| >
| >
| > | > | Hi Debbie:
| > |
| > | Thanks! the private sub would be on "open" report?
| > |
| > | Dan
| > |
| > | "DebbieG" wrote:
| > |
| > | > Put a textbox in the Detail section of your report called
txtStoreNoData:
| > | > Control Source: = "No records for Store " & [Enter Store
Number]
| > | > Visible: No
| > | >
| > | >
| > | > Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
| > | > ' check if any records
| > | > If Me.Report.HasData = 0 Then
| > | > Me.StoreNoData.Visible = True
| > | > Else
| > | > Me.StoreNoData.Visible = False
| > | > End If
| > | > End Sub
| > | >
| > | > HTH,
| > | > Debbie
| > | >
| > | >
| > | > | > | > | Hi:
| > | > |
| > | > | I have a report based on a query; the user should enter a
criteria
from
| > the
| > | > | query when runs the report; for example [enter store number].
| > | > |
| > | > | I would like to show the store number in the report when there
are no
| > | > | records; or some message, like "No records for that store"
| > | > |
| > | > | How do I do that?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | Dan
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
Click to expand...
Click to expand...
 
Back
Top