Using userforms

[shaun.burke]

I need to enter data into the demographics table, the TXgoals table, and the Interventions table so into the form so that I can have staff
add, delete, and modify goals and interventions into DailyServices and DailyInterventions tables.

I probably am not going about this right, so here is a run down of what I'm trying to do. Our agency provides support to students with
emotional disturbance in a classroom setting. There are typical multiple services provided throughout the daily based on the
needs of the student. We bill in 15 minute increments for those services but we can be working with a student for 15 minutes
or for several hours, based on the need of the student. We need to be able to document the services provided and provide data on the
effectiveness of treatment. A progress note will have the date, start / end time, the service type, that class / activity that was
occurring during the service and the service being provided. We need to be able to document which interventions from the
student's treatment plan we used during the time we provided services. We then need to give the purpose of the intervention
why the service is being provided), Response (the response of the student to the intervention, and the Plan (What happens next
based on the results of the intervention). We need to be able to state whether the intervention was effective or not.

Here's an example

Let's say I'm working with Johnny. I worked with him during Math class from 7:40 AM - 8:20 AM to stay on task. He was avoiding doing
his work, talking to those around him, drawing on his notebook, or making disruptive noises. I tried tried to redirect him to focus
which led him to tell me to "f-off." I then used environmental structuring (I sat next to him), which led him to escalate and get louder.
I then reminded him of his skills and encouraged him to use impulse control strategies, which led him to apologize and get on task. So,
during Math from 7:40 - 8:20 I used 3 different interventions for his Staying on Task treatment goal. I need to document that in a
progress note and track the data from that and see trends (is misbehaviors during a particular time of day, particular class. Is there
an intervention that tends to be more effective, is there one that doesn't work...etc).

So, here is the logic in what I set up. There needs to be a demographics sheet that identifies the students. I then need a sheet for
the treatment goals, measurable objectives, and the interventions. I put the interventions in a separate worksheet because there are
several interventions per treatment goal. I then have 2 separate sheets for the progress notes data entry, one for the daily services
being provided, when they are being provided, and the purpose, response, and plan for the interventions used during that time. I then
put the daily interventions in a separate sheet because I didn't know how to put multiple interventions per services all in one sheet.
Finally, I put in a daily summary worksheet because one of the programs uses a daily summary rather that a Purpose/Response/Plan for each
Service provided. That program still has several services throughout the day but only needs to document the day overall.

I still need to build the progress note report itself as well as an encounter report. I also haven't built any of the reports for overall
progress.

I am having a hard time pulling from the separate sheets of Demographics, Tx goals, and interventions to be able to add into the daily services, daily interventions, and daily summary worksheets. I am also having a hard time thinking about the most efficient way to create the daily progress notes

I have reattached the file with all of the sheets. .

[paulked]

I think it's going to be easier for you if you have one sheet that consolidates all the data. The userforms and tables on other sheets etc can then all read and write to that one database as needed. It may take a bit of time to set up, but will save you loads in the future!

[shaun.burke]

What would that look like?

[paulked]

I've created a table (tblData) on worksheet Data and have started to fill in some details. This table has consolidated data from the tables on sheets Demographics, TxGoals, Interventions, DailyServices, DailyInterventions and DailySummary.

Spend time setting this up to include all the data you need.

Keep the sheet AdministrativeList for all the options you want (I've made some references to them in the PNEnter UserForm_Initialize() routine).

Then, once you know what data you want and where, use the userforms to add/delete/report/update to/from the tblData.

I'll make up an example userform this afternoon for you to see how it should work.

[shaun.burke]

Holy cow this was simple. I was over-complicating it.

[paulked]

All the data in the same place does simplify things.

Attached is an example of getting/adding/updating patient details. There is no error checking and it was thrown together as I have little time to 'play' today!

[shaun.burke]

I appreciate the direction.

[shaun.burke]

So, I've created a user form to enter in the data using your form. Now I need to be able to integrate the VBA that pulls up the interventions that go with the measurable objectives

[paulked]

That userform seems awfully large, does it need to be? eg get rid of first and last names immediately as they are in cmb1

If that is for one visit then fine. But if it's for different instances then create two or more forms. The more boxes to fill, the more likely an error!

I'd suggest a 'short code' for the Interventions as these seem to be constants, otherwise picking 'Transfer of skills through discussions with collateral contacts in the school and community when determined to be clinically necessary by the treatment team to identify and support in using anger management skills.' from a combobox is a nightmare. Rather than a short code, do you want another userform to pop-up to select from?

Are there are a standard set of goals? If a goal is used often, then a list on the admin sheet is a time saver, the same goes for Measurable Ojectives. These lists can be dynamic, so if you add a new one it updates the list for next time. How do we pick these in the userform? Again, combobox with short code or pop-up userform?

Things to think about for content:
Userforms: Patient/Student Detail, Site Visits, Interventions, Print Reports, Admin Menu etc.
Reports (created on sheets): Billing, Contact Lists, Patient/Student Progress/Effectiveness etc.

These are the types of questions you need to be asking yourself and getting the info down on the two sheets Data and Admin. You can never have too much data, it's easier to dump it than to fish for it. Then we can start playing with the data proper :thumb

[paulked]

I have added a userform to produce a report. Again, there is no error checking so you must select someone with dates (ie Johnny Test!). Where do you get the units to be billed from?

[paulked]

I took a guess at the billing units and have put them on the Data Sheet. There is a cell on the Admin Sheet that holds the billing time segments (15mins at the moment) and I have rounded up the count of these units.

I've also added the export PDF for the report.

Let me know your thoughts, cheers.

[shaun.burke]

I appreciate all of your help. Your direction has really helped me rethink it. I had to change the billing units because we can't round up.

I added several Forms, alot of additional fields to the data, extra reports. I think there should be some additional lists I have to add in, but my head is swimming

[paulked]

That's great! The more info at this stage the easier it gets later. We'll get this done in no time :wink:

[paulked]

I like the way it is coming together but there is one thing to watch out for. You may not have noticed I left a clear row and column around the data table (tblData). It is quite important to keep this space so we can select the table easily using CurrentRegion. It may look a bit disorganised but it isn't :thumb. It's easy enough to insert a few more rows before the table starts to allow for sub-headings etc, they can always be hidden after.

Once you are sure this table has all the info you need we can start writing code 'proper', until then the columns we are looking up will be changing. Once we start coding we only add to the end of the table so we don't have loads of code to change and let the bugs in :devil2:

This is why it is worth all your time and effort early on :clap:

[paulked]

I've pm'd a OneDrive link to the file I'm working on.