OpenReport from User Input Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tricia Young - 15 Dec 2005 17:30 GMT

have a Access 2003 Database that is also used by 2000 and 2002 users. I
have report that I would like to make available for my teammembers.
Teammembers would like to have the ability to sort by one or more fields.
There are five fields i.e. SN#, CustomerName, SalesRep, SalesArea, & Status.
My idea was that I could create a form that they would open and maybe select
one or more of the fields for example they could select sort by
SalesRep/Asending, then sort by Status, then by CustomerName. Then select
report from Drop Down Menu. Then click okay to openreport for preview. The
report will reflect the choices made by user.

OR

Maybe from the form select the report and a list of fields show up in 5
combo boxes i.e. Sort first by.... Then by....
Then by.....

Then the user click Preview Report command button and the reports open.

Reply to this Message

Marshall Barton - 15 Dec 2005 19:49 GMT

> have a Access 2003 Database that is also used by 2000 and 2002 users. I
>have report that I would like to make available for my teammembers.
[quoted text clipped - 13 lines]
>
>Then the user click Preview Report command button and the reports open.

To do this, you need to use the report's Sorting and
Grouping (View menu) to precreate enough levels of sorting
for you to modify the the report is opened. The code in the
report's Open event would look like:

Me.GroupLevel(0).ControlSource = Forms!theform.sort1textbox
Me.GroupLevel(1).ControlSource = Forms!theform.sort2textbox
Me.GroupLevel(2).ControlSource = Forms!theform.sort3textbox
Me.GroupLevel(3).ControlSource = Forms!theform.sort4textbox
Me.GroupLevel(4).ControlSource = Forms!theform.sort5textbox

Signature

Marsh
MVP [MS Access]

Reply to this Message

Tricia Young - 15 Dec 2005 21:41 GMT

Thanks, this was very helpful. However, what do I do about null values
(empty text boxes). When the report opens and the code runs I get an error
"Invalid use of Null".

How do I get the code to over look the null values and move on?

Thanks,

Tricia

> > have a Access 2003 Database that is also used by 2000 and 2002 users. I
> >have report that I would like to make available for my teammembers.
[quoted text clipped - 24 lines]
> Me.GroupLevel(3).ControlSource = Forms!theform.sort4textbox
> Me.GroupLevel(4).ControlSource = Forms!theform.sort5textbox

Reply to this Message

Marshall Barton - 15 Dec 2005 23:11 GMT

The text boxes on the form that are not being used can be
set to any constant expression such as =1. Just to be
clear, the code in the form would look like:
Me.sort5textbox = "=1"

Signature

Marsh
MVP [MS Access]

>Thanks, this was very helpful. However, what do I do about null values
>(empty text boxes). When the report opens and the code runs I get an error
[quoted text clipped - 30 lines]
>> Me.GroupLevel(3).ControlSource = Forms!theform.sort4textbox
>> Me.GroupLevel(4).ControlSource = Forms!theform.sort5textbox

Reply to this Message