Using Multiple Checkboxes to set Query Parameters

roboton
roboton used Ask the Experts™
on
I am in the process of setting up a database that allows users to enter test result records, and generate specific reports from the records. There are 47 query parameters to choose from, and for the sake of a sane UI, I have chosen to use checkboxes so that the user can select from any of the 47 test results.

I have built in mysql+php, but my boss prefers me to re-create it using MS Access. In Access, I can create the forms and sub-forms for data entry, no problem. My opportunity arises when I try to create the 47 checkboxes that the user can use to select query parameters.

In php, I duplicated and reformatted the apache-specific $_POST array and named it $ParamCleaned. Next I created a while loop that references an array that contains key-value pairs of test results by date, based on a specimen ID number. Within the while loop is contained a foreach loop that loops over the $ParamCleaned array and pulls from the SQL select statement where $key is equal to the database field name.

It looks like:

$result = mysql_query ("SELECT * FROM parameters WHERE SpecimenID = '$SpecimenID'");

//removed code where $_POST get duplicated as $ParamCleaned//

while ($ResultArray = mysql_fetch_array($result)){
print ("$ResultArray[TestDate]");
foreach ($ParamCleaned as $key => $query){
print ("$ResultArray[$key]");
}
}


My questions are:

How do I set up a checkbox in MS access to contain a variable name? Furthermore, how do I make a command button that takes the value of all 47 checkboxes, and pulls the data from corresponding database fields where the checkbox value = true?

For instance, I have three checkboxes:
Amylase
Triglycerides
Cholesterol

The user wants to generate a report on Specimen ID 1004 that pulls only the triglyceride test results.

My hunch is that I need to set up a seperate database and query that maps to the checkboxes, and a command button that selects data from the DB with the results data based on the values contained in the other, the other being the database that the checkboxes update. For the life of me, I cannot seem to figure out how Access would do such a thing.

Am I the only that immediately turns stupid when I try to use MS software?

Regards,
Greg Morgan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
A checkbox can only contain True or False, but you can name them freely for loopprocessing.
E.g. name them chk1/chk2/etc.
Now you can use:
for intI = 1 to 47
if me("chk"&intI) = true then
  ...
endif
next

The location of the "triglyceride" code can be in the .tag property. enabling you to extract it from code, but you couls also label the checkbox with "triglyceride" and have the labels coded like the checkboxes.

Getting the idea ?

Nic;o)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial