Reusable report with a modifiable rowsource property and or code ?

[u473]

How do I call a reusable report with a modifiable Rowsource property
and or code ?
Like in the following case where the table name will change any
following day ?
I am using Access 2003.

Private Sub Top25_Click()
Dim strSQL As String
strTblName = "tblNew_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 25 tblOld.Fname, tblOld.Lname INTO " &
strTblName & " FROM tblOld"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
' How to I call the report with the variable Rowsource strTblName
from here ?
......
End Sub

Thank you for your Help,
Wayne

[Marshall Barton]

u473 wrote:

>How do I call a reusable report with a modifiable Rowsource property
and or code ?
Like in the following case where the table name will change any
following day ?
I am using Access 2003.

Private Sub Top25_Click()
Dim strSQL As String
strTblName = "tblNew_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 25 tblOld.Fname, tblOld.Lname INTO " &
strTblName & " FROM tblOld"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
' How to I call the report with the variable Rowsource strTblName
from here ?
......
End Sub

The only reliable place at run time that you can set a
report's RecordSource is in the report's Open event.

I see no reason in your post for the use of a make table
query. Why not just use the select part in the report? If
you are using the make table as some kind of archive, I
suggest that it would be better to use a date field in the
original table and execute an Update query to record the
report date.

You can use the OpenReport method's OpenArgs argument to
pass the SQL statement to the report:

Private Sub Top25_Click()
Dim strSQL As String
strSQL = "SELECT TOP 25 Fname, Lname FROM tblOld"
DoCmd.OpenReport "reportname", OpenArgs:= strSQL

In the report's Open event procedure:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

--
Marsh

[u473]

Thank you, you made my day.
I wanted to grab some VBA /SQL concepts and I made some progress
thanks to you.
But, in the design phase of the report, am I not obliged to enter
something for the Rowsource Property, or will will it be satisfied
from the On Open code ?

[Marshall Barton]

u473 wrote:

>Thank you, you made my day.
I wanted to grab some VBA /SQL concepts and I made some progress
thanks to you.
But, in the design phase of the report, am I not obliged to enter
something for the Rowsource Property, or will will it be satisfied
from the On Open code ?

RecordSource, not RowSource.

No, you can leave the RecordSource property empty at design
time. If you have code that refers to fields in the record
source, use their corresponding bound controls instead.
Sometimes it is easier to design and test report with its
RecordSource set, and remove the RecordSource after
everything else is squared away. OTOH, as long as the field
list is always the same, it is no big deal to leave the
RecordSource in and overwrite it in the Open event
procedure.

--
Marsh