how to populate report with vba code

[ges via AccessMonster.com]

I try to create report from opened form so user can key in the data to filter
the report.
I pass over values from opened form into report header. Then based on the
value entered in the opened form, I created several sql statements. When I
run the code it did not give me any error, the result of the report is about
20 pages, but only the page header populated (from opened form values), but
the details section is blank all 20 pages. Below is my code:

Public Sub Report_Open(Cancel As Integer)

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:

This is my first time to create report using vba code, so I have no idea at
all how it work.
I’ve been stuck for a couple of days try to figure out the way to do this.
Any one kindly enough to help me?

Thanks in advance for any help.

Ges

 

[Larry Linson]

I try to create report from opened form so user can key in the data to
filter
the report.
I pass over values from opened form into report header. Then based on the
value entered in the opened form, I created several sql statements. When
I
run the code it did not give me any error, the result of the report is
about
20 pages, but only the page header populated (from opened form values),
but
the details section is blank all 20 pages. Below is my code:

Public Sub Report_Open(Cancel As Integer)

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:

This is my first time to create report using vba code, so I have no idea
at
all how it work.
I've been stuck for a couple of days try to figure out the way to do this.
Any one kindly enough to help me?

There's virtually no information here for us to use to assist you -- roughly
the equivalent to saying "Something went wrong somewhere. How do I fix it?"
For just one example, you call a procedure named "SQLSetup" and pass it an
argument of "strSQLtxt" -- but, unless my eyes are dimmer than I think and
caused me to look right over it, you haven't explained how you "pass over
values" nor what values you pass over from the form (I'd rather expect to
see a reference to the Report's Open Args statement if a recent version of
Access), you didn't tell us what SQL Setup does/is supposed to do, what
information you provided it, what you think strSQLtxt contains when it is
returned, and my psychic powers are at a low ebb just now. you haven't
explained how you "pass over values" nor what values you pass over from the
form.

As a first try at debugging, between the statements calling SQLSetup and
replacing the RecordSource, you might insert a statement of Debug.Print
strSQLtxt, then look in the Immediate Window to see what strSQLtxt actually
contained. Other Debug.Print statments, judiciously placed in the Form from
which you are calling the Report might shed even more light on what's going
wrong and where.

Larry Linson
Microsoft Office Access MVP

 

[ges via AccessMonster.com]

Thanks for your reply Larry,

I have opened form called frmCollections, Report call ReportCollections. I
create "view report " button in frmCollections to open Report, code as
follow:

stDocName = "ReportCollections"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

In frmCollections I have combobox called "cboAgent" listed all collection
Agents. When user choose the agent, and click "view report" button it pass
the value of cboAgent from frmCollections to lblAgent in ReportCollections
(Report header section)

In frmCollections I have option group contains 9 options called
"optgrpSortByOption" , user has to choose the option first in frmCollections
before they can click "view Report" button.

Below are my codes (2 events - Report_Open and SQLSetup)

Private Sub Report_Open(Cancel As Integer)
Dim strSQLtxt As String

lblAgent.Caption = Forms("frmCollections")!cboAgent

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:


Private Sub SQLSetup(strSQLtxt As String)
On Error GoTo Err_SQLSetup

Select Case Forms("frmCollections")!optgrpSortByOption.Value
Case 1 'Sort By 0-30

strSQLtxt = "SELECT tblAccounts.a_Account, tblCollectionsInfo.
[CI_Days0-30], " & _
"tblNotes.CN_CtcPerson, tblNotes.CN_CtcPhone, " &
_
"tblNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo
" & _
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.
AcctIDAuto) " & _
"INNER JOIN tblNotes ON tblAccounts.AcctIDAuto =
tblNotes.AcctIDAuto " & _
"WHERE (((tblCollectionsInfo.[CI_Days0-30]) > 0)
" & _
"And ((tblAccounts.Agent) ='" & lblAgent.Caption
& "')) " & _
"ORDER BY tblCollectionsInfo.[CI_Days0-30] DESC;"

Case 2 ‘ Sort by 31-60
Field select the same as case one, only instead of
[CI_Days0-30], I will
Select [CI_Days31-60]
So forth and so on for the rest of 9 cases
End Select

Exit_SQLSetup:
Exit Sub

I have debug the sql string, using "?strSQLtxt" I copy the statement, paste
it into query SQLview and it show the table okay.

When I run the code, only the report header show all the values from form, it
has 20 pages but all details section is blank.
What do I need to do in order to show recordsource (by sql statements) in my
report?

Thanks so much!

Ges

I try to create report from opened form so user can key in the data to
filter

[quoted text clipped - 21 lines]

I've been stuck for a couple of days try to figure out the way to do this.
Any one kindly enough to help me?

