HELP--how do you select fields show in report based on opened form?

[ges]

Hi,
I try to create report (ReportCollection) from 3 tables:
1. tblCollections (has 16 fields)
2. tblAgent (has 6 fields)
3. tblNotes (has 23 fields)
I created a query that contains all fields in this 3 tables. (total field 45
fiels)
I have a loaded/open form call frmCollection. User will key in information
in frmCollection which contains: cboAgent, optgrpFileType, Bill Cycle
parameter, and 9 options to sort the report (optSort).
I create a "view report" button so on click even will open ReportCollection.

In the reportCollection design view, on property control I pass the value
from frmCollection to ReportCollection for example:
=[Forms].[frmCollectionsDashboard].[txtFromBillCycle]

On ReportCollection detail section, I only want to show related information
around 4 to 6 fields filtered based on frmCollection.

QUESTIONS:
1. How do I select fields for each sort?
I try to use sqlSetup, then Me.Recordsource = sql Setup (contains sql
statements for each case)
but the details section show blank. (I've use debug.print sql and sql
statement is correct)

2. In details section do I need to create unbound textboxes? or what do I
need to do?

I greatly appreciate any help, thanks in advance.

Ges

 

[Jeff Boyce]

Uhm...

You have a tblNotes with 23 fields?

Most "notes"-related tables have 3 or 4 fields, maximum (ID, Author,
NoteDateTime, Note). There's a chance that your tblNotes table is not
well-normalized (but I can't tell because I tell what you are storing in
it).

Who cares?, you ask? You care! MS Access is designed to work with
well-normalized relational data. If you are feeding it spreadsheet data,
both you and Access will have to work much harder to do things that are
simple for well-structured data.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ges via AccessMonster.com]

Hi Jeff,
Thanks for the reply. Yes believe it or not but I do have 23 fields in table
Notes. My client is collections company, we are using table notes to store
all the actions and dates taken by collector.
so of course I can't put all those fields in the report. So my client would
like the option to select fields that will show in report based on the
selection in the open form. I never do report thru vba, this if my first time.
..Can you help?

Thanks,
Ges

Uhm...

You have a tblNotes with 23 fields?

Most "notes"-related tables have 3 or 4 fields, maximum (ID, Author,
NoteDateTime, Note). There's a chance that your tblNotes table is not
well-normalized (but I can't tell because I tell what you are storing in
it).

Who cares?, you ask? You care! MS Access is designed to work with
well-normalized relational data. If you are feeding it spreadsheet data,
both you and Access will have to work much harder to do things that are
simple for well-structured data.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I try to create report (ReportCollection) from 3 tables:

[quoted text clipped - 32 lines]

Ges

 

[Jeff Boyce]

I'm still not convinced that the table is well-normalized. It seems to me
that you could use a single field to hold an "action", a single field to
hold a "collector", and a single field to hold "date action was taken", plus
maybe a rowID field.

If you'll provide a bit more information about the kind of data that is
being stored in these fields, we may be able to offer an easier way. I'm
not trying to be difficult, just pointing out that if your data isn't
structured the way Access expects it to be, you (and Access) will have to
work MUCH harder than you othewise would.

For example, I can use my chainsaw to drive nails ... but it isn't pretty or
safe or the right tool for the job...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
Thanks for the reply. Yes believe it or not but I do have 23 fields in
table
Notes. My client is collections company, we are using table notes to
store
all the actions and dates taken by collector.
so of course I can't put all those fields in the report. So my client
would
like the option to select fields that will show in report based on the
selection in the open form. I never do report thru vba, this if my first
time.
.Can you help?

Thanks,
Ges

Uhm...

You have a tblNotes with 23 fields?

Most "notes"-related tables have 3 or 4 fields, maximum (ID, Author,
NoteDateTime, Note). There's a chance that your tblNotes table is not
well-normalized (but I can't tell because I tell what you are storing in
it).

Who cares?, you ask? You care! MS Access is designed to work with
well-normalized relational data. If you are feeding it spreadsheet data,
both you and Access will have to work much harder to do things that are
simple for well-structured data.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I try to create report (ReportCollection) from 3 tables:

[quoted text clipped - 32 lines]

Ges

 

[ges via AccessMonster.com]

Hi Jeff,
In the tblNotes I have fields as follow:
Contact person, contact phone, (letter 1, letter 2, outbound call 1, outbound
call 2, outbound call 3, inbound call 1, inbound call 2, next follow up due,
updated date, hard disc, soft disc - all these fields store the date action
performed), last payment date, last payment amount, payment arrangement,
promise to pay, legal action, manager comment and collector notes, IDAuto,
batch number, balance

In table Collection I have fields such as : total due, total over 30, total
over 60, 0-30, 31-60, 61-90, 91-120, >120, bill cycle, IDAuto (for
relationship with other table), batch no, sales, status, Due date

In table Collector I have IDAuto, Agent, Account no, File Type, Customer Name,
accountKey

The project almost complete using all these table structures, the only one
left is the report.

