subreport not displaying

[C.J.]

I used the subreport wizard and dragged a report to an existing report, and
when I go to print preview two problems occur. The first problem is that it
keeps asking for the same paraI used the sub report wizard and dragged a
report to an existing report, and when I go to print preview two problems
occur: The first problem is that it keeps asking for the same parameters
over and over again. For example, when I open the report, it’s asked for a
beginning and ending date as it is setup to do, but it keeps asking for it
over and over again. The second problem is when I enter the parameter
requests once, I just hit enter until it clears, then one it does, my main
data is displayed but not the sub report data. I tried linking master and
child fields and it still does not work. All data is coming from the same
query and I have two reports coming from that same query also. Any
suggestions would be greatly appreciated.

 

[Duane Hookom]

IMHO, parameter prompts are never good user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. Try use a form with controls for
your filtering.

If you are still stumped, come back with follow-up questions.

 

[C.J.]

Ok, thanks. I finally got to see my data on the print preview but that was
only after I entered the parameters three sometimes five times. And that
depends on how many sub forms I have in my report. Any clue or knowledge on
how to solve this problem?

C.J.

 

[Duane Hookom]

You shouldn't be seeing any parameter prompts if you are using a form to
enter criteria values.

 

[C.J.]

This is a report based on a select query. When i open the report, it asks
for a beginning date and an ending date. once that is entered, my data is
displayed. When i added two subreports to my main report, thats when the
problems came up. Now i have to enter the same parameters five times before
it displays any information. The information on the main report comes out
fine, but if i input the wrong parameters for the other four times, then the
data on my subreport comes out wrong. All corresponding data has the same
"received date" and that's what the parameters are asking for, a beginning
and ending date. I tried to masterlink and child link the fields, but that
seems to make it worse. Need Help. Thanks.

C.J.

 

[Duane Hookom]

Did you read the link in my first reply? Don't ever use parameter prompts.
Let us know if you need information on how to use controls on forms.

 

[C.J.]

yes i would like more information on controls on forms. i am not very
experience with access so any info would be greatly appreciated.
C.J.

 

[Duane Hookom]

You start by creating a form (or using one that is already available) and
adding a control or controls that allow users to enter or select values that
you want to use to filter your report. Then you set the criteria in the
report's record source, like

BETWEEN Forms!YourFormName!txtStartDate And Forms!YourFormName!txtEndDate

Make sure the form is open and values are entered for the criteria before
you open the report.

 

[C.J.]

how do i create this form? i saw somewhere on this site where someone created
a form for this purpose, but when i did that, name# came up in the text boxes
and i did not understand the SQL language. I am not familiar with SQL. what
can i do?

 

[Duane Hookom]

There should be nothing in the Control Sources of the text boxes so there
can't be any error displayed. The form has no Record Source. It is just a
plain, unbound form with some unbound text boxes.

 

[C.J.]

ok i created a form and when i put my dates in to run it, it got an error
message and a visual basic screen came up. The message said "Complie Error
Method or data member not found." What does that mean?

 

[C.J.]

ok i created a form and when i put my dates in to run it, it got an error
message and a visual basic screen came up. The message said "Compile Error
Method or data member not found." What does that mean?

here is my visual basic code

Private Sub Command4_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

 

[Duane Hookom]

What line of code causes the error? I would simplify the code like:

Private Sub Command4_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

'using the next line doesn't bother the criteria
' but makes code much simpler
strWhere = "1=1 "

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND " & strField & _
" >= " & Format(Me.txtStartDate,conDateFormat)
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND " & strField & _
" <= " & Format(Me.txtEndDate,conDateFormat)
End If

'For debugging purposes only.
Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

 

[C.J.]

same error come up. the Private Sub Command4_Click() is highlighted in
yellow and .txtStartDate is highlighted gray. what do i do?

 

[Duane Hookom]

You make sure that you have a text box on your current form named
txtStartDate and have a date value entered. While you are at it, make sure
you have another text box named txtEndDate.

 

[C.J.]

i have done that and it still does not work?

You make sure that you have a text box on your current form named
txtStartDate and have a date value entered. While you are at it, make sure
you have another text box named txtEndDate.

 

[Duane Hookom]

If you are still getting the error then the form that contains the code
doesn't have a control with that name. I think you mentioned something
earlier about one form that opens another form. The code is running in the
"another form" and your controls might be contained on "one form".