Report Question...

[JOM]

My reports row source is from a crosstab query. The columns on my report are
undound as they are filled with the necessary infor. My problem is that I
want to have the each columns total, but i don't know how to..

Here is my open event procedure of the report.

Private Sub Report_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim j As Integer
Dim intColCount As Integer
Dim strName As String
Dim s1 As String

Set db = CurrentDb
s1 = Me.RecordSource

Set qdf = db.QueryDefs(s1)
qdf.Parameters(0) = [Forms]![frmAcct]![txtBeginDate]
qdf.Parameters(1) = [Forms]![frmAcct]![txtEndDate]
Set rs = qdf.OpenRecordset

intColCount = rs.Fields.Count
j = 1
' Fill in information for the necessary controls.
For i = 1 To intColCount - 1
strName = rs.Fields(i).Name
Me.Controls("lblEmpl" & j).Caption = strName
Me.Controls("txtLoanNumbers" & j).ControlSource = strName
j = j + 1
Next i

'Move position of Row totals Label & Textbox if less than 13 loan numbers of
data is displayed
If intColCount < 15 Then
i = 0.65 * intColCount * 1440 'New left margin in twips (1 inch = 1440
twips)

End If
End Sub

 

[Ken Sheridan]

I'd imagine you'd need to include some more unbound controls in the report
footer, txtLoanNumbersTotal1, txtLoanNumbersTotal2 etc. and set the
ControlSource properties of these in the code in the same way as you do for
the detail controls

intColCount = rs.Fields.Count
j = 1
' Fill in information for the necessary controls.
For i = 1 To intColCount - 1
strName = rs.Fields(i).Name
Me.Controls("lblEmpl" & j).Caption = strName
Me.Controls("txtLoanNumbers" & j).ControlSource = strName
Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum(" & strName & ")"
j = j + 1
Next i

Another possible way would be to leave them with a blank ControlSource,
initialize them to zero in the report header's Print event procedure:

txtLoanNumbersTotal1 = 0
txtLoanNumbersTotal2 = 0
<and so on>

and then increment them in the detail section's Print event procedure:

If PrintCount = 1 Then
txtLoanNumbersTotal1 = txtLoanNumbersTotal1 + txtLoanNumbers1
txtLoanNumbersTotal2 = txtLoanNumbersTotal2 + txtLoanNumbers2
<and so on>
End If

Ken Sheridan
Stafford, England

 

[JOM]

Thanks Ken, I tried the first option but am getting an error Error 2434. The
expression you entered contains invalid syntax.... I added the textboxes as
you said and gave them the names as you said...

I'd imagine you'd need to include some more unbound controls in the report
footer, txtLoanNumbersTotal1, txtLoanNumbersTotal2 etc. and set the
ControlSource properties of these in the code in the same way as you do for
the detail controls

intColCount = rs.Fields.Count
j = 1
' Fill in information for the necessary controls.
For i = 1 To intColCount - 1
strName = rs.Fields(i).Name
Me.Controls("lblEmpl" & j).Caption = strName
Me.Controls("txtLoanNumbers" & j).ControlSource = strName
Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum(" & strName & ")"
j = j + 1
Next i

Another possible way would be to leave them with a blank ControlSource,
initialize them to zero in the report header's Print event procedure:

txtLoanNumbersTotal1 = 0
txtLoanNumbersTotal2 = 0
<and so on>

and then increment them in the detail section's Print event procedure:

If PrintCount = 1 Then
txtLoanNumbersTotal1 = txtLoanNumbersTotal1 + txtLoanNumbers1
txtLoanNumbersTotal2 = txtLoanNumbersTotal2 + txtLoanNumbers2
<and so on>
End If

Ken Sheridan
Stafford, England

My reports row source is from a crosstab query. The columns on my report are
undound as they are filled with the necessary infor. My problem is that I
want to have the each columns total, but i don't know how to..

Here is my open event procedure of the report.

Private Sub Report_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim j As Integer
Dim intColCount As Integer
Dim strName As String
Dim s1 As String

Set db = CurrentDb
s1 = Me.RecordSource

Set qdf = db.QueryDefs(s1)
qdf.Parameters(0) = [Forms]![frmAcct]![txtBeginDate]
qdf.Parameters(1) = [Forms]![frmAcct]![txtEndDate]
Set rs = qdf.OpenRecordset

intColCount = rs.Fields.Count
j = 1
' Fill in information for the necessary controls.
For i = 1 To intColCount - 1
strName = rs.Fields(i).Name
Me.Controls("lblEmpl" & j).Caption = strName
Me.Controls("txtLoanNumbers" & j).ControlSource = strName
j = j + 1
Next i

'Move position of Row totals Label & Textbox if less than 13 loan numbers of
data is displayed
If intColCount < 15 Then
i = 0.65 * intColCount * 1440 'New left margin in twips (1 inch = 1440
twips)

End If
End Sub

 

[Ken Sheridan]

Try taking out the = sign from the literal string:

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "Sum(" & strName & ")"

Ken Sheridan
Stafford, England

 

[JOM]

I tried that and I was getting an error
error:438. object doesn't support thei property or method

 

[Douglas J. Steele]

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" & strName &
"])"

 

[JOM]

Doug, Thank you so much. You saved me alot...

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" & strName &
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried that and I was getting an error
error:438. object doesn't support thei property or method

 

[JOM]

