questions and Run-time error '2497'

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

Guest

I'm trying to create a Form that basically allows users to Choose a Report ,
then how they wish to view the report by selecting Option Button A (Summary)
or Option Button B (Detail).


Below is my VB Coding:


Option Compare Database


Private Sub Command17_Click()
DoCmd.Close acForm, "form3d"
End Sub

Private Sub Command18_Click()
Dim var
DoCmd.OpenReport selectedReport.Tag, acViewNormal
DoCmd.Close acForm, "form3d"
End Sub

Private Sub PREVIEW_Click()
DoCmd.OpenReport selectedReport.Tag, acViewPreview
DoCmd.Close acForm, "form3d"
End Sub


Private Sub selectedReport_Change()
Select Case selectedReport
Case "Field Office Summary Constrained"
selectedReport.Tag = ""
Case "Main Output Constrained"
selectedReport.Tag = " "
Case "Main Output Constrained Summary"
selectedReport.Tag = " "
Case "Field Office Summary Optimal"
selectedReport.Tag = " "
Case "Field Office Summary Optimal - Summary"
selectedReport.Tag = " "
Case "Main Output Optimal"
selectedReport.Tag = " "
Case "Main Output Optimal Summary"
selectedReport.Tag = " "
Case "Summary 4"
selectedReport.Tag = " "
Case "Summary 4-Summary"
selectedReport.Tag = " "
Case "Exemptions"
selectedReport.Tag = " "
Case "Summary 3"
selectedReport.Tag = " "
Case "Summary 3-Summary"
selectedReport.Tag = " "
Case "Summary 2"
selectedReport.Tag = " "
Case "Summary 2-Summary"
selectedReport.Tag = " "
Case "Summary 1"
selectedReport.Tag = " "
Case "Summary 1-Summary"
selectedReport.Tag = " "
Case "Air Passenger Inspection"
selectedReport.Tag = " "
Case "Air Passenger Inspection Summary"
selectedReport.Tag = " "
Case "Land Passenger Inspection"
selectedReport.Tag = " "
Case "Sea Passenger Inspection"
selectedReport.Tag = " "
Case "Cargo Inspection"
selectedReport.Tag = " "
Case "Cargo Inspection Summary"
selectedReport.Tag = " "
Case "Seizures Inspection"
selectedReport.Tag = " "
Case "Seizures Inspection Summary"
selectedReport.Tag = " "
Case "Agriculture Inspection"
selectedReport.Tag = " "
Case "Agriculture Inspection Summary"
selectedReport.Tag = " "
Case "Processing Times"
selectedReport.Tag = " "
Case "Percentage Factors"
selectedReport.Tag = " "
Case "Global Assumptions"
selectedReport.Tag = " "
End Select
End Sub

Private Sub selectedReport_AfterUpdate()
DoCmd.Close acForm, "form3d"
End Sub
 
What kind of control is selectedReport? Regardless of what kind of control
it is, the Change event is not the place to do this. The Change event fires
on every keystroke. It should probably be a combo box. That way you don't
have to use the Tag property, you openreport can read the value of the
control.

The error you are getting is because some of the reports that can be
selected put a space in the Tag property, so you are doing an OpenReport with
no report name.

I would suggest you make selectedReport a 2 column combo box and remove
those items that don't have an associated report ojbect from the list. Make
one column the plain text for the user to see and the other the report name.
Now, when you make a selection, you will have a valid report name.
 
thank you for your help... am new to programming Access 2000 with Visual
Basic 6.0 ..(:>

selectedReport is my Option Group name.. I have a combo box name is cboReports
with two option button (1) Summary (2) Detail Report.

You suggest making selectedReport a 2 column combo box and remove
those items that don't have an associated report object from the list. Make
one column the plain text for the user to see and the other the report name.
Now, when you make a selection, you will have a valid report name.

My report names r being pulled from my tblOptGrp
there are four fields
rptID =
rptName = Report Names
NameDetail = English Report Names that user see

Can you provide an example?
 
Okay.
First your row source property should look something like this:
SELECT rptName, NameDetail FROM tblOptGrp

In the combo's properties set these:
Column Count 2
Bound Column 1
Column Widths 0"; 2" (This will make the report name not show, only the
text.
The 2" can be the width necessary to
show your
NameDetail)

So now when you make a selection based on the text,
Me.MyCombo will return the report name and you don't need the select
statement. What I usually do is use one sub for reporting that I pass a
parameter to so I know whether it is Preview or Print.

Private Sub cmdPreview_Click()
Call PrintReport(acViewPreview)
End Sub

Private Sub cmdPrint_Click()
Call PrintReport(acViewNormal)
End Sub

Private Sub PrintReport(intView as Integer)
Docmd.OpenReport Me.SelectedReport, intView
End Sub

Now, what I don't know for sure is how you are handling detail and summary
selection. You mention a combo with Detail and Summary, but with only 2
selections, I would go with an option group that returns one of the values,
if you need this control at all.

The only reason to have the control would be if you are using the technique
of making the report's detail section hidden. Then the report would look at
the value of the control on the form to determine what to do. If you have
different report objects for the two types, then you just need both report
names in the combo.
 
Once Again Thank you

with the two Option Buttons

2. Option Button(Summary) will provide the User with a summarized report of
data. To view, or print

3. Option Button (Detail) will allow the user Detail Report.

Example: Should User select Report

a. FO Summary Constrained
b. FO Summary Constrained Summary
c. Agriculture
d. Summary of Agriculture

click one of the button below he/she should be able to preview the selected
report

o Summary o Detail
 
Since you are using different report objects for summary and detail, the
option buttons are not necessary. If you put the report name and description
of both in the table the combo box uses as a row source, you don't need it.

Even if you really want to do it that way, you would have to standardize
your report naming so your code would know how to call the correct report
name.

For example, if you selected Agriculture, your report name is "Agriculture".
Then if you select Summary, it would be "Summary of Agriculture", but if you
select
FO Summary Constrained, then the summary would be FO Summary Constrained
Summary. Since the word Summary is in different places in the string, your
code would not know what report to open.

To do it this way, you would need to have a standard that says append "Sum"
to the end of each report name if it is the summary. So you would have
Agriculture = Detail
AgricultureSum = Summary

Then in your code:

strReportName = Me.cboSelectedReport
If Me.OpgType = 1 Then
strReportName = strReportName & "Sum"
End If
 
Back
Top