Report based on parameter form

[gmazza via AccessMonster.com]

Hi there,
I have a parameter form that I enter criteria into, that being fields from a
table in a drop down box.
When the user chooses a field from the drop down, hits the Run Report button,
I want it to come up with a report grouped and totalled by the field they
chose.
I can do this with a report for each field, but is there a way I can do this
with only 1 report, and then whatever field is chosen by the user, tyhe
report knows to group it by that field cause it was somehow passed in?

To clarify a bit, the user chooses a field name, but I need that field name
grouped in the report, but the report needs to be dynamic for any field.
Thanks for your help in advance!

 

[Marshall Barton]

I have a parameter form that I enter criteria into, that being fields from a
table in a drop down box.
When the user chooses a field from the drop down, hits the Run Report button,
I want it to come up with a report grouped and totalled by the field they
chose.
I can do this with a report for each field, but is there a way I can do this
with only 1 report, and then whatever field is chosen by the user, tyhe
report knows to group it by that field cause it was somehow passed in?

To clarify a bit, the user chooses a field name, but I need that field name
grouped in the report, but the report needs to be dynamic for any field.

Create a group for any field, then use code in the report's
Open event to change it to the user selected field:

Me.GroupLevel(0).ControlSource = Forms!theform.thecombobox
Me.thegroupheadertextbox.ControlsSource =
Forms!theform.thecombobox

 

[gmazza via AccessMonster.com]

Ok so I grouped on a field, any one like you said.
I then set the control source of the text box under the group to:
[Forms]![SelectFieldDateRange]![cboChoice]

Then, in the Open event of the report I put:
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice

Is that right?

It didn't work, I got prompted for a parameter when I ran the report, after
putting in a field name in the combo box.

I have a parameter form that I enter criteria into, that being fields from a
table in a drop down box.

[quoted text clipped - 7 lines]

To clarify a bit, the user chooses a field name, but I need that field name
grouped in the report, but the report needs to be dynamic for any field.

Create a group for any field, then use code in the report's
Open event to change it to the user selected field:

Me.GroupLevel(0).ControlSource = Forms!theform.thecombobox
Me.thegroupheadertextbox.ControlsSource =
Forms!theform.thecombobox

 

[gmazza via AccessMonster.com]

Anyone else have any advice on this?

Ok so I grouped on a field, any one like you said.
I then set the control source of the text box under the group to:
[Forms]![SelectFieldDateRange]![cboChoice]

Then, in the Open event of the report I put:
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice

Is that right?

It didn't work, I got prompted for a parameter when I ran the report, after
putting in a field name in the combo box.
[quoted text clipped - 8 lines]

Me.thegroupheadertextbox.ControlsSource =
Forms!theform.thecombobox

 

[Marshall Barton]

Only half right. You need the second line of code in the
Open event that I posted to make the text box display the
value of the grouping field.

Without knowing what you were prompted to enter, I can't
tell why the prompt appeared.
--
Marsh
MVP [MS Access]

Ok so I grouped on a field, any one like you said.
I then set the control source of the text box under the group to:
[Forms]![SelectFieldDateRange]![cboChoice]

Then, in the Open event of the report I put:
Me.GroupLevel(0).ControlSource = Forms!SelectFieldDateRange.cboChoice

Is that right?

It didn't work, I got prompted for a parameter when I ran the report, after
putting in a field name in the combo box.

I have a parameter form that I enter criteria into, that being fields from a
table in a drop down box.

[quoted text clipped - 7 lines]

To clarify a bit, the user chooses a field name, but I need that field name
grouped in the report, but the report needs to be dynamic for any field.

Create a group for any field, then use code in the report's
Open event to change it to the user selected field:

Me.GroupLevel(0).ControlSource = Forms!theform.thecombobox
Me.thegroupheadertextbox.ControlsSource =
Forms!theform.thecombobox

 

[gmazza via AccessMonster.com]

I guess I'm not understanding what you mean by what you want and where.
So in the Open Event of the report I have:
Me.Username.ControlSource = Forms!SelectFieldDateRange.cboChoice

where Username is the name of the textbox that is being grouped, and
cboChoice is the name of the combo box on the form that the field is being
chosen from.
The 2 choices in the combo box are username and formname.
When I choose Username, the report shows up correctly, but I believe that is
because the grouping is on Username. When I choose Form from the combo box, a
prompt comes up that is named Form, and it wants me to enter something.
Hope that gives you the proper info you need.
Thanks!

Only half right. You need the second line of code in the
Open event that I posted to make the text box display the
value of the grouping field.

Without knowing what you were prompted to enter, I can't
tell why the prompt appeared.

Ok so I grouped on a field, any one like you said.
I then set the control source of the text box under the group to:

[quoted text clipped - 20 lines]

 

[Marshall Barton]

Do you still have the line that sets the GroupLevel's
ControlSource too?

When you are prompted for something, it means that the
prompt is not a field in the report's record source
table/query. Are you sure that Form is a field in your
table?

Whether it is or not, Form is a very poor choice of a name
because it is a reserved word.
--
Marsh
MVP [MS Access]

I guess I'm not understanding what you mean by what you want and where.
So in the Open Event of the report I have:
Me.Username.ControlSource = Forms!SelectFieldDateRange.cboChoice

