Event Diary Database Help!

Davrini

Registered User.
Local time
Today, 16:18
Joined
Aug 12, 2012
Messages
29
Hello!

Have been writing a database for the clinic where I work over the last few days, and have completed everything that I need to except for this, which is the biggest, and I haven't a clue how to tackle it.

I want a Form that across the top has a List of Counsellors (4-6 in total, depending on day), and down the side has allotted time slots (3 times), but varies on date selected. Every Date has a different list of Counsellors at the clinic. They need to autoload when the correct Day of the week is selected (So when the date is 13/08/2012, then all Monday Counsellors would load across the top). The Time remains the same, regardless of day.

Next is the actual bookings. With up to 6 Counsellors each with 3 different time slots, the form could have up to 18 bookings that day (and therefore, 18 subforms). Each Subform must contain the same information (Client ID, Session Type, Attendance and Notes), but when data is entered into these subforms, then they must also know to include the Date, Time and Counsellor automatically as allocated by the position they are in, eventhough that data isn't inputted by the administrator.

My problem is that I haven't a clue what tables to build, what queries to run, nor how to compile the main form (I know how I want the subforms to look).

I have compiled the 'look' of what I want in a MS Paint document attached to this post. If anyone could steer me towards how to accomplish this best, I would be incredibly greatful, or point me to a demo database that someone else has published that does something similar, I am sure I could dissect that and adapt it to my own needs.

Many Many thanks in advance!
Davron
 

Attachments

  • Database Diary.jpg
    Database Diary.jpg
    105.3 KB · Views: 158
davrini, hi

What version of access are you using?

I left this post alone long enough for some of the pro's to look at this as i am sure they will have an easy answer to this. I am working on something for this and will post as soon as i have it. Essentially you would use an unbound form to hold a subform with the appointment slots (from an appointments slot table) and columns of 5 subforms (from an appointments table) for the corresponding appointments. The BIG job is all the coding in the background to ensure the right counsellors and appointments appear for the selected day and syncronised to the scrolling of the appointments slots:eek::eek::banghead:. Hopefully you can wait OR someone will post saying "oh no, you dont need to go down Isskint's route, what you should do is..":p
 
Access 2010. Did some tweaking last night, still not quite what I want but I think I know more clearly about how I want the individual bookings to look. My difficulty is in linking, as you rightfully said, the date/time/counsellors to appear in the right place, and the code for that is beyond me at the moment. :/
 
Sorry i did not get back to you yesterday.

OK, so this is actually easier than i thought. I was trying to overcomplicate things.
Set your form up how you want it to look, all unbound.
You provide existing appointment details (from an Appointments table :p) by utilising OpenRecordset command. When you enter data, use an update query (for existing) appointments OR an append query for new appointments.

The 'trick' here is to put in afterupdate code for EVERY control on your form. To simplify, write a procedure to update the screen data and the appointments table. Then each time a textbox, combobox etc is changed, call this procedure.

Have a look at the attached, hope it helps/works
 

Attachments

Excellent! This is indeed something similar to what I want, however would require a few tweaks here or there, and you have over-complicated it! The clinic is only open two days a week with 3 time slots (but more counsellors (and there is an expansion coming up this year to 5 other sites, so it may indeed require different days!))

Now, my query is this! Everytime the Main form that you have created requires a change (whether amending the details of a booking, or the day itself) it asks me to amend every row. Is there anyway to bypass this at all so that it amends automatically without a message popping up?

Edit: Just noticed that every time you amend the "row", it creates a new appointment. So when I set all the details of, say, the first appointment of the day, new appointment details are put in for every "amend" I make. So the table goes:
- Put in Client ID
CLIENT ID - ___ - ___ - ___ -___
- Put in Counsellor (Automatic)
CLIENT ID - COUNSELLOR - ___ - ___ -___
- Put in Time
CLIENT ID - COUNSELLOR - TIME - ___ - ___
-Put in Session Type
CLIENT ID - COUNSELLOR - TIME - SESSION TYPE - ____
- Put in Attendance
CLIENT ID - COUNSELLOR - TIME - SESSION TYPE - ATTENDANCE

So it gives me 5 new appointment records for one Appointment. :/
 
Last edited:
The warning is easy to stop. Where you have the DoCmd.RunSQL put Docmd.Setwarnings FALSE on the line before it and Docmd.Setwarnings TRUE on the line after it, so you get
Code:
Docmd.Setwarnings FALSE
DoCmd.RunSQL strSQL
Docmd.Setwarnings TRUE

