Linking Checkboxes to values in a table for a Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

schmidtmic@gmail.com - 09 Dec 2005 16:45 GMT

I have a table that contains 20 checkbox fields. Each checkbox
corrisponds to an Score amount.

For Example:

Checkbox1 is worth 10 points
Checkbox2 is worth 5 points
Checkbox3 is worth 2 points
Checkbox4 is worth 3 points

The report I run has these score values hard coded within the query.
For Example: IIF([Checkbox1]=Yes,10,0)
If the checkbox is checked they get 10 points. If not they get zero
points

A report is then easily generated showing how many points a person
scored on a test.

PROBLEM:
I want the user to be able to change the score values of each Checkbox.
Today Checkbox1 is worth 10 points BUT tomorrow I want to make it worth
20 points. Hardcoding the values is not an option in this case.

What would the best way to do this be? Can I create a table with these
point values in them. If so how would I link it back to the original
data holding table?
Example:
tblScore

Checkbox Score Desc Point Value
Checkbox1 Name Entered 10
Checkbox2 Paper Filled 5

Thanks for any help you can provide

Reply to this Message

Mike Preston - 09 Dec 2005 22:16 GMT

>I have a table that contains 20 checkbox fields. Each checkbox
>corrisponds to an Score amount.
[quoted text clipped - 30 lines]
>
>Thanks for any help you can provide

There are lots of ways of doing this. In order to opine on the method
that might be least intrusive, I think more information is necessary.
Like the actual query you are using. Is it the recordsource for the
report or is it the controlsource for a textbox (or series of
textboxes) on a report.

Another way might be to use the Tag of each checkbox to store a value
and then access that value in your query.

Is the form open when the report is run? If so, you can set up a
parameter query and then set each parameter in vba with a simple loop
before executing the query and then use that results of that query as
your input into the report.

Hardcoding, in general, is bad.

So, fill in more details as to what is going on and maybe somebody
will have more detailed help.

mike

Reply to this Message

schmidtmic@gmail.com - 09 Dec 2005 23:44 GMT

Thanks for your reply Mike

The query is the record source for the report currently. The report
runs directly from it. A parameter report sounds interesting but
wouldn't that take a great deal of time to enter all 20 values?
Currently this report is run every day and I'm not sure if my users
would want to spend the time entering parameters.

I'm afraid I am not familiar with what a Tag is. Though after looking
it up in Help it sounds like it might do the trick. I'll give it a try
and see if I can make it work.

Thanks again

Reply to this Message

schmidtmic@gmail.com - 10 Dec 2005 00:22 GMT

HELP! After a few tries to set a TAG I seem to have failed.

I have created a macro that runs when a button is clicked on the same
form that the checkbox exists on.
The Macro uses the "SetValue" action. In the Item field I entered the
following. I'm not 100% sure the syntext is correct though:
[Forms]![Auditor's Input Form2]![Score01].[Tag]="10"

My assumption was that when I looked at the tag property of the
[Score01] checkbox that I would see 10 listed. Is this an incorrect
assumption?

Am I setting the tag value correctly?
Any help is greatly appreciated.

Reply to this Message

Mike Preston - 12 Dec 2005 03:11 GMT

>HELP! After a few tries to set a TAG I seem to have failed.
>
[quoted text clipped - 9 lines]
>
>Am I setting the tag value correctly?

Looks like it to me.

Not being familiar with macro's, though, I won't be able to test your
theories. If you did it in VBA, you could put a STOP command right
after the tag is set and then enter the immediate window (by typing
cntl-G) and then typing:

? [Forms]![Auditor's Input Form2]![Score01].[Tag]

and seeing what pops up.

Either switch to VBA, or somebody else will have to help from here on
out!

mike

Reply to this Message