Microsoft Excel: Spreadsheet Control or something else?

[EliseFreedman]

Hi There

Several Months ago I create a patient record system in excel using the template feature. Since then, I seem to have experienced various niggles with this not least its a pain when the users want the form altered. I have decided to take a new approach and do it using userforms (Ideally would use MS Access but we dont have this at work!!). I have successfully managed using code to update, delete and add patient records using a userform.

I now want to add an appointment card to the userform which would allow the users to insert the date of the appointment, the type of the appointment from a drop down list and whether the patient attended. I then need to hold a total on each record of the number of treatments, assessments etc in each month. These totals would be written to the spreadsheet linked to the userform. Im not sure what kind of object to use to hold this data. I was going to use a flexgrid but it doesnt seem to work with Excel. I then looked at the spreadsheet control but it seems to be fairly limited in what it can do for example I couldnt use a sumproduct formula and couldnt have any validation. Does anyone have any suggestions as to what approach I should use?

 

[SkipVought]

Elsie,

"I then need to hold a total on each record of the number of treatments, assessments etc in each month."

Not a good design. Each treatment ought to be stored in a table with the data of service.

Your Totals would be the result of a query, where you would aggregate on given criteria.

"Im not sure what kind of object to use to hold this data."

I'm not sure what you mean. The data is stored in a table. In Excel, that's a dedicated sheet or a Named Range. What the user sees, is a REPORT generated by one or more queries.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Please tell me you're not serious - this has massive data protection issues. Excel is NOT a secure application and if you are thinking of holding confidential patient treatment records, you are leaving yourself wide open to legal action should someone who is not authorised gain access to those records - nevermind the fact that Excel is not a database and therfore has no concept of referential integrity which is really needed to make sure that data is updated in a structured manner...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Do I hear an AMEN?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}