Why does SQL return only one row??

[Steve]

Folks,

I am opening a recordset in the code for a report, and because it is a
crosstab I have an sql query as the record source. I copied and pasted the
sql code from the SQL view of the query (which runs fine) to the code
itself. The problem is that when the code runs, the query returns only one
row!

Here is the sql code:

TRANSFORM Sum([Master Report].Qty) AS SumOfQty
SELECT [Master Report].SysPartNo, [Master Report].Application, [Master
Report].Market, [Master Report].Product, [Master Report].caption
FROM [Master Report]
GROUP BY [Master Report].SysPartNo, [Master Report].Application, [Master
Report].Market, [Master Report].Product, [Master Report].caption
ORDER BY [Master Report].Product
PIVOT [Master Report].Dimension;


Where Master Report is a select query.

Anyone have any ideas??

Thanks!

 

[Dirk Goldgar]

Folks,

I am opening a recordset in the code for a report, and because it is a
crosstab I have an sql query as the record source. I copied and
pasted the sql code from the SQL view of the query (which runs fine)
to the code itself. The problem is that when the code runs, the query
returns only one row!

Here is the sql code:

TRANSFORM Sum([Master Report].Qty) AS SumOfQty
SELECT [Master Report].SysPartNo, [Master Report].Application, [Master
Report].Market, [Master Report].Product, [Master Report].caption
FROM [Master Report]
GROUP BY [Master Report].SysPartNo, [Master Report].Application,
[Master Report].Market, [Master Report].Product, [Master
Report].caption
ORDER BY [Master Report].Product
PIVOT [Master Report].Dimension;


Where Master Report is a select query.

Anyone have any ideas??

Thanks!

Before we go staring too hard at the SQL, first tell us what makes you
think the query returns only one row. If you opened a recordset on this
query and are checking the recordset's RecordCount property, are you
aware that the RecordCount only represents the records visited so far?
So that until you move to the end of the reccordset, the RecordCount
property doesn't reflect the total number of records?

Normally, a freshy opened dynaset- or snapshot-type recordset will show
a RecordCount of 1 if the query has any records, and 0 if not. To find
out how many records there really are, you have to do something like
this:

With rs
If .RecordCount = 0 Then
Msgbox "The recordset is empty."
Else
.MoveLast
Msgbox "The recordset has " & .RecordCount & " records."
' if you want to go back and process all records, then you
' must go back to the top:
.MoveFirst
Do Until .EOF
' ... do something with each record ...
.MoveNext
Loop
End If
.Close
End With

Please post back if this isn't actually your problem. It was just a
guess on my part.

 

[Steve]

Yes, you were right - it turns out it is returning more than one row, but
for some reason the code in my detail_print section is running only once.
After checking and double checking the me.nextrecord, me.printsection and
me.movelayout I finally decided to check to see that I did in fact have more
than one row. I was looking at row count...but you were right.

Still can't figure out why the detail_print code is running only once,
though...

Folks,

I am opening a recordset in the code for a report, and because it is a
crosstab I have an sql query as the record source. I copied and
pasted the sql code from the SQL view of the query (which runs fine)
to the code itself. The problem is that when the code runs, the query
returns only one row!

Here is the sql code:

TRANSFORM Sum([Master Report].Qty) AS SumOfQty
SELECT [Master Report].SysPartNo, [Master Report].Application, [Master
Report].Market, [Master Report].Product, [Master Report].caption
FROM [Master Report]
GROUP BY [Master Report].SysPartNo, [Master Report].Application,
[Master Report].Market, [Master Report].Product, [Master
Report].caption
ORDER BY [Master Report].Product
PIVOT [Master Report].Dimension;


Where Master Report is a select query.

Anyone have any ideas??

Thanks!

Before we go staring too hard at the SQL, first tell us what makes you
think the query returns only one row. If you opened a recordset on this
query and are checking the recordset's RecordCount property, are you
aware that the RecordCount only represents the records visited so far?
So that until you move to the end of the reccordset, the RecordCount
property doesn't reflect the total number of records?

