Changing Record Source for Report

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

Guest

Greetings

I would like to know how to change the record source for a report in visual
basic. I have one report that can be run using either Query C or Query F. I
have the reports being run off of a form where a user enters a year and then
selects either fiscal or calendar. (Note only months and years are stored in
the database so I can not use a full date function to distinquish between the
fiscal and calendar year). Query C runs the data for the calendar year, and
Query F runs the data for the fiscal year. What I want to be able to do is
to change the source (Query C or F) for the report based on the user's
selection of Calendar or Fiscal. I realize I can use VB to rewrite the SQL
for a query each time, but I would rather just use VB to change the record
source. Any thoughts, helpful suggestions?

Thanks

Jessica
 
Greetings

I would like to know how to change the record source for a report in visual
basic. I have one report that can be run using either Query C or Query F. I
have the reports being run off of a form where a user enters a year and then
selects either fiscal or calendar. (Note only months and years are stored in
the database so I can not use a full date function to distinquish between the
fiscal and calendar year). Query C runs the data for the calendar year, and
Query F runs the data for the fiscal year. What I want to be able to do is
to change the source (Query C or F) for the report based on the user's
selection of Calendar or Fiscal. I realize I can use VB to rewrite the SQL
for a query each time, but I would rather just use VB to change the record
source. Any thoughts, helpful suggestions?

Thanks

Jessica
Click to expand...

Leave the report's recordsource property blank.

Code the Report's Open event:
If forms!FormName!ControlName = "Calendar" Then
Me.Recordsource = "QueryC"
Else
Me.Recordsource = "QueryF"
End If
 
hi,
in the reports on open event...

Private Sub Report_Open(Cancel As Integer)
dim strName as string
if [Forms]![frmYourform]![txttypefield] = "fiscal" then
strName = "queryF"
me.recordsource = strName
else
strName = "queryC"
me.recordsource = strName
end if
end sub

for the year you may have to put that criteria in the
queries [Forms]![frmYourform]![txtyearfield]
untested but should work.
good luck
 
Thanks! That worked perfectly!

Jessica


fredg said:
Leave the report's recordsource property blank.

Code the Report's Open event:
If forms!FormName!ControlName = "Calendar" Then
Me.Recordsource = "QueryC"
Else
Me.Recordsource = "QueryF"
End If
Click to expand...
 
Back
Top