I usually design the report using wizard tool. I select the rowsource from
table and it’s field. But I try to do it programmatically so the opened form
will filter the field being displayed in the report.

For example (Collection Form) has criteria as follow:
1. choose file type (3 different file type – using option group))
2. choose Agent (combobox)
3. Choose sort type (current, 30,60, 90, >120, ttl due, follow up due,
account, action)

Based on user selection from Collection Form, I try to create a report using
SQL statement that select the field.

For example, user choose file type A, Agent, Current)
The field I like to display in report:
Account No, current (0-30), Customer name, Contact phone, contact person,
Notes,
Total 6 fields.

If user choose sort by 60, all column in report will be the same except the
second one will show (61-90) instead of (0-30)

Below is my code:

Public Sub cmdbtnViewReport_Click()
On Error GoTo Err_cmdbtnViewReport_Click
Dim stDocName As String
Dim strWhere As String
Dim Errorno As String
Dim errormsg As String

stDocName = "ReportCollectionsDashboard"
'strWhere = "[txtAgent] = """ & Me.[cboAgent] & """"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_cmdbtnViewReport_Click:
Exit Sub


Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim Errorno As String
Dim errormsg As String

Dim strSQLtxt As String


lblTitle.Caption = Forms("frmCollections")!lblInfo.Caption
lblReportBillCycle.Caption = Forms("frmCollections").lblBillCycle.Caption
lblReportTo.Caption = Forms("frmCollections").lblTo.Caption
lblAgent.Caption = Forms("frmCollectionsDashboard")!cboAgent

If Forms("frmCollections")!lblBillCycle.Visible = True Then
lblReportBillCycle.Visible = True
lblReportTo.Visible = True
Else
lblReportBillCycle.Visible = False
lblReportTo.Visible = False
End If

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:



Private Sub SQLSetup(strSQLtxt As String)
On Error GoTo Err_ArborArrivalSQLSetup
Dim Errorno As String
Dim errormsg As String

Select Case Forms("frmCollectionsDashboard")!optgrpSortByOption.Value
Case 1 'Sort By 0-30
strSQLtxt = "SELECT tblAccounts.a_Account, tblAccounts.a_BillName,
" & _
"tblCollectionsInfo.[CI_Days0-30], & _
"tblCollectorNotes.CN_PersonContacted, " & _

"tblCollectorNotes.CN_ContactPhone, " & _
"tblCollectorNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo " &
_
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.
AcctIDAuto) " & _
"INNER JOIN tblCollectorNotes ON tblAccounts.
AcctIDAuto = " & _
"tblCollectorNotes.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
' where will be [CI_Days31-60]
End Select

Exit_SQLSetup:
Exit Sub


I have debug the strSQLtxt statement in the immediate windows, paste it to
query the table show up okay, so there is no problem with the statement.

But when I run it, it doesn’t give me any error, only the detail section
blank, nothing show up.

My question is:
1. When I use the sql statement for the recordsource, do I have to create
unbound text boxes in the details section ( I tried this but it’s blank also)
2. What do I miss or did not do?
3. I read some of the post said ORDER BY clause is ignored in report, how do
I select the sorting automatically when the report open?


Thank you.

Ges

I'm still not convinced that the table is well-normalized. It seems to me
that you could use a single field to hold an "action", a single field to
hold a "collector", and a single field to hold "date action was taken", plus
maybe a rowID field.

If you'll provide a bit more information about the kind of data that is
being stored in these fields, we may be able to offer an easier way. I'm
not trying to be difficult, just pointing out that if your data isn't
structured the way Access expects it to be, you (and Access) will have to
work MUCH harder than you othewise would.

For example, I can use my chainsaw to drive nails ... but it isn't pretty or
safe or the right tool for the job...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
Thanks for the reply. Yes believe it or not but I do have 23 fields in

[quoted text clipped - 38 lines]

 

[Jeff Boyce]

I truly believe that you are finding the reporting much more difficult than
it needs to be because your data is not in the structure Microsoft Access
expects. Both you and Access are working much harder to try to work around
the data structure you have.

For example, from what you described in your Notes table, you have a series
of possible actions ("letter 1, letter 2, inbound 1, inbound 2, ...), each
with (potentially) a date/time value. This is exactly what you'd see if you
were using a spreadsheet. And unless every single Note record included ALL
of these, you'd have empty date/time fields (i.e., some actions not
performed, some actions not performed yet, ...).

I'm sure there's a way to work around the structure you have, but I don't
have experience doing that -- perhaps other newsgroup readers can help out
here. It looks like a "pay now or pay later" situation to me. Sure, you
may be able to work around the non-normalized structure you have this time
.... and what happens next time, when you want to do something slightly
different?

