How can I generate a report by using Option Buttons?

[Guest]

I am trying to generate reports by quarters and FYD by using an option
botton. The person will select the certian option the select two certian
style of reports that will generate. I have set up all the queries and
reports for each option. In the report button I have created a string of
code in Visual Basic. But it is not work!! Does anyone have a suggestion on
what I am missing. Here is the code:

stDocName1 = "PenaltyQrt1"
stDocName2 = "PenaltyQrt2"
stDocName3 = "PenaltyQrt3"
stDocName4 = "PenaltyQrt4"
StDocName5 = "PenaltyYDT"


If Option6 = 1 And Option7 = "" And Option8 = "" And Option9 = "" And
Optioin10 = "" Then
DoCmd.OpenReport stDocName1, acViewPreview
'GoTo SkipOver
End If
'If varsortby = 2 And vardate = "" And vardateto = "" And varnbr = ""
And varvendor = "" Then
'DoCmd.OpenReport stDocName8, acViewPreview
'GoTo SkipOver
'End If
'If varsortby = 3 And vardate = "" And vardateto = "" And varnbr = ""
And varvendor = "" Then
'DoCmd.OpenReport stDocName11, acViewPreview
'GoTo SkipOver
'End If
'If varsortby = 4 And vardate = "" And vardateto = "" And varnbr = ""
And varvendor = "" Then
'DoCmd.OpenReport stDocName4, acViewPreview
'GoTo SkipOver

 

[Guest]

Your approach is incorrect. You do not reference an option button by itself.
The option buttons should be in an Option Group. Then the Option Group will
return the value specified for each button. You specifiy the value the
button returns in the Option Value property of the button. Lets say your
option group is named ReportSelect and that the first button is selected.
Then:
intSelect = Me.ReportSelect
will return the value specified for the first button in the group.
So, for this exercise, make your first option the same value as specified in
your Option Base directive, and each subsequent button increment by 1. So if
your Option Base is 0, then button one should have the Option Value set to 0,
button 2 should have 1, etc.

Dim varRptList as Variant
varRptList =
Array("PenaltyQrt1","PenaltyQrt2","PenaltyQrt3","PenaltyQrt4","PenaltyYDT")
strDocName = varPrtList(Me.ReportSelect)
DoCmd.OpenReport stDocName, acViewPreview

I can't figure out what you are doing with varSortBy and varVendor, so I
can't get into detail there because they are referencing strDocnames not in
your original list, but hopefully, the above will give you some ideas.

 

[Guest]

hank you for your help. I am relatively new at this function of access. I
have worked in visual basic before but not on this type of code. I copied
the string of code from another database I have and applied it wrong. I have
now set up my option group but I am not understanding the intSelect =
Me.ReportSelect. Where does that apply?

 

[Guest]

I just made up a name for your Option Group. I called it ReportSelect, it
should be whatever the name of your Option Group is. If you don't have one,
you need to create it and all your option buttons should be enclosed in it.
intSelect was only used for an example of how you get the value from the
Option Group. It would contain the value of the selection option button
within the Option Group. Notice in the code I posted, it is not used.
There is a typo in this line:
strDocName = varPrtList(Me.ReportSelect)
It should be
strDocName = varRptList(Me.ReportSelect)
What this is doing is using the value returned from the Option Group to
select an element from the Array containing all the report names.

 

[Guest]

When I select an option in the option group and then run the code it brings
up all the reports. Not just the one that I have selected.

 

[Guest]

Post your code so I can review it, please

When I select an option in the option group and then run the code it brings
up all the reports. Not just the one that I have selected.

 

[Guest]

Private Sub Command43_Click()

Dim varRptList As Variant

vardata0 = "PenaltyQrt1"
vardata1 = "PenaltyQrt2"
vardata2 = "PenaltyQrt3"
vardata3 = "PenaltyQrt4"
vardata4 = "PenaltyYTD"

stDocName1 = "PenaltyQrt1"
stDocName2 = "PenaltyQrt2"
stDocName3 = "PenaltyQrt3"
stDocName4 = "PenaltyQrt4"
stDocName5 = "PenaltyYTD"

varRptList = Array("PenaltyQrt1", "PenaltyQrt2", "PenaltyQrt3",
"PenaltyQrt4", "PenaltyYDT")

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName1, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName2, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName3, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName4, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName5, acViewPreview

 

[Guest]

Private Sub Command43_Click()

Dim varRptList As Variant

vardata0 = "PenaltyQrt1"
vardata1 = "PenaltyQrt2"
vardata2 = "PenaltyQrt3"
vardata3 = "PenaltyQrt4"
vardata4 = "PenaltyYTD"

stDocName1 = "PenaltyQrt1"
stDocName2 = "PenaltyQrt2"
stDocName3 = "PenaltyQrt3"
stDocName4 = "PenaltyQrt4"
stDocName5 = "PenaltyYTD"

varRptList = Array("PenaltyQrt1", "PenaltyQrt2", "PenaltyQrt3",
"PenaltyQrt4", "PenaltyYDT")

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName1, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName2, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName3, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName4, acViewPreview

stDocName = varRptList(Me.ReportSelect)
DoCmd.OpenReport stDocName5, acViewPreview

 

[Guest]

The reason all your reports were coming up is because that is what you were
telling it to do. Here is how this works:
varRptList is an array of the report names available for selection.
Me.SelectReport is your option group that will return an integer. That
interger will be used to select the report name from the array. The selected
array element will then be loaded into the stDocName varialbe. This will be
the report that opens.
The one thing you need to be aware of is what the setting is for your Option
Base statement. Option Base is at the module level, so look in the module
where the code is. If you don't find an Option Base statement at the top of
the module, that is okay. It means it is 0. That means that the first
element of your array will be 0. Example:
varRptList(0) will return "PenaltyQtr1"
If Option Base is 1, then it would be varRptList(1)

Private Sub Command43_Click()
Dim varRptList As Variant
'Set up the array to select a report
varRptList = Array("PenaltyQrt1", "PenaltyQrt2", "PenaltyQrt3",
"PenaltyQrt4", "PenaltyYDT")

'Initialize the variable used to open the report based on the selection
'in the Option Group
stDocName = varRptList(Me.ReportSelect)
'Open the selected report
DoCmd.OpenReport stDocName, acViewPreview

Here is an alternative the the line above that will do the same thing:
DoCmd.OpenReport varRptList(Me.ReportSelect), acViewPreview
I included this so many you will see the relationship.

 

[Guest]

You are my Hero!!! I works great!! I had one small error in the string of
code. I had (Me.ReportSelect) instead of (Me!ReportSelect). Where did you
learn all of this?

 

[Guest]

I was born know almost everything :-()

Actually, I have been programming for 28 years in about a dozen different
languages, so some of it has rubbed off.
One way to learn a lot about a language:
Have a "Function of the Day" or "Object of the Day" or whatever.
Pick one at random or one that interests you.
Read up on it. If it makes absolutely no sense, pick another.
Experiment with it to see what you can do with it.
Think about ways you can use it.
Think about code you have done in the past and whether it would have made it
better or easier.
Start with arrays, for example. See how easy using an array made this? Read
up on arrays and see how the work. You can expand your base from there,
because as you learn about arrays, you will discover Ubound, Lbound, Redim,
and Redim Preserve, for example.

Good Luck, Glad I could Help