Access reports (same report different theme)

[Swoop7]

Hi,

I've got a number of variations on the same report. I don't want to recreate/copy the same report X times. I'd rather change the SQL via code. Is this possible?

Could someone post a snippet of code to get me started.

Thanks,
-Swoop

 

[Swoop7]

To answer my own question see below. Are there any better ways of doing this? Can I change the Query object a report uses dynamically/supply SQL?

See

http://www.databasejournal.com/features/msaccess/article.php/1556051

for the source of this code.

Option Explicit
Option Compare Database
Private Sub cmdLaunchTransactionReport_Click()
On Error GoTo Err_cmdLaunchTransactionReport_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim sCriteria
sCriteria = "[MemberId]=" & txtMemberId
stDocName = "rptTransactions"
DoCmd.OpenReport stDocName, acViewPreview, , sCriteria, acWindowNormal
Exit_cmdLaunchTransactionReport_Click:
Exit Sub
Err_cmdLaunchTransactionReport_Click:
msgbox Err.Description
Resume Exit_cmdLaunchTransactionReport_Click
End Sub

 

[Swoop7]

Well that's funny. Another answer to my own question:

Sub cmboCompanyName_AfterUpdate()
Dim strNewRecord As String
strNewRecord = "SELECT * FROM Customers " _
& " WHERE CustomerID = '" _
& Me!cmboCompanyName.Value & "'"
Me.RecordSource = strNewRecord
End Sub

Source this time is Access Help. I suppose this will work with Reports as well as forms.

-Swoop