Setting Report's RecordSource

[JamesJ]

I'm trying to change a report's RecordSource by using the following in
the OnClick of a command button:

Me.RecordSource = "qryQueryName"

Nothing happens when I click the button.
The Query works fine when I run it. It brings up a datasheet with
the correct records. I just can't seem to apply it properly to the
report. I have have other queries I want to use as the record source
of the report and I want trigger them with command buttons on the report.

Thanks,
James

 

[Duane Hookom]

I'm not sure what button you are clicking on but get used to nothing
happening when you click anything on a report except the page navigation and
close button.

This is a bit unusual to change the record source of the report. I think I
provided the typical method for at least filtering a report in a past thread.

Can you explain what is different regarding your queries and why you think
you need to change them?

 

[Gina Whipp]

JamesJ,

I think you are misunderstanding what exactly a report is... An Access
report is basically an image (a snapshot of the data at the point of
opening), you can't change the RecordSource unless you Open/Close the
report. To understand more about reports see:
http://allenbrowne.com/casu-22.html

Perhaps you would consider creating a small form with command buttons that
you could use to set your filter on your report. The form could be small
and you could open it and close it based on the report. For example, once
the User selects a filter then the report would open and the form would
minimize or close. Once the report is closed then form would reopen. OR
you could leave the form open and allow users to move it to the side.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[JamesJ]

I believe I understand. One needs to set or change the reports RecordSource
before opening the report.

Thanks

James

 

[JamesJ]

I get now. I can't change the recordsource while the report is open. Only
before
it is opened.

Thanks,
James

 

[Duane Hookom]

99% of the time you only need to apply a different filter/criteria to a
report's record source. You shouldn't have multiple, similar queries where
the tables and fields are the same but the subset of records is different.

 

[JamesJ]

I'm now trying a simple apply filter to a report usong the Onclick of a
command button.
Clicking the button produces no filter. What am I missing???
The command button is in the header of the report. The filed DvdMovieTypeID
is a number
field and is on the report.

Private Sub cmdOpenMovieGenre_Click()

Me.Filter = DvdMovieTypeID.Value = 1
Me.FilterOn = True

End Sub

James

 

[Duane Hookom]

Clicking any control on a report will be of no functional value.

You should have a form with a combo box of DvdMovieTypeID values
(cboDMTypeID). Add a command button on your form with code like:

'--- start of command button code ----
Dim strWhere as String
Dim strReport as String
strReport = "rptMyDVDReport"
strWhere = "1=1 "
If not IsNull(Me.cboDMTypeID) Then
'assuming DvdMovieTypeID is numeric
strWhere = strWhere & " AND [DvdMovieTypeID] = " & cboDMTypeID
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere
'--- end of command button code ----

 

[JamesJ]

Got it now, thanks. A combo box based on a lookup table I've been using to
filter records on form based oin the same table as the report worked fine.
This stuff gives me a headache, sometimes.

Thanks again,
James

Clicking any control on a report will be of no functional value.

You should have a form with a combo box of DvdMovieTypeID values
(cboDMTypeID). Add a command button on your form with code like:

'--- start of command button code ----
Dim strWhere as String
Dim strReport as String
strReport = "rptMyDVDReport"
strWhere = "1=1 "
If not IsNull(Me.cboDMTypeID) Then
'assuming DvdMovieTypeID is numeric
strWhere = strWhere & " AND [DvdMovieTypeID] = " & cboDMTypeID
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere
'--- end of command button code ----

--
Duane Hookom
Microsoft Access MVP

I'm now trying a simple apply filter to a report usong the Onclick of a
command button.
Clicking the button produces no filter. What am I missing???
The command button is in the header of the report. The filed
DvdMovieTypeID
is a number
field and is on the report.

Private Sub cmdOpenMovieGenre_Click()

Me.Filter = DvdMovieTypeID.Value = 1
Me.FilterOn = True

End Sub

James