Am I doing this right?

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi

I am creating a report based on items that the user selects from a
multi-choice list box. In the code I cycle through the items selected in the
list box and then build the SQL statement. Is there another way to do this
part? A better way?

I want to assign the RecordSource (the SQL statement I just created) to the
report at run-time. It appears to me that I cannot set an object to a report
until it is open in some way. In the code below I have opened the report in
design mode. This is all so I can assign the RecordSource at run-time. There
must be a more elegant way of doing this. Do you know what it is?

Thanks

Tim

Below is the code;

Private Sub cmdOk_Click()

Dim ctl As Control
Dim varItm As Variant
Dim SQL As String
Dim rtpTest As Report

'The initial part of the SQL statement
SQL = "SELECT Transactions.Date, Transactions.Amount,
Transactions.[Transaction Description] "
SQL = SQL & "FROM Transactions WHERE ("

'open the report in design view so I can set object to it
DoCmd.OpenReport "rptTest", acViewDesign

Set rptTest = Reports!rptTest
Set ctl = Me.lstTranDesc

For Each varItm In ctl.ItemsSelected
'append select items to the SQL statement
SQL = SQL & "Transactions.[Transaction Description] = " &
ctl.ItemData(varItm) & " OR "

Next varItm

'take off the excess 'OR'
SQL = Left(SQL, (Len(SQL) - 4))

'finish off the SQL statement
SQL = SQL & ") AND Transactions.Date Between
DateAdd(""d"",-1,[Forms]![frmPickYourOwn]![dtFrom])"
SQL = SQL & " And [Forms]![frmPickYourOwn]![dtTo]"

'assign the recordsource property
rptTest.RecordSource = SQL
DoCmd.OpenReport "rptTest", acViewPreview, , , acWindowNormal

End Sub
 
Tim,

Do it generally like this:

Dim SQLStr As String

<Code to build your SQL string>

DoCmd.OpenReport "ReportName"
Reports!ReportName.Recordsource = SQLStr
 
I tried that and I encountered two problems;

First, as soon as the DoCmd runs it tries to print the report
Second, the line after fails. It says that the name of the report is either
misspelled (which it isn't) or it refers to a report that is not open, which
I guess it isn't as it is trying to print.

So back to the old problem; If I open it in preview mode I get an error
telling me that I cannot assign the RecordSource once the report is in
preview mode, hence my solution of opening it in design mode, referencing it
and then assigning the RecordSource. But I am sure there is a better way.

Any other suggestions? This must be fairly common functionality.

Tim

PC Datasheet said:
Tim,

Do it generally like this:

Dim SQLStr As String

<Code to build your SQL string>

DoCmd.OpenReport "ReportName"
Reports!ReportName.Recordsource = SQLStr


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Tim said:
Hi

I am creating a report based on items that the user selects from a
multi-choice list box. In the code I cycle through the items selected in the
list box and then build the SQL statement. Is there another way to do this
part? A better way?

I want to assign the RecordSource (the SQL statement I just created) to the
report at run-time. It appears to me that I cannot set an object to a report
until it is open in some way. In the code below I have opened the report in
design mode. This is all so I can assign the RecordSource at run-time. There
must be a more elegant way of doing this. Do you know what it is?

Thanks

Tim

Below is the code;

Private Sub cmdOk_Click()

Dim ctl As Control
Dim varItm As Variant
Dim SQL As String
Dim rtpTest As Report

'The initial part of the SQL statement
SQL = "SELECT Transactions.Date, Transactions.Amount,
Transactions.[Transaction Description] "
SQL = SQL & "FROM Transactions WHERE ("

'open the report in design view so I can set object to it
DoCmd.OpenReport "rptTest", acViewDesign

Set rptTest = Reports!rptTest
Set ctl = Me.lstTranDesc

For Each varItm In ctl.ItemsSelected
'append select items to the SQL statement
SQL = SQL & "Transactions.[Transaction Description] = " &
ctl.ItemData(varItm) & " OR "

Next varItm

'take off the excess 'OR'
SQL = Left(SQL, (Len(SQL) - 4))

'finish off the SQL statement
SQL = SQL & ") AND Transactions.Date Between
DateAdd(""d"",-1,[Forms]![frmPickYourOwn]![dtFrom])"
SQL = SQL & " And [Forms]![frmPickYourOwn]![dtTo]"

'assign the recordsource property
rptTest.RecordSource = SQL
DoCmd.OpenReport "rptTest", acViewPreview, , , acWindowNormal

End Sub
Click to expand...
Click to expand...
 
So back to the old problem; If I open it in preview mode I get an error
telling me that I cannot assign the RecordSource once the report is in
preview mode, hence my solution of opening it in design mode, referencing it
and then assigning the RecordSource. But I am sure there is a better way.

Any other suggestions? This must be fairly common functionality.
Click to expand...

I'd suggest setting the Recordsource in the Report's Open event.
 
Back
Top