The other bit i will have to double check l8r (the DB is on my works PC). Probably find the check of existing record code is faulty
 
:D

I'm tweaking with it bit-by-bit, but I'm not sure if I'm going to use this format in the end :banghead: Which feels silly given the amount of effort you've put into it, but I can't quite get it to work for myself.

However, I do have one more question! I saw in your code that you have it written so that data that's inputted into a form can be written to a table if those tables are not linked. 'Tis like this:

I have a pop-up form that is the full details of that appointment, drawn from the Appointment table. However, to ensure Client-Counsellor integrity, I have had to pull the Counsellor from the Appointment Table and run it through a query (so that each Client is allocated a Counsellor, this being on the main Client data table). At present, the Appointment table does not have a Counsellor field because a query runs which figures out the date/time/counsellor and pulls the info like that, and when I make an Appointment, the Counsellor (pulled from the Client Table) cannot be saved to the Appointment Table.

How can I make the Counsellor save to the Appointment table without having to input it manually (as doing so will mess up the Client-Counsellor integrity)?
 
hey, dont fret it. If you use it, great, if not perhaps it will provide tips for someone else.

As to saving the counsellor to the appointment table, do you mean as each appointment is created? Just follow the examples of UPDATE and INSERT INTO code and trigger this in an after update event of a control or the forms BEFORE_UPDATE event (depends when you query the date/time/counsellor).
If you mean how to add the field to the table and then populate the field with all the counsellor/client allocations so far, just use a normal UPDATE query.
 
Isskint,

Sorry for the lack of reply, was my Birthday yesterday and apparently this means I can't work on this database on said day! Pfft!

Today I cracked it! Figured out what I was doing wrong and how to solve all of my Diary problems, of which there were many. I may not have utalised the database that you made for me, however the VBA code has proved invaluable and managed to work how I want it to be done by adapting your code thusly. I extend my sincere gratitude! All that is left for me to do now if to figure out how to allow Supervision entries (for the Counsellors) without the Client field being inputted (which is pretty trivial and a problem I'm sure to solve myself), but incase any issues arise, I'm keeping you on a retainer!

As such perhaps there is one matter you may be able to help me with. I have created a Diary that looks like the attached file, where it a query-based setup. My question is a simple one; is there any way of changing the selected colour for the Diary boxes (as in, the contrast when you select an entry is black against a white background, but is there any way to make it a light grey against a white background. It's purely for aesthetic reasons, and is nothing to do with the functionality of it, other than more user-friendly)

:D
 

Attachments

  • Diarypic.jpg
    Diarypic.jpg
    95.2 KB · Views: 104
you can achieve this through Format>>Conditional Formatting (choose On Focus). As each box receives focus it will change to the colour schema you set. If you want each box in the record to change colour, you woudl have to use some VBA in the Got Focus event of each box.
 
Nope, can't conditional format list boxes. :/ And the work around is far to complicated, it will just have to stay how it is.

One more question! Hehe...

The clients in the database occasionally have a different type of session, called a "Change Interview". I've created a query which counts the total number of Change Interviews a client has had and inputs that data into the Mega-Query (which is where ALL the data about the client it collated). The same must be done with the attendance rates (that is, to calculate the total sessions they have attends, were UTA (Unable to Attend), DNA (Did Not Attend) or Cancelled another way. All of these variables are then summed to calculate their Session Number and Attendance Rate (Data which is needed for the funding).

My problem is that in instances where the clients have not yet had a Change interview, or have not missed a session, then the queries that are run do not return a value. How can I run a query so that instead of:

Code:
Client   No.CIs
CL0001   4
CL0002
CL0003   1
CL0004
CL0005
CL0006   2
CL0007   3

It returns the 'Null' values as a Zero so the query runs:

Code:
Client   No.CIs
CL0001   4
CL0002   0
CL0003   1
CL0004   0
CL0005   0
CL0006   2
CL0007   3

(The same would be applied for attendance. If they haven't skipped a session, then that query returns a blank field). The problem with this is that the Formula's then do not number crunch corrently as if one of the fields is missing a number, the formula does not work.

Total Sessions: DateDiff("w",[First Session],[Last Session])-([Total UTAs]+[Total DNAs]+[Other Cancelations]+[Total CIs])

I'm trying to do this on one query so that I the data is all automatic, and does not require a form to be open for it to be adjusted. Is this possible?
 
Take a look at the Nz() function if you want Null values returned as something else.
 

Users who are viewing this thread

Back
Top Bottom