Roy:
I can see why you are confused, as I'd misread your previous post; Doh!
With it set up the way I described you don't open the report directly, but
instead open the form. The report is then opened when you click the button
on the form. The reason the control in the report is showing the #name error
when the report is opened directly is that the form is not open so the
reference to it's controls is meaningless as far as the report is concerned.
However, there is a way you can open the report directly and have it open
the form. What you do is put code in the report's Open event procedure which
checks to see if the form is open and if not, cancels the opening of the
report and opens the form for you to insert the parameter values and click
the button to open the report. The code for the report's Open event
procedure would be this:
Const FORMNOTOPEN = 2450
Dim frm As Form
On Error Resume Next
Set frm = Forms![Report Size Range]
If Err = FORMNOTOPEN Then
Cancel = True
DoCmd.OpenForm "[Report Size Range]"
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
The control in the report header and the Report Size Range form stay as is,
though you can change the text for the control in the report header if you
wish, rather than using the text I gave you.
If you do the above and are opening the report from anywhere else in the
application, using the OpenReport method, from a button on another form for
instance you'd have to change the code slightly to handle the error which
would be raised when the above code cancels the opening of the report, e.g.
On Error GoTo Err_PreviewReport_Click
Const REPORTCANCELLED = 2501
Dim stDocName As String
stDocName = "rptStoreListBySizeRangeV1"
DoCmd.OpenReport stDocName, acPreview
Exit_PreviewReport_Click:
Exit Sub
Err_PreviewReport_Click:
Select Case Err.Number
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
' unknown error so inform user
MsgBox Err.Description
End Select
Resume Exit_PreviewReport_Click
Ken Sheridan
Stafford, England
shep said:
Ken you are a good guy to try to help an old man such as I. were We in
Kirkbymooreside, where I have visited many times, it would be my pleasure to
buy you a pint.
As it is, I am still confused.
I renamed controls txtMin and txtMax, same results.
My association with with Kirkbymooreside is Slingsby Aviation.
Kind regards
Roy Hooks
Ken Sheridan said:
It may be because you are calling the text box controls Min and Max, which
are the names of built in aggregation operators. That's why I suggested
txtMin and txtMax.. You will probably get away with it by wrapping the names
in brackets:
="Stores sized between " & Forms![Report Size Range]![Min] & " and " &
Forms![Report Size Range]![Max]
but its nevertheless better to avoid using 'keywords' as object names.
Ken Sheridan
Stafford, England
:
Thanks for your response
I created form "Report Size Range" with two unbound text boxes for Min and
Max and added control to preview report.
Option Compare Database
Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click
Dim stDocName As String
stDocName = "rptStoreListBySizeRangeV1"
DoCmd.OpenReport stDocName, acPreview
Exit_PreviewReport_Click:
Exit Sub
Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click
End Sub
Then added to query
SELECT tblStoreInfoV1.StoreName, tblStoreInfoV1.StoreNo,
tblStoreInfoV1.StoreSize
FROM tblStoreInfoV1
WHERE (((tblStoreInfoV1.StoreSize) Between [Forms]![Report Size Range]![Min]
And [Forms]![Report Size Range]![Max]));
I added unbound text box in report header section on report
="Stores sized between " & Forms![Report Size Range]!Min & " and " &
Forms![Report Size Range]!Max
When I run report the form does not open. Data is generated but text box in
report header shows #Name?
Can you please guide me a bit farther?
Thanks
:
Instead of using simple parameters create a dialogue form with two text
boxes, txtMin and txtMax say and reference theses as the parameters, e.g.
WHERE StoreSize BETWEEN Forms![YourDialogueForm]![txtMin] AND
Forms![YourDialogueForm]![txtMax]
Add a button to the form to open the report, or maybe two buttons, one to
preview it and one to print it.
In your report add an unbound text box in a suitable section, e.g. the
report header with a ControlSource property such as:
="Stores sized between " & Forms![YourDialogueForm]![txtMin] & " and " &
Forms![YourDialogueForm]![txtMax]
Instead of opening the report, open the dialogue form, enter the parameter
values and click the button to open the report. The form must stay open of
course for the report to be able to reference it. If you want it to close
automatically, then put code to do this in the report's Close event procedure:
DoCmd.Close acForm, "YourDialogueForm"
Ken Sheridan
Stafford, England
:
Office Pro 2003
I have a query to select stores within a size range.
SELECT tblStoreInfoV1.StoreName, tblStoreInfoV1.StoreNo,
tblStoreInfoV1.StoreSize
FROM tblStoreInfoV1
WHERE (((tblStoreInfoV1.StoreSize) Between [Min] And [Max]));
How can I get the range user enters to automatically show on report based on
query?
Thanks