Normally, a freshy opened dynaset- or snapshot-type recordset will show
a RecordCount of 1 if the query has any records, and 0 if not. To find
out how many records there really are, you have to do something like
this:

With rs
If .RecordCount = 0 Then
Msgbox "The recordset is empty."
Else
.MoveLast
Msgbox "The recordset has " & .RecordCount & " records."
' if you want to go back and process all records, then you
' must go back to the top:
.MoveFirst
Do Until .EOF
' ... do something with each record ...
.MoveNext
Loop
End If
.Close
End With

Please post back if this isn't actually your problem. It was just a
guess on my part.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes, you were right - it turns out it is returning more than one row,
but for some reason the code in my detail_print section is running
only once. After checking and double checking the me.nextrecord,
me.printsection and me.movelayout I finally decided to check to see
that I did in fact have more than one row. I was looking at row
count...but you were right.

Still can't figure out why the detail_print code is running only once,
though...

Without seeing the code -- and possibly other code in your report's
class module -- I couldn't hazard a guess. Are you fooling around with
the NextRecord, PrintSection, and MoveLayout properties? You're aware
that for most reports you don't need to use these at all?

 

[Steve]

Here's the code, if you care to take a look (please care! I know it is
very rough, but I am inexperienced and trying to build on things I've found
here and there. I did have code in to mess with the variables you mention,
but they have since been removed - I'm using watch to look at the values,
and they are true at the end of the with block in detail_print...

Option Compare Database
Option Explicit

Public rst As ADODB.Recordset

Dim intColCount As Integer
Dim TotalColumns As Integer

Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intControlCount As Integer
Dim i As Integer
Dim strName As String
Dim strSQL As String

' On Error GoTo err_sub

Set rst = New ADODB.Recordset

rst.Open _
Source:="TRANSFORM Sum([Master Report].Qty) AS SumOfQty " _
& "SELECT [Master Report].SysPartNo, [Master Report].Application,[Master
Report].Market, [Master Report].Product, [Master Report].caption " _
& "FROM [Master Report] " _
& "GROUP BY [Master Report].SysPartNo, [Master Report].Application, [Master
Report].Market, [Master Report].Product, [Master Report].caption " _
& "ORDER BY [Master Report].Product " _
& "PIVOT [Master Report].Dimension;", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenStatic, _
Options:=adCmdText


intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

TotalColumns = intColCount

' Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
Me.Controls("lblHeader" & i).caption = strName
' Me.Controls("txtData" & i).ControlSource = strName
' Me.Controls("txtSum" & i).ControlSource = "=Sum([" & strName & "])"
Next i

' Hide the extra controls.
For i = intColCount + 1 To intControlCount
' Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
' Me.Controls("txtSum" & i).Visible = False
Next i

' Close the recordset.
'rst.Close


rst.MoveFirst



exit_sub:
Exit Sub
err_sub:
MsgBox Err.Number & ": " & Err.Description
Resume exit_sub

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim strTemp As String

With rst
If .RecordCount = 0 Then
MsgBox "The recordset is empty."
Else
.MoveFirst
For intX = 1 To 12
Me("txtData" & intX) = rst.Fields(intX - 1)
Next intX
'.MoveNext
End If
End With


End Sub

Private Sub detail_retreat()
'On Error GoTo err_sub

rst.MovePrevious

exit_sub:
Exit Sub
err_sub:
MsgBox Err.Number & ": " & Err.Description
Resume exit_sub

End Sub

 

[Dirk Goldgar]

Here's the code, if you care to take a look (please care! I know
it is very rough, but I am inexperienced and trying to build on
things I've found here and there. I did have code in to mess with the
variables you mention, but they have since been removed - I'm using
watch to look at the values, and they are true at the end of the with
block in detail_print...

Option Compare Database
Option Explicit

Public rst As ADODB.Recordset

Dim intColCount As Integer
Dim TotalColumns As Integer

Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intControlCount As Integer
Dim i As Integer
Dim strName As String
Dim strSQL As String

' On Error GoTo err_sub

Set rst = New ADODB.Recordset

