Using SQL To Populate A Tabular Report 2

[Russie]

Hi

The following sql is used to populate a tabular report:

"SELECT Sum(tblDailyTransClearing.Amount) AS SumOfAmount, tblDailyTransClearing.Type
FROM tblDailyTransClearing
GROUP BY tblDailyTransClearing.Type
HAVING tblDailyTransClearing.Type<>'CHK' And tblDailyTransClearing.Type <>'MSC' And tblDailyTransClearing.Type <>'VIS' And tblDailyTransClearing.Type<>'BAC' And tblDailyTransClearing.Type <>'CSH' And tblDailyTransClearing.Type<>'ADJ';&quot;

It gives a result rougly similar to:

Type SumOfAmount
CHG £200
CON £50

I can get the sql working at code level but I can't find out how I could use a sql cursor to populate a tabular report.

How could I populate a tabular report with a sql recordset at code level??

Any help appreciated.

 

[cmmrfrds]

Simply make the sql statement the recordsource in the report.

In the OnOpen event
dim mysql as string

mysql = & _
&quot;SELECT Sum(tblDailyTransClearing.Amount) AS &quot; & _
&quot;SumOfAmount, tblDailyTransClearing.Type &quot; & _
&quot;FROM tblDailyTransClearing &quot; & _
etc...

'check out the sql string
debug.print mysql
'equate to record source
Me.Recordsource = mysql

 

[GKProgrammer]

Hi Russie,

Why would you want to do this at code level? You know your SQL string, simply place this SQL in the Reports recordsource property. Or is there something I am missing?

Regards,
gkprogrammer

 

[Russie]

What I don't understand is how to associate the values in my SQL statement with the fields in my TABULAR FORM

 

[GKProgrammer]

Set the fields ControlSource property to the name of the field in the SQL statement

 

[Russie]

As they would say in England...it's 'avin none of it...

Private Sub Form_Open(Cancel As Integer)

Dim conDatabase As ADODB.Connection
Dim strSQL As String

'set connection object
Set conDatabase = CurrentProject.Connection

strSQL = &quot;SELECT Sum(tblDailyTransClearing.Amount) AS SumOfAmount, tblDailyTransClearing.Type&quot; & _
&quot;FROM tblDailyTransClearing&quot; & _
&quot;GROUP BY tblDailyTransClearing.Type ;&quot;

'execute the SQL
conDatabase.Execute strSQL

'check out the sql string
Debug.Print strSQL
'equate to record source
Me.RecordSource = strSQL

'close and destroy database
conDatabase.Close
Set conDatabase = Nothing

End Sub

I tried to only reference the 'SumOfAmount' field but the properties box doesn't see any value.

When I load the form it sees the whole SQL statement as the record source....

Obviously I'm doing something pretty silly....

 

[GKProgrammer]

OK, DON'T set the Recordsource through code. In design view for the report enter your SQL value into the Recordsource property for the Report. Now while in design view select the fields that you want to refect the data from the SQL statement and change the ControlSource propety to the correct field.

Regards,
gkprogrammer

 

[cmmrfrds]

Go ahead and do what GKProgrammer suggested, but you were close on what you had in the on open event.

A couple of points on the code you had.

You do not need a connection object. you don't need the execute.

This is sufficient.
Me.RecordSource = strSQL

Another thing is that the Access Form will not see the field names when the SQL is built dynamically in code. So, you need to enter the field names directly. I.e. SumOfAmount would be the field name. You can still bind to the field even though Access does not see it in the design view.

 

[Russie]

IT WORKED IT WORKED!!!!!!! wow!!!
Thanks cmmrfrds, you put me on the right track.

Private Sub Form_Open(Cancel As Integer)Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_frmPaymentOpen_Click
'Dim conDatabase As ADODB.Connection
Dim strSQL As String


strSQL = &quot;SELECT SUM(tblDailyTransClearing.Amount) AS [SumOfAmount]&quot; & _
&quot;, tblDailyTransClearing.Type AS [Type]&quot; & _
&quot;FROM tblDailyTransClearing &quot; & _
&quot;GROUP BY tblDailyTransClearing.Type &quot; & _
&quot;HAVING tblDailyTransClearing.Type <> 'CHG' &quot; & _
&quot;And tblDailyTransClearing.Type <>'CON';&quot;

'check out the sql string
Debug.Print strSQL
'equate to record source
Me.RecordSource = strSQL

'close and destroy database
'conDatabase.Close
'Set conDatabase = Nothing

Exit_frmPaymentOpen_Click:
Exit Sub

Err_frmPaymentOpen_Click:
MsgBox Err.Description
Resume Exit_frmPaymentOpen_Click

End Sub