There's virtually no information here for us to use to assist you -- roughly
the equivalent to saying "Something went wrong somewhere. How do I fix it?"
For just one example, you call a procedure named "SQLSetup" and pass it an
argument of "strSQLtxt" -- but, unless my eyes are dimmer than I think and
caused me to look right over it, you haven't explained how you "pass over
values" nor what values you pass over from the form (I'd rather expect to
see a reference to the Report's Open Args statement if a recent version of
Access), you didn't tell us what SQL Setup does/is supposed to do, what
information you provided it, what you think strSQLtxt contains when it is
returned, and my psychic powers are at a low ebb just now. you haven't
explained how you "pass over values" nor what values you pass over from the
form.

As a first try at debugging, between the statements calling SQLSetup and
replacing the RecordSource, you might insert a statement of Debug.Print
strSQLtxt, then look in the Immediate Window to see what strSQLtxt actually
contained. Other Debug.Print statments, judiciously placed in the Form from
which you are calling the Report might shed even more light on what's going
wrong and where.

Larry Linson
Microsoft Office Access MVP

 

[ges via AccessMonster.com]

Sorry the strSQLtxt look messy when I click post button, below the strSQLtxt
=

SELECT tblAccounts.a_Account, tblCollectionsInfo.[CI_Days0-30], " & _
"tblNotes.CN_CtcPerson, tblNotes.CN_CtcPhone, " & _
"tblNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo " & _
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.AcctIDAuto) " & _
"INNER JOIN tblNotes ON tblAccounts.AcctIDAuto = tblNotes.AcctIDAuto " & _
"WHERE (((tblCollectionsInfo.[CI_Days0-30]) > 0) " & _
"And ((tblAccounts.Agent) ='" & lblAgent.Caption & "')) " & _
"ORDER BY tblCollectionsInfo.[CI_Days0-30] DESC;"

Ges

Thanks for your reply Larry,

I have opened form called frmCollections, Report call ReportCollections. I
create "view report " button in frmCollections to open Report, code as
follow:

stDocName = "ReportCollections"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

In frmCollections I have combobox called "cboAgent" listed all collection
Agents. When user choose the agent, and click "view report" button it pass
the value of cboAgent from frmCollections to lblAgent in ReportCollections
(Report header section)

In frmCollections I have option group contains 9 options called
"optgrpSortByOption" , user has to choose the option first in frmCollections
before they can click "view Report" button.

Below are my codes (2 events - Report_Open and SQLSetup)

Private Sub Report_Open(Cancel As Integer)
Dim strSQLtxt As String

lblAgent.Caption = Forms("frmCollections")!cboAgent

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:

Private Sub SQLSetup(strSQLtxt As String)
On Error GoTo Err_SQLSetup

Select Case Forms("frmCollections")!optgrpSortByOption.Value
Case 1 'Sort By 0-30

strSQLtxt = "SELECT tblAccounts.a_Account, tblCollectionsInfo.
[CI_Days0-30], " & _
"tblNotes.CN_CtcPerson, tblNotes.CN_CtcPhone, " &
_
"tblNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo
" & _
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.
AcctIDAuto) " & _
"INNER JOIN tblNotes ON tblAccounts.AcctIDAuto =
tblNotes.AcctIDAuto " & _
"WHERE (((tblCollectionsInfo.[CI_Days0-30]) > 0)
" & _
"And ((tblAccounts.Agent) ='" & lblAgent.Caption
& "')) " & _
"ORDER BY tblCollectionsInfo.[CI_Days0-30] DESC;"

Case 2 ‘ Sort by 31-60
Field select the same as case one, only instead of
[CI_Days0-30], I will
Select [CI_Days31-60]
So forth and so on for the rest of 9 cases
End Select

Exit_SQLSetup:
Exit Sub

I have debug the sql string, using "?strSQLtxt" I copy the statement, paste
it into query SQLview and it show the table okay.

When I run the code, only the report header show all the values from form, it
has 20 pages but all details section is blank.
What do I need to do in order to show recordsource (by sql statements) in my
report?

Thanks so much!

Ges
[quoted text clipped - 24 lines]

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

