Sales Repot Pipeline Template

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

foxfinance - 27 Mar 2008 16:38 GMT

In Access 2007, there is a local template called "Sales Pipeline." When you
open it and click on the "Customer List" Form, you will see a Report combo
box toeards the right. CLicking on the drop-arrow will reveal the list of ALL
reports available and these reports are autmtically updated whenever you
select one of the forms from the list.
I'm not as experienced in using Access as most of you are, but I can
sometimes figure out how things like this was done by looking at it in the
design view. However, this is way beyond my 3 hour college course that I
recently took and I was hoping that someone could explain to me - in laymans
terms - how this is done. I'd like to create a list using the combo box that
will show each report in my datatbase and when I choose a specific report, it
be updated based on the information that I put into the tables.

One thing that stands out in this is, in the design view for the combo box,
under the "data" tab, in "row source" it says: SELECT MSysObjects.Name FROM
MSysObjects WHERE (((MSysObjects.Name) Not Like "*Subreport*") AND
((MSysObjects.Type)=-32764))"

This is wayyyyy beyond my skill so if someone can point me in the "start
here" direction, I'd appreciate it.

Thanks everyone.

Reply to this Message

Evi - 30 Mar 2008 16:28 GMT

Sorry about the HTML, I don't want the rows to break up
You've just given us the answer (which works just as well in Access2000, thank you very much!)
Put a combo box in your form. When the wizard starts, just click Cancel.
Click on Properties,
Next to RowSource paste the following

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "*Subreport*") AND ((MSysObjects.Type)=-32764))

(It should be 1 long line so past it into Word first and check it)

It lists all the Reports in the database except for those which contain the word Subreport in the name -

that's the Not Like "*Subreport*" bit.

If, like me, you just call your subreports xxxsub then change that to say

Not Like "*Sub*"

and avoid using the letters sub in your main report's names.

Evi

> In Access 2007, there is a local template called "Sales Pipeline." When you
> open it and click on the "Customer List" Form, you will see a Report combo
[quoted text clipped - 18 lines]
>
> Thanks everyone.

Reply to this Message

Evi - 30 Mar 2008 16:58 GMT

Forgot to answer your second requirement.
when I choose a specific report, it

> be updated based on the information that I put into the tables

That happens anyway. As soon as you put info into your tables your reports
are automatically updated.

If you mean that you want to open your report then use Properties on the
Other tab to name your report cboReports
On the Events tab, click next to AfterUpdate and choose Event Procedure.
Click just right of that to open a code page
Above the line which says

End Sub

type

DoCmd.OpenReport Me.cboReports

Or do you mean that you are typing your data directly into your table and
you want to have your report automatically update as you type, before you
close your table?

Evi

Sorry about the HTML, I don't want the rows to break up
You've just given us the answer (which works just as well in Access2000,
thank you very much!)
Put a combo box in your form. When the wizard starts, just click Cancel.
Click on Properties,
Next to RowSource paste the following

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like
"*Subreport*") AND ((MSysObjects.Type)=-32764))

(It should be 1 long line so past it into Word first and check it)

It lists all the Reports in the database except for those which contain the
word Subreport in the name -

that's the Not Like "*Subreport*" bit.

If, like me, you just call your subreports xxxsub then change that to say

Not Like "*Sub*"

and avoid using the letters sub in your main report's names.

Evi

Reply to this Message