471,355 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Reusable report with a modifiable rowsource property and or code ?

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

Jan 2 '07 #1
3 2024
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
Jan 2 '07 #2

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 ?

Jan 6 '07 #3
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
Jan 6 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lgeastwood | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.