How to read option button values to a query

[Guest]

I am trying to figure out the former programmers code, she has 3 radio
buttons and it seems like she doesn't get the value and the query prints out
all 3 options. How do I get it to recognize what the value is and have the
query print it out correctly?

Thanks!!!

 

[Guest]

An option group has a numeric value, depending on which option is selected;
more often than not these are 1, 2 and 3 bit you can check this by examining
the OptionValue of each radio button in its properties sheet.

The Option group would be referenced as parameter in the query along these
lines:

Forms!YourForm!YourOptionGroup

Depending on the option selected the parameter would thus have a value of 1,
2 or 3, so how this is used to restrict the rows in the query depends on what
values the column to which the parameter is applied contains. If the column
only has values of 1, 2 or 3 in it then its simply a question of putting the
reference to the option group in the criteria row of the column in query
design view. If the column has 3 possible numeric values, but these don't
equate with the OptionValues of the radio buttons, then simply change the
OptionValue properties so they tally with the values in the column in the
table.

If the column has other non numeric values, Good, Bad, Indifferent for
instance then you can create another table, Options say, with columns like so:

OptionID Option
---------------------------
1 Good
2 Bad
3 Indifferent

You can then add the Options table to the query, joining it to your existing
table, and put the parameter in the criteria row of the OptionID column.

The above is a very simple scenario, of course, and it may be that your
options are less simply handled. If so post back with more details.

Having said that, its sometimes the case that option buttons are used in
dialogue forms when a combo box would be a better solution. Unlike an option
group a combo box gives the user choices of values directly obtained from the
data, so it always presents the currently available values, i.e. the
available options change as the data in the table changes, so if Excellent
was added to my Good, Bad, Indifferent options by putting this value in a new
row in the table, the combo box would automatically include Excellent in its
list.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,
This is what I found:
Select Case framSelectList
Case 1
txtSelectList = ""
Case 2
txtSelectList = "[CACN] = 'A'"
Case 3
txtSelectList = "[CACN] = 'N'"
Case Else
MsgBox "Please select a list", , "Select a List"
framSelectList.SetFocus
End Select

This is in the command_click button
Select Case framDeletes
Case 1
txtDeletes = ""
Case 2
txtDeletes = "[Delete] = 0"

framDeletes.SetFocus
End Select
DoCmd.SetWarnings False
Me.Form.Refresh

Call ReportUtilities.GetParishioners
Call ReportUtilities.DeleteContribTmpTbl
Call ReportUtilities.GetCurrentContribs

'DoCmd.OpenQuery "RosterContributions qry", acViewNormal, acEdit
DoCmd.SendObject acSendQuery, "RosterContributions qry", acFormatXLS, ,
, "Data Recording", "Company", , True

I don't know how she ever got the values.

 

[chriske911]

Anna was thinking very hard :

Ken,
This is what I found:
Select Case framSelectList
Case 1
txtSelectList = ""
Case 2
txtSelectList = "[CACN] = 'A'"
Case 3
txtSelectList = "[CACN] = 'N'"
Case Else
MsgBox "Please select a list", , "Select a List"
framSelectList.SetFocus
End Select

This is in the command_click button
Select Case framDeletes
Case 1
txtDeletes = ""
Case 2
txtDeletes = "[Delete] = 0"

framDeletes.SetFocus
End Select
DoCmd.SetWarnings False
Me.Form.Refresh

Call ReportUtilities.GetParishioners
Call ReportUtilities.DeleteContribTmpTbl
Call ReportUtilities.GetCurrentContribs

'DoCmd.OpenQuery "RosterContributions qry", acViewNormal, acEdit
DoCmd.SendObject acSendQuery, "RosterContributions qry", acFormatXLS, ,
, "Data Recording", "Company", , True

I don't know how she ever got the values.

"Anna" wrote:

most likely there is a referral in the query of the report(s) pointing
to those fields (CACN and Delete) on the selection form
open up the data source and take a look at the criteria or the
parameters

grtz

 

[Guest]

Anna:

Lets take it piece by piece:

Select Case framSelectList
Case 1
txtSelectList = ""
Case 2
txtSelectList = "[CACN] = 'A'"
Case 3
txtSelectList = "[CACN] = 'N'"
Case Else
MsgBox "Please select a list", , "Select a List"
framSelectList.SetFocus
End Select

In what event procedure is this code located? It examines the option group
framSelectlist and sets the value of a (hidden?) text box, txtSelectList, to
a zero length string, "[CACN] = 'A'" or "[CACN] = 'N'" depending on which
option button is selected. It also includes a Case Else statement which I'd
imagine is if no option is selected as it prompts the user to make a
selection. The string expressions are not values for a parameter in the
normal way. If they were the value of the text box would be set to just A or
N and the CACN column in a query would reference the text box. It looks more
like a string expression to be concatenated into an SQL statement, which
might be done within the code in the procedures in the ReportUtilities module
(see below).

This is in the command_click button
Select Case framDeletes
Case 1
txtDeletes = ""
Case 2
txtDeletes = "[Delete] = 0"

framDeletes.SetFocus
End Select
DoCmd.SetWarnings False
Me.Form.Refresh

Call ReportUtilities.GetParishioners
Call ReportUtilities.DeleteContribTmpTbl
Call ReportUtilities.GetCurrentContribs

'DoCmd.OpenQuery "RosterContributions qry", acViewNormal, acEdit
DoCmd.SendObject acSendQuery, "RosterContributions qry", acFormatXLS, ,
, "Data Recording", "Company", , True

This appears to refer to another option group and to judge by the name of
the control sounds like it is for deleting records. It again sets the value
of a (hidden?) text box to a sting expression, and then calls some procedures
in a module called ReportUtilities, and finally uses the SendObject method to
send a query as an email attachment in XLS format. There is a line before
this to open the query, but this is commented out, so won't execute.

The first thing to do is have a look at the GetParishioners,
DeleteContribTmpTbl and GetCurrentContribs procures in the ReportUtilities
module to see what they do. My guess is that these probably make use of both
the text boxes whose values are set in the code in the form's class module.

To be honest it all looks rather messy. If the values in the text boxes are
being used by the procedures in the module then its not a very good approach.
Far better would be to pass the values as arguments to the procedures. I
think you should concentrate on what these routines are intended to do,
rather than trying to figure out how the previous developer tried to do it.
I'd suspect that once the purpose of the routines is understood a better way
of doing it can be produced.

One other thing I notice which makes my antennae twitch is that from its
name it sounds like the DeleteContribTmpTbl procedure might be deleting a
temporary table. Sometimes creating a temporary table makes sense, but my
experience has tended to be that its more commonly a device to overcome more
fundamental shortcomings in an application.

Ken Sheridan
Stafford, England