where Username is the name of the textbox that is being grouped, and
cboChoice is the name of the combo box on the form that the field is being
chosen from.
The 2 choices in the combo box are username and formname.
When I choose Username, the report shows up correctly, but I believe that is
because the grouping is on Username. When I choose Form from the combo box, a
prompt comes up that is named Form, and it wants me to enter something.

Only half right. You need the second line of code in the
Open event that I posted to make the text box display the
value of the grouping field.

Without knowing what you were prompted to enter, I can't
tell why the prompt appeared.

Ok so I grouped on a field, any one like you said.
I then set the control source of the text box under the group to:

[quoted text clipped - 20 lines]

Me.thegroupheadertextbox.ControlsSource =
Forms!theform.thecombobox

 

[gmazza via AccessMonster.com]

Yes I still have the line that sets the group level.
You are correct, I thought it was Form, its actually called FormName.
The report is now showing, but I need a text field in the report to show if
FormName is chosen and a different field to show if Username is shown.
So if Username is chosen, it is grouped by Username, and I need the FormName
to show for each Username.
If FormName is chosen, it is grouped by FormName, and I need Username to show
for each FormName in the table.

I got the label working by checking to see what the control source is of the
grouping field, then if its FormName, I get the label's caption to be
Username like so:
Label7.Caption = "Username"
But the field, I called txtChange under this caption is not working.
I tried this:
txtChange = Nz(DLookup("Username", "ErrorLog", "[FormName] = " & txtFieldName.
Value))

where txtFieldName is the name of my grouping field but that doesn't work,
and I see how it doesn't make sense. I need to get the record for the current
FormName, and return the Username.
Can you see what I would need here?
Thanks again for your help so far, I'm almost there.

Do you still have the line that sets the GroupLevel's
ControlSource too?

When you are prompted for something, it means that the
prompt is not a field in the report's record source
table/query. Are you sure that Form is a field in your
table?

Whether it is or not, Form is a very poor choice of a name
because it is a reserved word.

I guess I'm not understanding what you mean by what you want and where.
So in the Open Event of the report I have:

[quoted text clipped - 19 lines]

 

[Marshall Barton]

Yes I still have the line that sets the group level.
You are correct, I thought it was Form, its actually called FormName.
The report is now showing, but I need a text field in the report to show if
FormName is chosen and a different field to show if Username is shown.
So if Username is chosen, it is grouped by Username, and I need the FormName
to show for each Username.
If FormName is chosen, it is grouped by FormName, and I need Username to show
for each FormName in the table.

I got the label working by checking to see what the control source is of the
grouping field, then if its FormName, I get the label's caption to be
Username like so:
Label7.Caption = "Username"
But the field, I called txtChange under this caption is not working.
I tried this:
txtChange = Nz(DLookup("Username", "ErrorLog", "[FormName] = " & txtFieldName.
Value))

where txtFieldName is the name of my grouping field but that doesn't work,
and I see how it doesn't make sense. I need to get the record for the current
FormName, and return the Username.

You can set the label's Caption the same way you set the
other control sources:

Me.Label7.Caption = Forms!SelectFieldDateRange.cboChoice

You would only need to use an If block when you want the
caption to be something other than the actual field name and
even then I would try to use the combo box's Column property
instead of hard coding a combo box's value.

Not sure I understand what you want in txtChange, but isn't
it in the record source? If so, I think you could use the
same kind of code to set txtChange's ControlSource. Here we
would need to know the specifics of the combo box:

If Forms!SelectFieldDateRange.cboChoice = "FormName" _
Then Me.txtChange.ControlSource = "UserName"

I'm getting tired of typing and reading that form control
reference. If you are too, use a With statement to simplify
the rest of the code:

WIth Forms!SelectFieldDateRange.cboChoice
Me.GroupLevel(0).ControlSource = .Value
Me.Username.ControlSource = .Value
Me.Label7.Caption = .Value
If .Value = "FormName" Then Me.txtChange.ControlSource =
"UserName"
End If

 

[gmazza via AccessMonster.com]

I see what you are saying. I mplemented it into my code and everything works
perfect, I thank you for your help! Have a good day!

Yes I still have the line that sets the group level.
You are correct, I thought it was Form, its actually called FormName.

[quoted text clipped - 17 lines]

and I see how it doesn't make sense. I need to get the record for the current
FormName, and return the Username.

You can set the label's Caption the same way you set the
other control sources:

Me.Label7.Caption = Forms!SelectFieldDateRange.cboChoice

You would only need to use an If block when you want the
caption to be something other than the actual field name and
even then I would try to use the combo box's Column property
instead of hard coding a combo box's value.

Not sure I understand what you want in txtChange, but isn't
it in the record source? If so, I think you could use the
same kind of code to set txtChange's ControlSource. Here we
would need to know the specifics of the combo box:

If Forms!SelectFieldDateRange.cboChoice = "FormName" _
Then Me.txtChange.ControlSource = "UserName"

I'm getting tired of typing and reading that form control
reference. If you are too, use a With statement to simplify
the rest of the code:

WIth Forms!SelectFieldDateRange.cboChoice
Me.GroupLevel(0).ControlSource = .Value
Me.Username.ControlSource = .Value
Me.Label7.Caption = .Value
If .Value = "FormName" Then Me.txtChange.ControlSource =
"UserName"
End If