Select Sort Order Through a Form

  • Thread starter Thread starter Kathy Webster
  • Start date Start date
K

Kathy Webster

In an MDE database where users don't have rights to edit the report design,
I would like the user to be able to select the sort order of a report in a
combo box on a form.
How would I tell the report that the sort order is, for example, equal to
[forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
 
I'm sorry but that doesn't help me. Perhaps it is too advanced for me. :-(
Can you, or anyone else, be more specific?
Thank you

Duane Hookom said:
You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of a
report in a combo box on a form.
How would I tell the report that the sort order is, for example, equal to
[forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
 
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I'm sorry but that doesn't help me. Perhaps it is too advanced for me. :-(
Can you, or anyone else, be more specific?
Thank you

Duane Hookom said:
You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of a
report in a combo box on a form.
How would I tell the report that the sort order is, for example, equal
to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Duane: I can't get Allen's method, neither.

Here is my design:

Assuming Radio butoon on form "Choose Sort" in option group "grpSort" with
values and captions:
1. ID (primary key) also default value for grpSort
2. Fst Name
3. Lst Name
Also one command button "pvwrpt" to open report "Myreport" in preview view.

Then, the question is: where should I add this following codes???
Select Case Forms!ChooseSort!grpSort
Case 1' ID
Me.GroupLevel(0).ControlSource ="ID"
Me.GroupLevel(1).ControlSource ="Fst Name"
Me.GroupLevel(2).ControlSource ="Lst Name"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="Fst Name"
Me.GroupLevel(1).ControlSource ="ID"
Me.GroupLevel(2).ControlSource ="Lst Name"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="LstName"
Me.GroupLevel(1).ControlSource ="ID"
Me.GroupLevel(2).ControlSource ="Fst Name"
End Select

Also if I want dynamic sorting, Allen suggests me to use the following code
when Report "MyReport" is open:
Dim strDoc As String
strDoc="Myreport"
If IsLoaded(strDoc) Then
DoCmd.Close aReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview
End If
How come IsLoaded is not predefined at the VB???

Thank you for giving me more instructions.....






Follow to Allen's instructions, th code




Duane Hookom said:
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I'm sorry but that doesn't help me. Perhaps it is too advanced for me. :-(
Can you, or anyone else, be more specific?
Thank you

Duane Hookom said:
You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of a
report in a combo box on a form.
How would I tell the report that the sort order is, for example, equal
to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
As the web page states "with code like this in the Open event of the
report:"

Another issue you might have is your use of spaces in field names. Try wrap
your field names in [ ]s
Me.GroupLevel(1).ControlSource ="[Fst Name]"

--
Duane Hookom
MS Access MVP
--

Vivi said:
Duane: I can't get Allen's method, neither.

Here is my design:

Assuming Radio butoon on form "Choose Sort" in option group "grpSort" with
values and captions:
1. ID (primary key) also default value for grpSort
2. Fst Name
3. Lst Name
Also one command button "pvwrpt" to open report "Myreport" in preview
view.

Then, the question is: where should I add this following codes???
Select Case Forms!ChooseSort!grpSort
Case 1' ID
Me.GroupLevel(0).ControlSource ="ID"
Me.GroupLevel(1).ControlSource ="Fst Name"
Me.GroupLevel(2).ControlSource ="Lst Name"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="Fst Name"
Me.GroupLevel(1).ControlSource ="ID"
Me.GroupLevel(2).ControlSource ="Lst Name"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="LstName"
Me.GroupLevel(1).ControlSource ="ID"
Me.GroupLevel(2).ControlSource ="Fst Name"
End Select

Also if I want dynamic sorting, Allen suggests me to use the following
code
when Report "MyReport" is open:
Dim strDoc As String
strDoc="Myreport"
If IsLoaded(strDoc) Then
DoCmd.Close aReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview
End If
How come IsLoaded is not predefined at the VB???

Thank you for giving me more instructions.....






Follow to Allen's instructions, th code




Duane Hookom said:
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I'm sorry but that doesn't help me. Perhaps it is too advanced for me.
:-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of
a
report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal
to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

Duane Hookom said:
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I'm sorry but that doesn't help me. Perhaps it is too advanced for me.
:-(
Can you, or anyone else, be more specific?
Thank you

Duane Hookom said:
You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of a
report in a combo box on a form.
How would I tell the report that the sort order is, for example, equal
to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

Duane Hookom said:
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I'm sorry but that doesn't help me. Perhaps it is too advanced for me.
:-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of a
report in a combo box on a form.
How would I tell the report that the sort order is, for example, equal
to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
I couldn't wait to try it, then I got the flu! I finally tried it, and it
works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in one
place rather than pasting it into the Open Event of 10 reports?

Duane Hookom said:
Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

Duane Hookom said:
What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for me.
:-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of
a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I couldn't wait to try it, then I got the flu! I finally tried it, and it
works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in one
place rather than pasting it into the Open Event of 10 reports?

Duane Hookom said:
Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for me.
:-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order of
a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
I am so new at writing code. Historically I have accomplished everything
through macros and the other direct menu choices. Can you help me with this,
or are you sick of me already? :-)

Duane Hookom said:
I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I couldn't wait to try it, then I got the flu! I finally tried it, and it
works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in one
place rather than pasting it into the Open Event of 10 reports?

Duane Hookom said:
Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for
me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the report
design, I would like the user to be able to select the sort order
of a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Here is somewhat generic code. Better code would probably accept the form
and control name as an argument.

Function SetRptSort(strRptName As String)
Dim rpt As Report
Set rpt = Reports(strRptName)
With rpt
Select Case [Forms]![reportselector]![SortOrder]
Case 1 ' LastName
.GroupLevel(0).ControlSource = "LastName"
.GroupLevel(1).ControlSource = "Title"
.GroupLevel(2).ControlSource = "HireDate"
Case 2 'Title
.GroupLevel(0).ControlSource = "Title"
.GroupLevel(1).ControlSource = "HireDate"
.GroupLevel(2).ControlSource = "LastName"
Case 3 'HireDate
.GroupLevel(0).ControlSource = "HireDate"
.GroupLevel(1).ControlSource = "LastName"
.GroupLevel(2).ControlSource = "Title"
End Select
End With
End Function

You can set the On Open property to:
On Open: =SetRptSort([Name])

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I am so new at writing code. Historically I have accomplished everything
through macros and the other direct menu choices. Can you help me with
this, or are you sick of me already? :-)

Duane Hookom said:
I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I couldn't wait to try it, then I got the flu! I finally tried it, and it
works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in one
place rather than pasting it into the Open Event of 10 reports?

Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for
me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the
report design, I would like the user to be able to select the sort
order of a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Thanks for staying with me on this. Where do I put this function stuff? Do I
make a module?
And is the OnOpen property On Open: =SetRptSort([Name]) what calls and
executes this function in a module?


Duane Hookom said:
Here is somewhat generic code. Better code would probably accept the form
and control name as an argument.

Function SetRptSort(strRptName As String)
Dim rpt As Report
Set rpt = Reports(strRptName)
With rpt
Select Case [Forms]![reportselector]![SortOrder]
Case 1 ' LastName
.GroupLevel(0).ControlSource = "LastName"
.GroupLevel(1).ControlSource = "Title"
.GroupLevel(2).ControlSource = "HireDate"
Case 2 'Title
.GroupLevel(0).ControlSource = "Title"
.GroupLevel(1).ControlSource = "HireDate"
.GroupLevel(2).ControlSource = "LastName"
Case 3 'HireDate
.GroupLevel(0).ControlSource = "HireDate"
.GroupLevel(1).ControlSource = "LastName"
.GroupLevel(2).ControlSource = "Title"
End Select
End With
End Function

You can set the On Open property to:
On Open: =SetRptSort([Name])

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I am so new at writing code. Historically I have accomplished everything
through macros and the other direct menu choices. Can you help me with
this, or are you sick of me already? :-)

Duane Hookom said:
I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

I couldn't wait to try it, then I got the flu! I finally tried it, and
it works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in
one place rather than pasting it into the Open Event of 10 reports?

Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these values
correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for
me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the
report design, I would like the user to be able to select the
sort order of a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Put the code in a general module and make sure you save the module with a
name like 'modReportCode'. The function call in the event property will run
the function.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Thanks for staying with me on this. Where do I put this function stuff? Do
I make a module?
And is the OnOpen property On Open: =SetRptSort([Name]) what calls and
executes this function in a module?


Duane Hookom said:
Here is somewhat generic code. Better code would probably accept the form
and control name as an argument.

Function SetRptSort(strRptName As String)
Dim rpt As Report
Set rpt = Reports(strRptName)
With rpt
Select Case [Forms]![reportselector]![SortOrder]
Case 1 ' LastName
.GroupLevel(0).ControlSource = "LastName"
.GroupLevel(1).ControlSource = "Title"
.GroupLevel(2).ControlSource = "HireDate"
Case 2 'Title
.GroupLevel(0).ControlSource = "Title"
.GroupLevel(1).ControlSource = "HireDate"
.GroupLevel(2).ControlSource = "LastName"
Case 3 'HireDate
.GroupLevel(0).ControlSource = "HireDate"
.GroupLevel(1).ControlSource = "LastName"
.GroupLevel(2).ControlSource = "Title"
End Select
End With
End Function

You can set the On Open property to:
On Open: =SetRptSort([Name])

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
I am so new at writing code. Historically I have accomplished everything
through macros and the other direct menu choices. Can you help me with
this, or are you sick of me already? :-)

I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

I couldn't wait to try it, then I got the flu! I finally tried it, and
it works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in
one place rather than pasting it into the Open Event of 10 reports?

Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these
values correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced for
me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the
report design, I would like the user to be able to select the
sort order of a report in a combo box on a form.
How would I tell the report that the sort order is, for example,
equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Thanks again, Duane. In general, is it better to have 1 module with lots of
functions in it, or lots of modules with a small number of functions in it?

Duane Hookom said:
Put the code in a general module and make sure you save the module with a
name like 'modReportCode'. The function call in the event property will
run the function.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Thanks for staying with me on this. Where do I put this function stuff?
Do I make a module?
And is the OnOpen property On Open: =SetRptSort([Name]) what calls and
executes this function in a module?


Duane Hookom said:
Here is somewhat generic code. Better code would probably accept the
form and control name as an argument.

Function SetRptSort(strRptName As String)
Dim rpt As Report
Set rpt = Reports(strRptName)
With rpt
Select Case [Forms]![reportselector]![SortOrder]
Case 1 ' LastName
.GroupLevel(0).ControlSource = "LastName"
.GroupLevel(1).ControlSource = "Title"
.GroupLevel(2).ControlSource = "HireDate"
Case 2 'Title
.GroupLevel(0).ControlSource = "Title"
.GroupLevel(1).ControlSource = "HireDate"
.GroupLevel(2).ControlSource = "LastName"
Case 3 'HireDate
.GroupLevel(0).ControlSource = "HireDate"
.GroupLevel(1).ControlSource = "LastName"
.GroupLevel(2).ControlSource = "Title"
End Select
End With
End Function

You can set the On Open property to:
On Open: =SetRptSort([Name])

--
Duane Hookom
MS Access MVP
--

I am so new at writing code. Historically I have accomplished everything
through macros and the other direct menu choices. Can you help me with
this, or are you sick of me already? :-)

I expect you could create a generic function that would pass either the
report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

I couldn't wait to try it, then I got the flu! I finally tried it, and
it works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in
one place rather than pasting it into the Open Event of 10 reports?

Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these
values correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced
for me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the
report design, I would like the user to be able to select the
sort order of a report in a combo box on a form.
How would I tell the report that the sort order is, for
example, equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
I don't think it makes a lot of difference. Organize them like you would
folders on your hard-drive. I would place all my business rules calculations
in a single module. Whatever works for you is the best solution.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Thanks again, Duane. In general, is it better to have 1 module with lots
of functions in it, or lots of modules with a small number of functions in
it?

Duane Hookom said:
Put the code in a general module and make sure you save the module with a
name like 'modReportCode'. The function call in the event property will
run the function.

--
Duane Hookom
MS Access MVP
--

Kathy Webster said:
Thanks for staying with me on this. Where do I put this function stuff?
Do I make a module?
And is the OnOpen property On Open: =SetRptSort([Name]) what calls and
executes this function in a module?


Here is somewhat generic code. Better code would probably accept the
form and control name as an argument.

Function SetRptSort(strRptName As String)
Dim rpt As Report
Set rpt = Reports(strRptName)
With rpt
Select Case [Forms]![reportselector]![SortOrder]
Case 1 ' LastName
.GroupLevel(0).ControlSource = "LastName"
.GroupLevel(1).ControlSource = "Title"
.GroupLevel(2).ControlSource = "HireDate"
Case 2 'Title
.GroupLevel(0).ControlSource = "Title"
.GroupLevel(1).ControlSource = "HireDate"
.GroupLevel(2).ControlSource = "LastName"
Case 3 'HireDate
.GroupLevel(0).ControlSource = "HireDate"
.GroupLevel(1).ControlSource = "LastName"
.GroupLevel(2).ControlSource = "Title"
End Select
End With
End Function

You can set the On Open property to:
On Open: =SetRptSort([Name])

--
Duane Hookom
MS Access MVP
--

I am so new at writing code. Historically I have accomplished
everything through macros and the other direct menu choices. Can you
help me with this, or are you sick of me already? :-)