Firstly, let me say that the order of the data in your Report's Record
Source does not determine the order of the data displayed in the Report...
the Report's Sorting and Grouping Properties determine the order in which
the data is displayed. So whatever time and effort you invested in selecting
that and including the ORDER BY clause were for nothing. That's just a
forewarning, though, that it will be a problem later... it does not explain
your not seeing any data at all. (FYI, I _think_ you can change the Sorting
and Grouping properties in the Report Open, but I am not certain, because
I've never had a requirement to do so.

Put a stop in the Detail Section's Print event, and review the data to which
the Controls in the Detail Section refer -- either with Debug.Print or
viewing in the Immediate Window. If the data looks OK, then the only thing
I can think is to check the Visible property of each of the Controls (and
Labels) in the Detail Section, or of the Section itself, or the way you've
specified the Control Source for each of the Controls. A bound Control's
ControlSource should refer to a Field in the Report's Control Source.

Larry Linson
Microsoft Office Access MVP

Sorry the strSQLtxt look messy when I click post button, below the
strSQLtxt
=

SELECT tblAccounts.a_Account, tblCollectionsInfo.[CI_Days0-30], " & _
"tblNotes.CN_CtcPerson, tblNotes.CN_CtcPhone, " & _
"tblNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo " & _
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.AcctIDAuto) " & _
"INNER JOIN tblNotes ON tblAccounts.AcctIDAuto = tblNotes.AcctIDAuto " & _
"WHERE (((tblCollectionsInfo.[CI_Days0-30]) > 0) " & _
"And ((tblAccounts.Agent) ='" & lblAgent.Caption & "')) " & _
"ORDER BY tblCollectionsInfo.[CI_Days0-30] DESC;"

Ges

Thanks for your reply Larry,

I have opened form called frmCollections, Report call ReportCollections.
I
create "view report " button in frmCollections to open Report, code as
follow:

stDocName = "ReportCollections"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

In frmCollections I have combobox called "cboAgent" listed all collection
Agents. When user choose the agent, and click "view report" button it
pass
the value of cboAgent from frmCollections to lblAgent in ReportCollections
(Report header section)

In frmCollections I have option group contains 9 options called
"optgrpSortByOption" , user has to choose the option first in
frmCollections
before they can click "view Report" button.

Below are my codes (2 events - Report_Open and SQLSetup)

Private Sub Report_Open(Cancel As Integer)
Dim strSQLtxt As String

lblAgent.Caption = Forms("frmCollections")!cboAgent

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:

Private Sub SQLSetup(strSQLtxt As String)
On Error GoTo Err_SQLSetup

Select Case Forms("frmCollections")!optgrpSortByOption.Value
Case 1 'Sort By 0-30

strSQLtxt = "SELECT tblAccounts.a_Account, tblCollectionsInfo.
[CI_Days0-30], " & _
"tblNotes.CN_CtcPerson, tblNotes.CN_CtcPhone,
" &
_
"tblNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN
tblCollectionsInfo
" & _
"ON tblAccounts.AcctIDAuto =
tblCollectionsInfo.
AcctIDAuto) " & _
"INNER JOIN tblNotes ON tblAccounts.AcctIDAuto
=
tblNotes.AcctIDAuto " & _
"WHERE (((tblCollectionsInfo.[CI_Days0-30]) >
0)
" & _
"And ((tblAccounts.Agent) ='" &
lblAgent.Caption
& "')) " & _
"ORDER BY tblCollectionsInfo.[CI_Days0-30]
DESC;"

Case 2 ' Sort by 31-60
Field select the same as case one, only instead of
[CI_Days0-30], I will
Select [CI_Days31-60]
So forth and so on for the rest of 9 cases
End Select

Exit_SQLSetup:
Exit Sub

I have debug the sql string, using "?strSQLtxt" I copy the statement,
paste
it into query SQLview and it show the table okay.

When I run the code, only the report header show all the values from form,
it
has 20 pages but all details section is blank.
What do I need to do in order to show recordsource (by sql statements) in
my
report?

Thanks so much!

Ges

I try to create report from opened form so user can key in the data to
filter

[quoted text clipped - 24 lines]

Larry Linson
Microsoft Office Access MVP

 

[ges via AccessMonster.com]

Thanks, Larry.
Order by clause noted - thanks
Immediate windows sql look okay, I have checked the property for each
controls and I did select visible for all, still not sure why the details
section come out blank....

Ges

Firstly, let me say that the order of the data in your Report's Record
Source does not determine the order of the data displayed in the Report...
the Report's Sorting and Grouping Properties determine the order in which
the data is displayed. So whatever time and effort you invested in selecting
that and including the ORDER BY clause were for nothing. That's just a
forewarning, though, that it will be a problem later... it does not explain
your not seeing any data at all. (FYI, I _think_ you can change the Sorting
and Grouping properties in the Report Open, but I am not certain, because
I've never had a requirement to do so.

Put a stop in the Detail Section's Print event, and review the data to which
the Controls in the Detail Section refer -- either with Debug.Print or
viewing in the Immediate Window. If the data looks OK, then the only thing
I can think is to check the Visible property of each of the Controls (and
Labels) in the Detail Section, or of the Section itself, or the way you've
specified the Control Source for each of the Controls. A bound Control's
ControlSource should refer to a Field in the Report's Control Source.

Larry Linson
Microsoft Office Access MVP

Sorry the strSQLtxt look messy when I click post button, below the
strSQLtxt

[quoted text clipped - 106 lines]