Dough, I have another question as follows regarding to this post
What will happen if I have more than 13 columns, I thought that would like
start on a new page? But apparently for me now I have more Employees and am
getting an error that lblEmpl14 is not defined. How can I take care of that
other than changing the paper size, because one never knows that maybe
tomorrow there might be 100 and that won't fit at all.......

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" & strName &
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried that and I was getting an error
error:438. object doesn't support thei property or method

 

[Douglas J. Steele]

Afraid I don't understand the question, since you never indicated anywhere
what your tables looked like

Do you have a separate field for each employee? If so, then it sounds as
though you haven't properly normalized your database. Jeff Conrad has some
good references on this topic at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dough, I have another question as follows regarding to this post
What will happen if I have more than 13 columns, I thought that would like
start on a new page? But apparently for me now I have more Employees and
am
getting an error that lblEmpl14 is not defined. How can I take care of
that
other than changing the paper size, because one never knows that maybe
tomorrow there might be 100 and that won't fit at all.......

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" & strName
&
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried that and I was getting an error
error:438. object doesn't support thei property or method


:

Try taking out the = sign from the literal string:

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "Sum(" &
strName &
")"

Ken Sheridan
Stafford, England

:

Thanks Ken, I tried the first option but am getting an error Error
2434. The
expression you entered contains invalid syntax.... I added the
textboxes as
you said and gave them the names as you said...

 

[JOM]

Well the report is based on a crosstab query, whereby I have Employee names
as the column headings and date as the row headings and the total count of
loans as the value. According to the way I have formated the report, (that
is how the user wanted it), the report can take upto 12 names per page the 13
column is used as a total column. So this is where the question is, what if
there are more than 12 employees/columns how do I make it go to the next page
and not receive the error...

Afraid I don't understand the question, since you never indicated anywhere
what your tables looked like

Do you have a separate field for each employee? If so, then it sounds as
though you haven't properly normalized your database. Jeff Conrad has some
good references on this topic at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dough, I have another question as follows regarding to this post
What will happen if I have more than 13 columns, I thought that would like
start on a new page? But apparently for me now I have more Employees and
am
getting an error that lblEmpl14 is not defined. How can I take care of
that
other than changing the paper size, because one never knows that maybe
tomorrow there might be 100 and that won't fit at all.......

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" & strName
&
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried that and I was getting an error
error:438. object doesn't support thei property or method


:

Try taking out the = sign from the literal string:

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "Sum(" &
strName &
")"

Ken Sheridan
Stafford, England

:

Thanks Ken, I tried the first option but am getting an error Error
2434. The
expression you entered contains invalid syntax.... I added the
textboxes as
you said and gave them the names as you said...

 

[Douglas J. Steele]

I don't believe there's any way to do that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well the report is based on a crosstab query, whereby I have Employee
names
as the column headings and date as the row headings and the total count of
loans as the value. According to the way I have formated the report,
(that
is how the user wanted it), the report can take upto 12 names per page the
13
column is used as a total column. So this is where the question is, what
if
there are more than 12 employees/columns how do I make it go to the next
page
and not receive the error...

Afraid I don't understand the question, since you never indicated
anywhere
what your tables looked like

Do you have a separate field for each employee? If so, then it sounds as
though you haven't properly normalized your database. Jeff Conrad has
some
good references on this topic at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dough, I have another question as follows regarding to this post
What will happen if I have more than 13 columns, I thought that would
like
start on a new page? But apparently for me now I have more Employees
and
am
getting an error that lblEmpl14 is not defined. How can I take care of
that
other than changing the paper size, because one never knows that maybe
tomorrow there might be 100 and that won't fit at all.......



:

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" &
strName
&
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried that and I was getting an error
error:438. object doesn't support thei property or method


:

Try taking out the = sign from the literal string:

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "Sum(" &
strName &
")"

Ken Sheridan
Stafford, England

:

Thanks Ken, I tried the first option but am getting an error
Error
2434. The
expression you entered contains invalid syntax.... I added the
textboxes as
you said and gave them the names as you said...

 

[JOM]

Thanks Doug...

I don't believe there's any way to do that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well the report is based on a crosstab query, whereby I have Employee
names
as the column headings and date as the row headings and the total count of
loans as the value. According to the way I have formated the report,
(that
is how the user wanted it), the report can take upto 12 names per page the
13
column is used as a total column. So this is where the question is, what
if
there are more than 12 employees/columns how do I make it go to the next
page
and not receive the error...

Afraid I don't understand the question, since you never indicated
anywhere
what your tables looked like

Do you have a separate field for each employee? If so, then it sounds as
though you haven't properly normalized your database. Jeff Conrad has
some
good references on this topic at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dough, I have another question as follows regarding to this post
What will happen if I have more than 13 columns, I thought that would
like
start on a new page? But apparently for me now I have more Employees
and
am
getting an error that lblEmpl14 is not defined. How can I take care of
that
other than changing the paper size, because one never knows that maybe
tomorrow there might be 100 and that won't fit at all.......



:

What's the value of strName? If there are embedded blanks, you'll need

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "=Sum([" &
strName
&
"])"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried that and I was getting an error
error:438. object doesn't support thei property or method


:

Try taking out the = sign from the literal string:

Me.Controls("txtLoanNumbersTotal" & j).ControlSource = "Sum(" &
strName &
")"

Ken Sheridan
Stafford, England

:

Thanks Ken, I tried the first option but am getting an error
Error
2434. The
expression you entered contains invalid syntax.... I added the
textboxes as
you said and gave them the names as you said...