rst.Open _
Source:="TRANSFORM Sum([Master Report].Qty) AS SumOfQty " _
& "SELECT [Master Report].SysPartNo, [Master
Report].Application,[Master Report].Market, [Master Report].Product,
[Master Report].caption " _ & "FROM [Master Report] " _
& "GROUP BY [Master Report].SysPartNo, [Master Report].Application,
[Master Report].Market, [Master Report].Product, [Master
Report].caption " _ & "ORDER BY [Master Report].Product " _
& "PIVOT [Master Report].Dimension;", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenStatic, _
Options:=adCmdText


intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

TotalColumns = intColCount

' Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
Me.Controls("lblHeader" & i).caption = strName
' Me.Controls("txtData" & i).ControlSource = strName
' Me.Controls("txtSum" & i).ControlSource = "=Sum([" & strName
& "])" Next i

' Hide the extra controls.
For i = intColCount + 1 To intControlCount
' Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
' Me.Controls("txtSum" & i).Visible = False
Next i

' Close the recordset.
'rst.Close


rst.MoveFirst



exit_sub:
Exit Sub
err_sub:
MsgBox Err.Number & ": " & Err.Description
Resume exit_sub

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim strTemp As String

With rst
If .RecordCount = 0 Then
MsgBox "The recordset is empty."
Else
.MoveFirst
For intX = 1 To 12
Me("txtData" & intX) = rst.Fields(intX - 1)
Next intX
'.MoveNext
End If
End With


End Sub

Private Sub detail_retreat()
'On Error GoTo err_sub

rst.MovePrevious

exit_sub:
Exit Sub
err_sub:
MsgBox Err.Number & ": " & Err.Description
Resume exit_sub

End Sub

Perhaps you could explain what you're trying to do here. You appear to
be setting manually setting control properties and loading control
values from the crosstab query, but what is the actual recordsource of
the report? Is it the same query, or something else? Where did you
crib this code from?

 

[Steve]

Dirk,

There is no record source for the report - I am tying to make an unbound
report, because I need to have two dynamic crosstab reports as sub reports
in another (yet to be designed) report. So, for the time being I'm trying
get one unbound dynamic crosstab report to work. The plan was to then bring
that report as a sub report into the main report, along with another like it
pulling from another crosstab query. I'm sure I am lacking in knowledge of
how to do this properly, as I'm learning as I go. I am also sure the code is
rather sloppy, but at this point I'm jsut trying to get it to work - I will
ultimately be the only user of it anyway.

Anyway, that is why I am manually loading the values.

Basically, I started with the crosstab report code from volume 1 of the
access 2k developer's handbook, but the the stuff in the detail_print
section was "cribbed" from an example someone sent me. Most of it has been
worked and reworked to the point where the original structure isn't really
there anymore, and even so what I'm trying to do doesn't match the code's
original purpose anyway. I have bounced back and forth between ADO and DAO
(I have two main books I am using, and one uses ADO, the other DAO).

Steve

 

[Steve]

I got it - I had to set the me.nextrecord value to false...

 

[Dirk Goldgar]

Dirk,

There is no record source for the report - I am tying to make an
unbound report, because I need to have two dynamic crosstab reports
as sub reports in another (yet to be designed) report. So, for the
time being I'm trying get one unbound dynamic crosstab report to
work. The plan was to then bring that report as a sub report into the
main report, along with another like it pulling from another crosstab
query. I'm sure I am lacking in knowledge of how to do this
properly, as I'm learning as I go. I am also sure the code is rather
sloppy, but at this point I'm jsut trying to get it to work - I will
ultimately be the only user of it anyway.

Anyway, that is why I am manually loading the values.

Basically, I started with the crosstab report code from volume 1 of
the access 2k developer's handbook, but the the stuff in the
detail_print section was "cribbed" from an example someone sent me.
Most of it has been worked and reworked to the point where the
original structure isn't really there anymore, and even so what I'm
trying to do doesn't match the code's original purpose anyway. I have
bounced back and forth between ADO and DAO (I have two main books I
am using, and one uses ADO, the other DAO).

Oh, I see. I also see from your followup reply that you've solved your
own problem, so I'll consider this thread closed unless you reopen it.
Good luck!