I expect you could create a generic function that would pass either
the report object or the report name as an argument.

--
Duane Hookom
MS Access MVP
--

I couldn't wait to try it, then I got the flu! I finally tried it,
and it works like a charm!!!
Thank you, Duane!
Since I want this same code in 10 reports, how can I put the code in
one place rather than pasting it into the Open Event of 10 reports?

Select Case [forms]![reportselector]![sortorder]
Case 1' ID
Me.GroupLevel(0).ControlSource ="FileName"
Me.GroupLevel(1).ControlSource ="FileNumber"
Me.GroupLevel(2).ControlSource ="DateOpened"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="FileNumber"
Me.GroupLevel(1).ControlSource ="DateOpened"
Me.GroupLevel(2).ControlSource ="FileName"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="DateOpened"
Me.GroupLevel(1).ControlSource ="FileName"
Me.GroupLevel(2).ControlSource ="FileNumber"
End Select

Make sure you have predefined 3 grouping levels in your report.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

1 = sort by FileName
2 = sort by FileNumber
3 = sort by DateOpened

Thanks again,
Kathy

What are the values in your combo box and what field do these
values correspond with?

--
Duane Hookom
MS Access MVP
--

I'm sorry but that doesn't help me. Perhaps it is too advanced
for me. :-(
Can you, or anyone else, be more specific?
Thank you

You might want to check the tip on Allen Browne's site
http://allenbrowne.com/ser-33.html.

--
Duane Hookom
MS Access MVP
--

In an MDE database where users don't have rights to edit the
report design, I would like the user to be able to select the
sort order of a report in a combo box on a form.
How would I tell the report that the sort order is, for
example, equal to [forms]![reportselector]![sortorder] ?

Thanks in advance,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top