For example, what happens when you add a new action that can be done (e.g.,
Send followup Email #1)? You'll have to change your table structure, change
your queries, change your forms, change your reports, change ... -- what a
maintenance headache!

Or you could adopt a well-normalized table structure and the only thing
you'd need to do is add the new action to your tlkpAction table! That's it!
Your table structure stays the same, your queries stay the same, your
reports and forms stay the same, ...

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
In the tblNotes I have fields as follow:
Contact person, contact phone, (letter 1, letter 2, outbound call 1,
outbound
call 2, outbound call 3, inbound call 1, inbound call 2, next follow up
due,
updated date, hard disc, soft disc - all these fields store the date
action
performed), last payment date, last payment amount, payment arrangement,
promise to pay, legal action, manager comment and collector notes, IDAuto,
batch number, balance

In table Collection I have fields such as : total due, total over 30,
total
over 60, 0-30, 31-60, 61-90, 91-120, >120, bill cycle, IDAuto (for
relationship with other table), batch no, sales, status, Due date

In table Collector I have IDAuto, Agent, Account no, File Type, Customer
Name,
accountKey

The project almost complete using all these table structures, the only one
left is the report.

I usually design the report using wizard tool. I select the rowsource
from
table and it's field. But I try to do it programmatically so the opened
form
will filter the field being displayed in the report.

For example (Collection Form) has criteria as follow:
1. choose file type (3 different file type - using option group))
2. choose Agent (combobox)
3. Choose sort type (current, 30,60, 90, >120, ttl due, follow up due,
account, action)

Based on user selection from Collection Form, I try to create a report
using
SQL statement that select the field.

For example, user choose file type A, Agent, Current)
The field I like to display in report:
Account No, current (0-30), Customer name, Contact phone, contact person,
Notes,
Total 6 fields.

If user choose sort by 60, all column in report will be the same except
the
second one will show (61-90) instead of (0-30)

Below is my code:

Public Sub cmdbtnViewReport_Click()
On Error GoTo Err_cmdbtnViewReport_Click
Dim stDocName As String
Dim strWhere As String
Dim Errorno As String
Dim errormsg As String

stDocName = "ReportCollectionsDashboard"
'strWhere = "[txtAgent] = """ & Me.[cboAgent] & """"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_cmdbtnViewReport_Click:
Exit Sub


Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim Errorno As String
Dim errormsg As String

Dim strSQLtxt As String


lblTitle.Caption = Forms("frmCollections")!lblInfo.Caption
lblReportBillCycle.Caption =
Forms("frmCollections").lblBillCycle.Caption
lblReportTo.Caption = Forms("frmCollections").lblTo.Caption
lblAgent.Caption = Forms("frmCollectionsDashboard")!cboAgent

If Forms("frmCollections")!lblBillCycle.Visible = True Then
lblReportBillCycle.Visible = True
lblReportTo.Visible = True
Else
lblReportBillCycle.Visible = False
lblReportTo.Visible = False
End If

SQLSetup strSQLtxt

Me.RecordSource = strSQLtxt

Exit_Report_Open:



Private Sub SQLSetup(strSQLtxt As String)
On Error GoTo Err_ArborArrivalSQLSetup
Dim Errorno As String
Dim errormsg As String

Select Case Forms("frmCollectionsDashboard")!optgrpSortByOption.Value
Case 1 'Sort By 0-30
strSQLtxt = "SELECT tblAccounts.a_Account,
tblAccounts.a_BillName,
" & _
"tblCollectionsInfo.[CI_Days0-30], & _
"tblCollectorNotes.CN_PersonContacted, " & _

"tblCollectorNotes.CN_ContactPhone, " & _
"tblCollectorNotes.CN_Notes " & _
"FROM (tblAccounts INNER JOIN tblCollectionsInfo "
&
_
"ON tblAccounts.AcctIDAuto = tblCollectionsInfo.
AcctIDAuto) " & _
"INNER JOIN tblCollectorNotes ON tblAccounts.
AcctIDAuto = " & _
"tblCollectorNotes.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
' where will be [CI_Days31-60]
End Select

Exit_SQLSetup:
Exit Sub


I have debug the strSQLtxt statement in the immediate windows, paste it
to
query the table show up okay, so there is no problem with the statement.

But when I run it, it doesn't give me any error, only the detail section
blank, nothing show up.

My question is:
1. When I use the sql statement for the recordsource, do I have to create
unbound text boxes in the details section ( I tried this but it's blank
also)
2. What do I miss or did not do?
3. I read some of the post said ORDER BY clause is ignored in report, how
do
I select the sorting automatically when the report open?


Thank you.

Ges

I'm still not convinced that the table is well-normalized. It seems to me
that you could use a single field to hold an "action", a single field to
hold a "collector", and a single field to hold "date action was taken",
plus
maybe a rowID field.

If you'll provide a bit more information about the kind of data that is
being stored in these fields, we may be able to offer an easier way. I'm
not trying to be difficult, just pointing out that if your data isn't
structured the way Access expects it to be, you (and Access) will have to
work MUCH harder than you othewise would.

For example, I can use my chainsaw to drive nails ... but it isn't pretty
or
safe or the right tool for the job...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
Thanks for the reply. Yes believe it or not but I do have 23 fields in

[quoted text clipped - 38 lines]

Ges