Full Version: How To Work With A Flexgrid?
UtterAccess Forums > Microsoft Access > Access Forms
JessicaZ
Quick recap: I am creating an appointment database for a massage spa. I am working with the FlexGrid as suggested by Peter Hibbs to try to show appointments for all of the massage therapists working on a chosen day.
o I have now successfully gotten the FlexGrid to work and have gotten the top row and first column populated (yay me!) Next step is to populate the appointments. I believe I need to loop through the therapists and loop through the appointments in order to get them all in the correct spot. the question is: how do I match the therapist ID (top of the column) to the appointment recordset? (I know it would be if xyz therapist, list the appointments, but I need to know the correct way to reference that therapists column)
Also, is there a way to block out times the therapist isn't working? My schedule goes from 9am to 9pm but therapists will only work a certain length shift (usually 4-6 hours), I need to ba able to look at the work schedule table and either only allow appointments during the times they work or completely "turn off' the cell for times they don't work
I hope this is making sense!
Thanks
Jess
Peter Hibbs
Hi Jess,
i>"I need to loop through the therapists and loop through the appointments" That is correct. You would probably want to create a query which returns all the appointments for the selected date along with the times and therapist's ID, etc and then create a recordset in code which would allocate the appointments to the appropriate cells in the FlexGrid control. I believe the demo programs I suggested have something similar you can look at.
"how do I match the therapist ID (top of the column) to the appointment recordset" There are several ways you can do this. The method I prefer is to add a new Number field to the table which holds the therapist's data (call it ColumnNo or whatever) and enter a number between 1 and n which would represent the column on the grid for that therapist. In that way it is easy to change the positions of the therapist names on the grid and add more if necessary. In the code you would then use the value in the field to determine which column that therapists appointments would be shown in.
"is there a way to block out times the therapist isn't working" This is a bit trickier to advise on since I don't know how you are laying out the grid but one method may be to use the extensive cell color coding facilities of the grid. Perhaps you could color the time slots where the therapist is not available in grey, for example. I am guessing you have a couple of fields in the therapist's table which define their working hours (or non-working hours) so you could probably use that data to calculate the cells to be marked.
Perhaps a screen shot of what have so far would help us to suggest other options.
Peter Hibbs.
JessicaZ
Here is a screenshot. its nothing spectacular <
herapists across the top (sorted by work schedule start time), times along the side.
for the schedule:
Take Michelle - she works from 9 - 1pm, so she needs to be grey after 1:00. Audra works from 11-7, so she needs to be marked out before 11 and after 7, etc...
And of course I need to fill in the appointments under the correct person
Peter Hibbs
Hi Jess,
OK, that looks pretty good so far. Just a couple of suggestions on the screen shot - what font are you using? I suggest that you use Tahoma which generally looks better than some other fonts. You could also change the formatting of the times as you don't really need the 'seconds' column and it would take up less room.
Now I think it would be a good idea if you wrote the code yourself (rather than me doing it for you) because you will learn more that way. So I will give you a few tips and see how you get on (but feel free to ask for advice on specific parts of the code).
You haven't said what tables and fields you have set up or what code you have written so far so this is just general coding advice for the moment. As I said before, I think your best bet is to add a new Number field to the Employees table and set it to 1 for Michelle, 2 for Audra and so on.
For the coding part, I think it is essential that you keep all the code for this form in the same sub-routine and I would suggest saving the sub-routine in a separate Code module rather than having it in the form's code module. The reason for keeping all the code in a sub-routine is that later on you may want to add more facilities which involve re-programming the FlexGrid control in a different way to show different data. For example, your current code will show the appointments for a single day but you could also have the option to show all the appointments for a week for one employee, or whatever. What you would do in this case is write another sub-routine which changed the grid format to show the different data and then you would just call the sub-routine from a button and to go back to the Day mode you would just call the first sub-routine again. If you study the demo database called FlexGrid Appointments Demo you can see how it works.
The nearest code that does what you need is called ShowDay in the module modCalendarRoutines and the sub-routine called DayUpdateAppointments is the recordset loop code that you would use. In your case, the code could be simplified a bit because your appointments (I assume) will never last more than a day whereas this code allows for multiple days. Also, in this code there is only one column for appointments but you will have several so you would use the value from your new field to specify the required column (fixed as 1 in the code). Also the query part of the code can be simplified because you only need to return the appointments for one day (specified by variable vDate that is passed to the routine from the calling form) instead of multiple days. To show all the cells for each appointment as one block, you need to write the appointment information to all the cells for the appointment and set the MergeCol property for the columns but I would not bother with that for now as it can be added later. I suggest you think about getting the appointments showing on the grid before you do anything else.
Regarding the issue of non working hours, the way I would do this is to run a separate routine first (probably combined with the code you used to show the header cells) which colors in the times that each employee is not available based on the data held in their records. However, I would suggest you leave this bit for now as that can be added in later.
The two sub-routines I mentioned do pretty much what you want although, as I said, they will need changing slightly so it is worth looking at these carefully and see how they work. Once you have the basics working you should find it easier to add in the 'bells and whistles' later. Also the SelectRange and FlexGridClick routines in the module modFlexGridRoutines will be very useful so make sure you import those into your project.
Oassume you have also printed out the list of FlexGrid properties, methods and events that came in the documentation with the original FlexGrid Demo which you will need when setting up the grid.
When you have done a bit more please post back and let us know how you are getting on.
Peter Hibbs.
JessicaZ
hi Peter and thanks for the feedback
Oagree with the time format and font, honestly I have been just trying to figure out how the flexgrid works and not worrying too much about those details yet. I have the guides printed off and will hopefully have 5 minutes to myself to read through them this weekend!
Is for your suggestion about numbering the order of the therapists - my only concern is that they will not always be in the same order and not all of them will be on the same days (we have around 15 therapists - my query looks at who is working that day and then orders them by the time they start - so one day Michelle may be working at 9 and will be the first one on the books, but the next day she may work a later shift and someone else will be the first on the books. I was hoping I could match the appointments to the therapist ID in the grid to fill in the appointments....guess we will see what I can figure out!)
I'll let you know after I do some more playing around <
jess
BananaRepublic
While I've not worked with FlexiGrid, I had a similar setup for a PivotTable and the approach was to create a query (could be queries if you prefer) that uses 3 sources:
) a table listing all hours/time block that your business is open
2) a table listing all therapists
3) a query listing therapists' assigned schedule.
You need to cross join the first two table (e.g. no line between the tables). That will give you all possible combination of hours/therapist.
Next, you would join the 3rd table to the result of the joined pairs, matching on therapist and time block. if there is a result, there is a result, otherwise, it's Null.
That gives you a query that lists the full range of hours which you can easily iterate over to get the timeblock and format flexigrid accordingly. At least that's what worked for me when I used PivotTable.
Peter Hibbs
Hi Jess,
Yes, the times and fonts can be sorted later, as you say.
Actually, after I posted my reply, I realised that my suggestion of a new field would not work for the column number allocation because you did mention in another thread about having a number of therapists. I then assumed that these would vary for each day which means that you need a different method to calculate the column number for each therapist for each day. You also mentioned earlier that you have seven rooms at your establishment so I guess you could have up to seven therapists working on any given day (although you only show five in your screen shot). If you have already created a query which returns a list of therapists for the day, would it be possible to allocate a number (starting at 1) to each therapist for that day which could then be used in the code to specify the column number. The Time column will be column 0 and then the therapists columns would be 1 to 6 (or however many you have). The only problem that I can see with that is if you were to step through the days, the columns for the therapists could change each day which could be a bit confusing for the users (but I don't suppose there is much you can do about that with this system).
Regarding the therapists work periods - how do you determine those for each therapist? I would think you need a table to hold the info for the therapists and then another table to hold the working days and times for each therapist which would be linked to the previous table. Do the therapists work the same days and hours for each week or are they completely random? How are you storing the work times? How often do they change? How do you process holiday periods? There are so many questions that need to be resolved for something like this. Perhaps you could post a screen shot of your Relationships window so we can see what tables (and fields) you have and how they are related. Or maybe you could post a copy of the database!
I see that BananaRepublic has suggested an interesting idea for showing the work periods available which I might try myself (not having used this method before). Not sure how well it will work in your situation as I don't have enough information about your set up but it is certainly worth looking into. If you are intending to use my suggestion of coloring the available work periods in white (or whatever) and the non available periods in grey then I think it would be easier to color the whole grid in grey initially and then color in the work periods in white for each therapist. But that can be left for the moment, your next task is to work out how to copy the appointment data to the correct grid cells for the correct therapists on the correct days.
Anyway, let us know how you get on.
Peter Hibbs.
JessicaZ
Hi
Odidn't really have a lot of extra time this weekend to work on anything further, but I will go ahead and post what I have here. I ended up switching to an mdb because I have access 2010 at home and access 2007 at work and keep getting that issue where if I work on a database in 2010 and then open in 2007 it isn't recognized. Since I'm not using anything specific to 2007 or 2010 yet, I figured I'd switch to mdb for now.
nyway.....
To answer your questions: Yes, I have a tables for:
Therapists (actually for all employees, but there is an indicator for if they are a therapist or something else)
Work Schedules
Appointments
Clients
Currently I am just using a static date in the code so I can figure stuff out, but ultimately I will obviously be making it dynamic based on the form date.
Yes we have 7 rooms but we could have any number of therapists working in a day - so having only 7 columns isn't feasible. (one therapist could work in one room from 9 - 1 then someone else could work in the same room in the afternoon, etc). Yes the number of columns will change daily, but that is no different than what they are used to with the paper method they use now. (only therapists who are working are on the schedule), so I don't think anyone will be confused.
HAs you say, my next step is to figure out how to get the appointments into the grid under the correct therapist. I feel like I should be able to match the Therapist ID in the column with the Therapist ID in the appointment and fill it in that way, but being a complete newbie at flexgrids, I don't really know how to reference the Therapist ID in the column, or even reference the time column to make it go to the correct time. iconfused.gi
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>BananaRepublic</div>
  <div class='postdate'>May 6 2013, 10:58 AM</div>
 </div>
 <div class='postcontent'>
  I'm not sure if this will be the best solution but continuing on my earlier suggestion:<br />You still need a table that will list all available time blocks -- Assuming for a minute that you work in time block of 30 minutes, you need to create a table that provide full range of time blocks available (from your opening time to closing time).<br />Next, you need a Tally table -- simply a one column table that has sequential numbering from 1 to oh, I don't know... 10,000? Whatever is a big enough number. You will use that to generate a full range of dates for range you're interested in.<br />Example, if you want to list all dates between say, 2013-05-10 to 2013-07-10, then you would create a query similar to this:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->SELECT DateAdd("d",[tsysTally].[val],#2013-05-10#) AS GivenDate<br />FROM tsysTally<br />WHERE tsysTally.val <= DateDiff("d",#2013-05-10#,#2013-07-10#)<!--c2--></div><!--ec2--><br />(saved as query "qryDateRange")<br />That will give you a full list of dates that are valid between those range you specified.<br />So to build all possible combination of time block for a room for a date, you need to do a cartesian join between those 3 tables:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->SELECT ???<br />FROM qryDateRange, tblTimeBlocks, tblRooms;<!--c2--></div><!--ec2--><br />(saved as query "qryAvailableTimeBlockByRoomAndDate")<br />Then, you join into the appointments and the schedule:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->SELECT ???<br />FROM qryAvailableTimeBlockByRoomAndDate AS avail<br />LEFT JOIN tblAppointments AS appt<br />  ON avail.RoomID = appt.RoomID <br />  AND avail.TimeBlock = appt.TimeBlock<br />  AND avail.GivenDate = appt.AppointmentDate<br />LEFT JOIN tblSchedules AS sch<br />  ON avail.TimeBlock = sch.TimeBlock<br />  AND avail.GivenDate = sch.WorkDate<br />;<!--c2--></div><!--ec2--><br />You should then have all the information for each therapists' availability and when creating new appointment, you can look at infromation returned from schedule to determine if the therapist is available or not to fill in the appointment and you can use appointment to determine which room are filled or not and program accordingly in your FlexiGrid.<br />Hope that gives you some ideas. As you can imagine, though, doing that for several dates & therapist may be total overkill, but you also can simplify such as limiting to only a therapist at a time for example.<br />I'm sure Peter Hibbs will have other wonderful suggestions that may be easier to work with FlexiGrid.<br />Best of luck!
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>JessicaZ</div>
  <div class='postdate'>May 6 2013, 01:16 PM</div>
 </div>
 <div class='postcontent'>
  Thanks BananaRepublic, Any advice is appreciated and who knows, this approach may be exactly what I need <  I'll report back
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>Peter Hibbs</div>
  <div class='postdate'>May 6 2013, 05:14 PM</div>
 </div>
 <div class='postcontent'>
  Hi Jess,<br />OK, I have had a quick look at your database and there are a few issues.<br />You are using ADO references, is there any specific reason for this as I would normally recommend using the <b>DAO 3.6 Object Library</b> instead? This is built in to A2010 I believe so you don't need to set a reference to it.<br />I think the main problem at the moment is finding some way to tie the therapist names for any given day to the columns in the grid AND using the same column reference in the appointment table so that when you display the appointments for the day, the correct appointments appear in the column for the appropriate therapist. This is a bit tricky but I have an idea which I will try out tomorrow. My plan is to add a new Number field to the Appointments table (called <b>ColRef</b>) so that when you create a new appointment for a client the column number will be saved in the new field in the appointment record. Then when the appointments are displayed on the grid, the code will use the value in the new field to determine which column to use. Of course, this relies on the fact that for any given day, the therapists displayed at the top of the grid will always be the same as they were when you allocated the therapists for the day. If this is not the case, i.e. you may change which therapists work in on which days <u>after</u> you have created an appointment, then this will need a re-think.<br />Can I assume that you will be creating new appointments for clients by clicking (or double clicking) on a cell in the grid in the same way as my demo databases? If so then this idea should work since we will know which row and column is being used and therefore which therapist. Let me know if you see any problems with this.<br />I take your point that therapists could be working in different rooms and so I would think that in addition to showing the client name in the grid (under the therapist name) you will also need to show the room number that the therapist/client has been allocated to. I don't think that should be a problem though. One more question though, what would be the absolute maximum number of therapists that could be working in a day. As I see it (from what you have said) you are going to need a column for each therapist that is working on a day. If this could be 15 then the column widths are going to be very small (unless you allow horizontal scrolling which would be very user unfriendly IMO). Obviously, even though you only have 7 rooms, you would need more than 7 columns if different therapists can use the same rooms at different times of the day. Please clarify!<br />Anyway, I will have a play with this tomorrow and see how it works out.<br />Peter.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>JessicaZ</div>
  <div class='postdate'>May 6 2013, 05:31 PM</div>
 </div>
 <div class='postcontent'>
  Hi Peter<br />Oused ADO because I was following a tutorial and that's what they used (before I started perusing your example databases). *shrug* I'm not tied to it.<br />Yter<br />Oused ADO because I was following a tutorial and that's what they used (before I started perusing your example databases). *shrug* I'm not tied to it.<br />Your other assumptions are correct - the part I am struggling with is getting the appointments to show up in the correct therapist column.  I do plan to add appointments by clicking on the cell and have a pop up form, very much like your example database.  You bring up a good point about therapists changing after allocating them for a day - it could happen (someone quits or is hired or takes an unexpected day off, for example).  I don't think we need to show the room number the therapist has been assigned to since they pretty much deal with that amongst themselves, we (the receptionists) generally don't know what room they are in - they have a chart in the back where they put their name in the room they are using so the other therapists know who is where.  I believe that the maximum number of therapists that would work on a given day is 14, assuming a day shift and a night shift in each room (thought I don't think we ever really have all 7 rooms filled all the time).  So yes, in order to see the appointments for each therapist, we will most likely end up with horizontal scroll bars (much less user unfriendly than a paper schedule!)<br />I now have to take my daughter to a school function so I probably wont get a chance to do anything with it again tonight! (Grr)
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>Peter Hibbs</div>
  <div class='postdate'>May 7 2013, 03:46 PM</div>
 </div>
 <div class='postcontent'>
  Hi Jess,<br />I have added a bit of code to your form to do the basics - that is show the therapist names, show the time slots (much the same as you had) and also to show the client names in the appropriate grid cells (see screen shot below). The grey cells are time slots that are NOT available for that therapist and the white cells are the times available. The yellow cells are the appointments for each client. I don't know whether you intended to use any sort of color coding for appointments but this can be added easily enough if you do.<br />A couple more questions :-<br />When you want to create a new appointment for a client you would click on the grid cell under the appropriate therapist and on the row for the required time slot which will pop up a form which will allow you to add an appointment or edit an existing one. Now how do you intend to choose a client for the appointment? Since you have a table for clients I guess you have (or are intending to make) a form for clients so that you can add new ones or edit existing ones. So on the pop up form would you provide a Combo box or List box in which you could choose the client for the appointment or would you click a button which would open another form that shows a list of all clients and then choose one there or what? Also, roughly how many clients do you have, i.e. is it dozens, hundreds, what?<br />I see in the Appointments table you have added four fields called LastName, FirstName, MI and Phone. What are these fields for? If they are fields for the client details then you do not need them because the appointment is linked to the Clients table which can supply that information (via a query, of course). If they are there for some other reason then please explain (and what does MI stand for anyway).<br />What other client information would you want to display on the grid (if any)? As you can see I have just added the client name (because that is all you provided) but is there anything else you want to see such as phone number, email address, etc (although bear in mind there is a limited amount of room available for some appointments).<br />Since it seems that each day can have different therapists working at different times of the day - how do you intend to set up that information? Are you planning to have a form which shows all your therapists and then have some method to allocate them to dates and time slots, etc. Perhaps you could explain your thinking here because the data stored in the WorkSchedules table will have an impact on the design of the code for the main calendar form. I am thinking that it may even be an idea to use a FlexGrid control for that form as well but until I know what you are planning, I can't be sure.<br />Anyway, let me know when you can and feel free to provide any other information that would be useful.<br />Peter.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>JessicaZ</div>
  <div class='postdate'>May 7 2013, 07:19 PM</div>
 </div>
 <div class='postcontent'>
  br />It looks great!  Peter, you are a rock star!<br />All right, so to answer your questions:  Yes, my intent is to click the grid and open a form to book the appointment, Since there are thousands of clients in the current database (after many years of business), I was thinking of a search box to filter down a combo box (like, search for last name "Johnson" and all the Johnson's pop into the combo then you can choose one to add to appointment)<br />Ah, yes the Appointments table came from the current database the spa uses (they keep client records and list daily appointments, but they book appointments on paper then type them into the database to print off  <....hence my project).  I wanted to sort of start with what they have already and build from it. So, yes, those fields are not needed.  (MI means middle initial <)<br />We need to see the client name, service type & length (example: "Chair Mx - 30" ["Mx" being shorthand for massage, I will probably be changing the service list to have the shorthand, and "30" being the number of minutes])  and phone number on the grid (though actually we may be able to forego phone number since all we use it for is confirmation calls and I was planning on doing a query to list the clients we need to call for the next day).  <br />Therapist schedules - my thought was just to have a form with a therapist list and a subform to input the schedules.  Either that or figure out a way to input a "regular" schedule, the problem with that is therapists's schedules are not always the same each week.  Your idea of possibly using a flexgrid for the schedules is intriguing...<br />OK, so I am attaching a screenshot of the database they use currently so you can kind of see what I am building off of (Its proprietary otherwise I would upload the database).  I will probably keep the form on the left as the client info form since that holds everything about the clients.  The form on the right is where they type in the list of clients for the next day to print off for each therapist.  I don't think I will have any use for it once I am done here, but i will still have to be able to print off the therapist daily client list and travelers (a traveler is a sheet with client info on it that shows a short synopsis of the last 3 appointments and any medical issues the therapist needs to be aware of).  You don't need to worry about any of that though, I think I have a handle on it, I just wanted to give you a bigger picture of what I am doing.<br />I am so excited to learn how to make this work!<br />Edited to add:  Also, in your screenshot Michelle is on the right but she should be on the left because her work shift starts at 9, then the 11:00's should be next, and Dustin at the end since he works late
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>Peter Hibbs</div>
  <div class='postdate'>May 9 2013, 10:00 AM</div>
 </div>
 <div class='postcontent'>
  Hi Jess,<br />i>I was thinking of a search box to filter down a combo box (like, search for last name "Johnson" and all the Johnson's pop into the combo then you can choose one to add to appointment)</i>  Yep, I think the same. Now you could use a Combo box but what I usually do when having to search through thousands of records (and WOW, I did not expect that many!!) is to use a List box which can be 'filtered as you type'. What I am doing at the moment for you is trying to work out the best way to enter new appointments using a pop up form (as I mentioned earlier). The tricky bit is finding a way to pass the information from the Flex Grid control to the pop up form so that the form knows which time slot and therapist the user clicked on. Also, of course, you will need facilities to alter existing appointments and perhaps delete an appointment so what I will do is design a form with the List box for you to try out. If you prefer to use a Combo box control then you should be able to change it easily.<br /><i>So, yes, those fields are not needed. (MI means middle initial)</i>  Doh!!! I should have guessed that. Actually I think that you should include the middle initials (if any) in the client name on the grid in case you happen to have two clients with the same first and last names (and hopefully a different MI).<br /><i>We need to see the client name, service type & length</i>  OK, as you suggested, it might be a good idea to use an abbreviated version of the service type which we can add in when you have done them, it would save a bit space on the grid.<br /><i>my thought was just to have a form with a therapist list and a subform to input the schedules</i>  OK, sounds good. You probably need to make it as easy to use as possible as I guess you will be doing that very frequently. A Flex Grid type form might help or might be a bit 'overkill', something more to think about later.<br /><i>Also, in your screenshot Michelle is on the right but she should be on the left because her work shift starts at 9, then the 11:00's should be next, and Dustin at the end since he works late</i>   Yes, well, there was a good reason for that. The problem is that you are using the <b>StartTime </b>field to set the sort order in the query which determines the order that the therapists appear along the top of the grid and if you should happen to have two or more therapists that start at the same time, I am not sure that Access will keep the original sort order. To avoid this problem I used the <b>ScheduleID </b>field to sort the query which then produced the order you see. However, now that I see the logic you are using I have changed it slightly so that the sorting is done on the <b>StartTime </b>and then the <b>EmpLastName </b>fields so that if you have several therapists starting at the same time they will then be ordered by their names. If you have some other thoughts on this then let me know.<br />Now, I am having a little bit of a problem with your table schema and the Flex Grid. In the <b>Employees </b>table you have a field <b>EmployeeID </b>which is a Text field holding the employee initials and is also the Primary key linked to the other tables. In the Flex Grid I need to store a reference to the employee (i.e. therapist) so that when the users click on the grid, the code can determine which therapist has been selected. Now the easiest way to do that is to store a unique value in the <b>ColData </b>store in the grid for each column but I can't because that store will only accept Integer values. So what I would really like to do is change the <b>EmployeeID </b>field to a Number type (actually an AutoNumber type) field which would make the code a fair bit simpler to write. Also, we would add a new field to hold the therapist initials separately as I think you probably use those elsewhere. This would also mean changing the Foreign Key fields (<b>TherapistID </b>in <b>tblAppointments </b>and <b>EmployeeID </b>in <b>tblWorkSchedules</b>) to Number type fields. With the tables that you have in your example database this is not a problem, they can be changed and relinked easily enough, what I am wondering though is - do you have other forms, queries, tables, etc, etc which use any of these fields which could mess up other parts of your database? If not, then I suggest we make those changes, if you do then we need to work around it (which I can do but it will be a little bit messy). Let me know what you think.<br />One other thing - how many different time periods can you allocate for sessions, you show just 30 and 60 minutes in your tables. Is that it or do you have other periods that can used?<br />Peter.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>JessicaZ</div>
  <div class='postdate'>May 9 2013, 01:33 PM</div>
 </div>
 <div class='postcontent'>
  Hi Peter<br />Good ideas on all and good thought on the sort order too.<br />!--coloro:#0000FF--><span style=So what I would really like to do is change the EmployeeID field to a Number type (actually an AutoNumber type) field which would make the code a fair bit simpler to write. Also, we would add a new field to hold the therapist initials separately as I think you probably use those elsewhere... what I am wondering though is - do you have other forms, queries, tables, etc, etc which use any of these fields which could mess up other parts of your database? I think that is fine. Like I said, I started with an existing database and that is how it was laid out....you are correct that the initials are used elsewhere, I will have to dig a little to make sure, but I don't think it will be hard to switch, the current application isn't super complicated.
how many different time periods can you allocate for sessions I just put example data in the tables, but essentially, table massages can have anywhere from 30 minutes to 3 hours, in increments of 15 minutes (I have never actually seen a 3 hour massage, but it is available <) Chair massages are done in increments of 5 minutes, but since the book only has 15 minute time slots, we round up when booking the times. All types of massages have an extra 15 minutes in between them for clean up. At first I was going to just list all of the combinations of massages and minutes in the services table, but now I wonder if it would make more sense to just list the service names and then in the appointments table list the minutes (seems less redundant and there is already a minutes column in the appointments table)
Peter Hibbs
Hi Jess,
I have now got the main part working for the FlexGrid display and adding, amending and/or deleting appointments (see screen shots below). I will upload the new version in a day or so but before I do that, I have a couple more questions.
1. How exactly do you want the appointments displayed in the grid? In your last post you mentioned that you need to allow a 15 minute gap before the next appointment to clean up after a massage. If you look at the screen shot for therapist Michelle Nelson you can see that I have left a one row gap between appointments and for therapist Rick Pavlik there is no gap. The code is written so that you cannot overlap two appointments (an error message is displayed if you try this) but do you want the 15 minute gap automatically added to an appointment or would you just rely on the receptionists to leave a gap after some appointments? Alternatively, you could just add 15 minutes automatically to the massage appointments so that no gaps are visible on the grid (as for Rick) or, maybe, you color in those gaps in a different color. This is not a big deal but you might as well have it how you want at the start as these sorts of changes are easier to implement at this stage rather than later.
2. I have also written the code so that if you try and enter an appointment for a therapist which will finish AFTER his/her shift period, an error message will be displayed. Is this OK or do the therapists sometimes work longer than their shift time (as overtime perhaps)? If so, what would you want to happen if an appointment was scheduled to last longer than the allocated time - pop up a warning message and allow it or just allow it anyway or something else?
3. At the moment the appointments are all colored yellow (as you can see). However, the FlexGrid control has the facility to make any cell any color which you could use here. For example, you could show your different services in different colors or perhaps they could be shown in a color based on some other criteria such as Category (which you seem to have but not explained) or maybe on the Commission level or whatever. I am not saying you should, just that the option is available if it will be a useful facility for the receptionists to have.
4. In each appointment I am showing the client name, service type and appointment duration (as you suggested). Just wondering if you wanted the appointment duration shown in minutes (as I have) or in hours:minutes. Would your receptionists be happy, for example, with knowing that 105 minutes is 1 hour 45 minutes?
5. I see you have two other tables (tblCommission and tblSecurityGroups) in the DB which are not joined to any of the other tables. I think they probably should be although the Commission table has a lot of repeating records so I think this one should probably be changed. Having said that I think you said earlier that this one could be done differently.
6. I was think a bit more about the horizontal scroll bar issue, obviously this works as is but it would be nice to get rid of it, if possible. Apart from using a much wider screen (which would be my preferred option) you could use the FlexGrid control Column Width property like this - you could make all the columns narrower, say about half the width they are now, and then make the one that you want to see the normal width. Then, if the user wanted to see the full data in a column they could click on a cell in that column and the code could expand that column and make the previous column width narrow. It might be a bit disconcerting to see the columns changing their widths when you click on them but it might work (although I haven't tried it myself). Anyway, just a thought but let me know if you think it is worth testing out.
Anyway, let me know what you think about the above when you have time and feel free to ask for any other options that I have not mentioned.
Peter.
JessicaZ
Peter, it looks fantastic!
On answer to your questions:
1. How exactly do you want the appointments displayed in the grid? This is a good question...Right now its automatically added by the receptionists when we book an appointment. But if someone requested an appointment at 8 and we close at 9, obviously we wouldn't need the gap. And in some cases we need 1/2 hour between appointments instead of 15 minutes. I had actually been trying to figure out a couple weeks ago which would be the best way to go about it...one of my thoughts is to have a "service" called "break" or "clean up" or something like that which is only 15 minutes long that we could put on as needed (and is a different color as you suggested) - and if we needed 2 we could add 2, but if we didn't need any then we wouldn't add any. Not sure if its the best way to go, but it seems logical.
2. what would you want to happen if an appointment was scheduled to last longer than the allocated time? I think just a pop up warning - occasionally a therapist may say they will stay late or arrive early for a client, so I would want to be able to book it if necessary
3.FlexGrid control has the facility to make any cell any color which you could use here. Yes, I think color coding based on Service type is what is needed here. (And the Category field was one I thought might be needed, but now I think I will remove it)
4. Would your receptionists be happy, for example, with knowing that 105 minutes is 1 hour 45 minutes? Yes, we would actually but 1 1/2 hours in the book rather than 90 minutes
5. I see you have two other tables (tblCommission and tblSecurityGroups) in the DB which are not joined to any of the other tables. I think they probably should be although the Commission table has a lot of repeating records so I think this one should probably be changed. Having said that I think you said earlier that this one could be done differently. Yes, tblCommissions is from the current database and I haven't worried about optimizing that yet, but ultimately it is used by the owners to pull a report on what services the therapist did and the commission they will be paid for that service. tblSecurityGroups was my start at security, basically my thought is that the owners will get full access to the database (be able to run reports, etc) and receptionists will only have access to book and add client notes. Therapists would have access to read only. It will be tied to the employees table.
6. I was think a bit more about the horizontal scroll bar issue, obviously this works as is but it would be nice to get rid of it, if possible. Apart from using a much wider screen (which would be my preferred option) you could use the FlexGrid control Column Width property like this - you could make all the columns narrower, say about half the width they are now, and then make the one that you want to see the normal width. Then, if the user wanted to see the full data in a column they could click on a cell in that column and the code could expand that column and make the previous column width narrow. It might be a bit disconcerting to see the columns changing their widths when you click on them but it might work (although I haven't tried it myself). Anyway, just a thought but let me know if you think it is worth testing out. I know you don't like horizontal scroll bars < But narrowing columns wouldn't work because we need to be able to see all the available therapists at one time - so if a client calls in and asks if we have an appointment at 3:00 we can see which of the therapists are open at 3:00 and which have clients.
So, another piece that you may (or may not) have an idea about is this: The current paper booking system is color coded with a system of dots and underlines so we can see at a glance a few things. I think the significant ones that would be useful to have are:
CAn indicator when the client arrives/checks in
An indicator that the client has paid/left
An indicator that the client requested that specific therapist
An indicator that the client is brand new to the spa
Obviously we aren't going to be able to do colored dots, but I was thinking maybe italicizing when they check in, changing the box color when they check out, Bold the name if they are brand new and use an asterix by the name if they are a request? Or something along those lines. Of course, I am assuming this means I will need to add check box fields for Request & New to the appointments table. I'm not totally sure how to do the check in/check out piece - more check box fields? So when we click on the appointment, we click a check box to check them in and a different one to check out? Or a field to enter the invoice number that they paid that would indicate they checked out? Can you tell I'm still brainstorming? iconfused.gi
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>Peter Hibbs</div>
  <div class='postdate'>May 13 2013, 01:41 PM</div>
 </div>
 <div class='postcontent'>
  Hi Jess,<br />See comments below (in blue) -<br />. How exactly do you want the appointments displayed in the grid? This is a good question...Right now its automatically added by the receptionists when we book an appointment. But if someone requested an appointment at 8 and we close at 9, obviously we wouldn't need the gap. And in some cases we need 1/2 hour between appointments instead of 15 minutes. I had actually been trying to figure out a couple weeks ago which would be the best way to go about it...one of my thoughts is to have a "service" called "break" or "clean up" or something like that which is only 15 minutes long that we could put on as needed (and is a different color as you suggested) - and if we needed 2 we could add 2, but if we didn't need any then we wouldn't add any. Not sure if its the best way to go, but it seems logical.  <!--coloro:#0000FF--><span style=OK, this is a bit trickier! I think that if you add a 'clean up' time period to the end of an appointment, it really needs to be associated with the appointment (rather than a completely separate record) because if you were to delete or move the appointment, you would want the 'clean up' cell/s to do the same. What I am thinking here is we add an Option control to the Appointments form so that when the user creates a new appointment - they could also select a 'clean up' period of 'None', '15 mins' or '30 mins' on the form and then the grid code could automatically add none, 1 or 2 time slots immediately after the appointment on the grid (and probably use a different color). Not sure how easy this will be to implement but I think it could be done. What do you think?
2. what would you want to happen if an appointment was scheduled to last longer than the allocated time? I think just a pop up warning - occasionally a therapist may say they will stay late or arrive early for a client, so I would want to be able to book it if necessary OK, in that case I need to change the code so that a user CAN create an appointment in one of the grey cells but perhaps pop up a warning message first, in case they did not mean to do that. The only problem I see is that if they wanted a therapist to start before 9.00 am we would have to change the first times to 8:00 am or whatever. What do you think?
3.FlexGrid control has the facility to make any cell any color which you could use here. Yes, I think color coding based on Service type is what is needed here. (And the Category field was one I thought might be needed, but now I think I will remove it) No problem here. I have changed the CategoryNumber field name to ColorCode which holds the Integer value of the BackColor for each Service type. Incidentally, do you already have a form which allows you to enter data into the Service table or is that still on your 'to do list'?
4. Would your receptionists be happy, for example, with knowing that 105 minutes is 1 hour 45 minutes? Yes, we would actually but 1 1/2 hours in the book rather than 90 minutes OK, will leave as it is for now.
5. I see you have two other tables (tblCommission and tblSecurityGroups) in the DB which are not joined to any of the other tables. I think they probably should be although the Commission table has a lot of repeating records so I think this one should probably be changed. Having said that I think you said earlier that this one could be done differently. Yes, tblCommissions is from the current database and I haven't worried about optimizing that yet, but ultimately it is used by the owners to pull a report on what services the therapist did and the commission they will be paid for that service. tblSecurityGroups was my start at security, basically my thought is that the owners will get full access to the database (be able to run reports, etc) and receptionists will only have access to book and add client notes. Therapists would have access to read only. It will be tied to the employees table. Hmmm. It sounds like you have more than one computer using this database! If so, could you give me a brief description of what you have got there. Are you using multiple PCs on a Network (and how many users) or one stand-alone PC? If multiple PCs - how is the database stored on the PCs? Is the database split into Front-Ends and a Back-End?
6. I was think a bit more about the horizontal scroll bar issue.... OK, forget that idea! I wasn't over keen anyway
So, another piece that you may (or may not) have an idea about is this: The current paper booking system is color coded with a system of dots and underlines so we can see at a glance a few things. I think the significant ones that would be useful to have are:
CAn indicator when the client arrives/checks in
An indicator that the client has paid/left
An indicator that the client requested that specific therapist
An indicator that the client is brand new to the spa
Obviously we aren't going to be able to do colored dots, but I was thinking maybe italicizing when they check in, changing the box color when they check out, Bold the name if they are brand new and use an asterix by the name if they are a request? Or something along those lines. Of course, I am assuming this means I will need to add check box fields for Request & New to the appointments table. I'm not totally sure how to do the check in/check out piece - more check box fields? So when we click on the appointment, we click a check box to check them in and a different one to check out? Or a field to enter the invoice number that they paid that would indicate they checked out? Can you tell I'm still brainstorming? I can (and it's making my brain ache as well!!). As you say, you would need to add more fields to the Appointments table but I don't think that is a problem. I think it will be more important to make sure that the system is easy for the users to use and understand without making it overly complicated. I am not sure that using italics and bold text will be very easy to see on a screen that is full of appointments, might have to try it and see. In my experience with the FlexGrid, using Bold text can sometimes make the display look odd. The problem with Bold text is that the text is considerably wider than normal text so the normal text that displays nicely in a cell will wrap around to the next line when made bold. It can be done but is not always what you want. Another option you could consider is using special symbols on the text for those indicators, the Tahoma font has a number of different symbols in the higher characters which might be useful. If you open the Character Map utility in Windows, select that font and scroll down you can see all sorts of weird and wonderful characters. Maybe you could use some of those to mark the appointments on the grid, let me know what you think. Anyway, I think, for the moment, we will leave these facilities and get the others above working first, we can come back to this later.
There is one other issue that you raised earlier and that is you may want to move a client appointment to another date or another therapist after the appointment has been saved. I have provided an option to delete an appointment which you could use and then re-create the appointment on another date but this is a bit messy for your users, especially if there are a lot of other settings to set up. I think it would be simpler for the user to have an option to do this in one operation. As I see it at the moment it would work something like this - let's say you have booked in John Doe for an appointment with therapist X on a certain date. The client then rings up the day before and asked to be moved to another date or time or even a different therapist. What your user would do is - find the original appointment and click on it to open the Appointment form. They would then click on a button (let's call it Move Appt or whatever) which would close the Appointment form (perhaps with some warning message on the main calendar form as a reminder) and then they would click on a blank time slot on the new date or time or therapist and the old appointment would be automatically deleted and then inserted into the new time slot. The Appointment form would re-open so that the user can confirm that the appointment has been moved correctly. Does this sound like it would work from your point of view?
There is also the issue of how to handle the situation if a therapist cannot get in to work but I will need to think about that some more (unless you have any thoughts).
Anyway, let me know when you can.
Peter.
Peter Hibbs
Hi Jess,
Thanks for the update. OK, I have made the changes I proposed except the 'moving an appointment' bit which I will have a look at tomorrow. See the screen shot below for an example. Note the 'Clean Up Time' cells after some appointments which can be set to 'None', '15 mins', '30 mins' or '45 mins' on the Appointment form with an Option group control. If the users click on one of these cells then nothing happens, if they need to change them they would open the appointment itself and make any changes there. You will be able to see how it all works when I send an update. Obviously you will be able to change the colors of the services yourself as they are stored in the Service table. I have made up a small form which allows you do this easily using the standard Windows Color Selector, I will include that as well if you find it useful.
The Time Slot text at the top of the form is a new facility - I found that when you try and select a time slot on a column at the far right of the grid, it was sometimes a bit difficult to see which row (and time slot) you were over so I have added this control at the top of the form which shows the time slot row that the mouse is currently hovering over.
One question - what would be the shortest appointment duration that you use in practice? Obviously 15 minutes is the shortest available but would you ever have one that short? If so then it is going to be difficult to show anything other than the client name in the cell. If 30 minutes is the shortest then you get two lines of text which should be enough to show the name, the type of service and the duration (and maybe some other symbols). If 45 minutes is the shortest then that does give a bit more scope for showing other information in each cell.
Also, this may be a good time to think about the other options you mentioned, that is indicating the appointment status on the grid like Checked In, Paid, Checked Out, etc. Have you had any further thoughts on this and specifically how you would enter this data on the forms? Perhaps a brief explanation of the procedure you would use for a complete appointment would be useful.
You also said you will be producing a number of printed reports for the owners, etc, you may find this Reports Selection Form demo useful for that. You also said you intend to provide a Log On form for the various users at some stage, I have created such a form for various other databases, if you would like a copy then I would be happy to pass it on, it may save you a bit of time. Just let me know.
Oknow you said you were waiting to see my version of the grid before you made any more changes to your database but it might save you a bit of time if you can create a form to set up the therapists work schedules as I have not done that. I have not made any significant changes to the WorkSchedules table (apart from changing the EmployeeID field type to Long Integer) so you could design a form to add the schedules to this table as you don't need the FlexGrid form to do that.
Anyway, will get back to you in the next day or so.
Peter.
JessicaZ
Yes, please do!
nything to make it easier - thank you!
actually yes, a 15 minute chair massage is the shortest we do, and we do them on occasion. In the paper book, since we add the extra 15 minutes in between appointments, we do have 2 "slots" to work with, it kind of makes me wonder if we should merge the cleanup time with the appointment so we have the extra room. iconfused.gif
All right, a tutorial on the workings of a salon!
-Client arrives and checks in at the front desk. Receptionist marks them as "checked in" (in the paper system we highlight them in yellow)
-Therapist takes the client back for their service
-After the service, the client comes back to the desk and pays for their service. They may or may not make another appointment (if they make another appointment, we mark today's apointment with a purple dot which means they "prebooked" their next appointment before they left).
-We mark the client as having paid on the paper book (highlight in orange) and note the transaction number next to their name (I was thinking we would have a field in the appointment form for the transaction number, it wouldn't have to show on the grid) So orange highlight means they have left and the transaction number means they paid, though really we do both at the same time so one indicator on the grid would suffice
More about the color coding system, so you have a full view:
- If the client requests a specific therapist when they book the appointment (either before they leave or if they call in for an appointment) then we make a green dot next to their name on the new appointment denoting they requested the therapist.
-if the client has never been to the spa before they get underlined in red so we know to give them the required paperwork
-if the client calls and books an appointment for later the same day they get an orange dot so the owners can track last minute bookings.
-If they call and book for the next day they get a blue underline which is a flag to the receptionist not to call and confirm the appointment (we call the day before so it wouldn't make sense for us to confirm right after they book the appointment!) AND once we do call and confirm, they get a blue dot to note that we have confirmed the appointment
Do you have a headache yet? Yes we go through a LOT of colored pencils!!!
HAs I mentioned before, I think the significant ones that would be useful to have are:
CAn indicator when the client arrives/checks in (currently: yellow highlight)
An indicator that the client has paid/left (currently:orange highlight & transaction #)
An indicator that the client requested that specific therapist (currently:green dot)
An indicator that the client is brand new to the spa (currently: red underline)

Thanks - I will check it out, and definitely I'd love to take a look at the log in form.
OK, I will change the employeeID to a long integer and get to work!

Please, take your time!
Peter Hibbs
Hi Jess,
I have changed the display format so that the 'Clean Up Time' is just added to the appointment time itself (as you suggested) and I think it looks better as well as providing a bit more space for text (see screen shots below). What I have done with the time displays is to show the number of minutes for the appointment and then if there is some clean up time added, I have shown that after the appointment duration, i.e. 45+15m is a 45 minute appointment plus 15 minutes clean up time.
I think one problem with the grid is showing all the information you need in the cells, especially for 15 minute appointments. For example, if you have a client with a long name (and I am sure there are some) at the moment the text in a cell wraps round to the next line if the text cannot fit on one line. This means, of course, that in this case the second line which shows the type of service and appointment times will be pushed down to the third line which may not exist for a short appointment. It is possible to disable the 'wrap round' facility which would mean that any text that did not fit on a line would be cut off. Now, how do you want to display your client names on the grid? I have shown them as First Name + Initials + Last Name, is this what you want? Do you even use first names or do you use titles, i.e. Mr, Mrs, etc. Do you want the Last Name to be shown first, i.e. Doe, John K, if so then you could disable the 'wrap round' option so that only part of the first name and initials would be cut off (which would probably not matter too much). Let me know what you think.
Other options to display more text could be :-
Make the cells wider. This would mean a bit more horizontal scrolling, of course, but then you are having to do that anyway.
Make the cells taller. This would mean a bit more vertical scrolling but again, you have to do that anyway.
These two options are easy enough to change in code so you could try out the demo before you decide anything.
HAs you now have each service type color coded on the grid, do you really need to show the service type in text in the cell? If you removed that text you would then have more room for the other items. It would probably take a while for the receptionists to get used to the colors but it sounds as if they use color coding a lot anyway so it may not be a problem.
Regarding the other indicators you asked for I have added three more fields to the Appointments table and done it like this :-
When an appointment is first created there are no indicators shown.
When the client arrives I have added the @ symbol after the appointment time.
When the client pays and leaves I show the symbol to show the appointment has been completed.
If the client requests the therapist I have added the symbol to the cell.
If this is the first visit to the Spa for the client I have added the symbol (couldn't find one that looks like a V). Actually I don't think we really need an extra field for this one since you can easily find the first date (and therefore appointment) that a client makes but I have done it that way for the moment as I am not sure if you had something else in mind.
I can see that there may be more options you will want to add later but I think you should have a play with what I have done so far before complicating things too much.
Let me know your thoughts on the above when you have a minute.
Peter.
JessicaZ
Peter, you have done sooooo much, and I cannot thank you enough! I'd buy you a beer but we don't live on the same continent (and personally, I prefer wine)
Instead of telling you all the tweaks, I think I'd like to have a chance to play with it myself and get to know how it works. Then if I need any help, I can ask (though you have helped so much already!)
But, to respond to your note:
Odon't think we need to show the clean up time - so instead of showing 45+15, we's just show 45 (that's how we do it on paper)
We do show the name as First + Last, though adding the initial is good.
You bring up good points about showing the appointment type and the cell width and height and the symbols - I think some of these things I will have to confer with the owners about and, as you said, play with it <
Jess
Peter Hibbs
Hi Jess,
OK, see attached zip for the FlexGrid form in your demo database. I have also included the Log On form and the a form to change the Service color codes. There is also a Word document file with a few notes to help you.
Let me know how you get on and if you need any further assistance.
Peter.
JessicaZ
Peter, you are amazing and truly deserve all tehccolades you receive!
havent yet looked at it, but I am sure you did a magnificent job. I will let you know if I have any questions!
JessicaZ
Peter: to answer your question, yes it will be split <
I have finally gotten a change to start digging into it, so stay tuned!
Peter Hibbs
Hi Jess,
i>"yes it will be split " OK, that is good.
"so stay tuned!" Will do and as I said, don't be afraid to ask any questions about the code, etc.
Peter.
JessicaZ
Hey Peter - question
in the FlexGridInitialise Funtion you have
CODE
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments WHERE DateValue(ApptStart) = " & Format(vDate, "\#mm\/dd\/yyyy\#") & " AND RecordID <> " & gMoveAppt)

Why did you use "\#mm\/dd\/yyyy\#" in the Format of the vDate? I've never seen that with all of the extra slashes and I'm not sure what they mean exactly.
I am asking because I am now getting error 3464, data type mismatch and I've narrowed it down to that where statement.
Thanks!
(I pulled your forms into the full database and have been incorporating them and it was working at first. i didn't change anything in the code so I wonder if I did something to a table that messed it up)
Peter Hibbs
Hi Jess,
orry, I had intended to explain the date thing but I forgot. As you probably know, the date format over here is dd/mm/yyyy which means that all dates in SQL strings have to be formatted to conform with the US format. Theoretically you don't really need to reformat the dates but I would advise that you leave it as it is so that your code will work in any country. The extra slashes are to make the date format also work in countries that do not use the slash character like France and Germany, etc where they use the dot character instead. You can find more details on Allen Browne's site here. Again, I realise that this is of no use to you but again, I suggest you leave it as it is because it should make no difference to the working of the code.
I am not sure why you get the error but I suspect that the field RecordID and the variable gMoveAppt or different data types which could give this error. Variable gMoveAppt is a Long Integer (unless you changed it) and field RecordID should also be a Long Integer type, can you check these types. If they are both Long Integers, then what value does the variable gMoveAppt have if you place a break point at that line?
Peter.
JessicaZ
Thanks, I thought it might be something like that.
MoveAppt = 0
I will double check the data types
JessicaZ
Peter - the problem was there were some blank dates & times in the data. Once I deleted the records with the blanks, everything is just fine <
Peter Hibbs
Jess - Ah yes, you have to make sure that there is a valid date in the two date fields.
ounds like you are making good progress, but let me know if you have any problems.
Peter.
JessicaZ
Just wondering if there is a way to have the appointment details pop up when I hover over an appointment? Well, either pop up or maybe pop into an unbound control?
Kind of like this example:
Peter Hibbs
Hi Jess,
OK, good question and the answer depends on exactly how you want it to work. The simplest method is to make sure that the cells have all the information you want to display and then copy the contents of the cells to the Tool Tips pop up form. To do that just copy the code below into the flxDailySchedule_MouseMove event (which is already in use). Just copy and paste this code into the event after the four lines of code that are already there.
Me.flxDailySchedule.ControlTipText = Me.flxDailySchedule.TextMatrix(Me.flxDailySchedule.MouseRow, Me.flxDailySchedule.MouseCol)
The problem with this is that the tool tips pop up can be a bit slow to appear and there is a slight flicker as the mouse is moved around. You can probably get round the second annoyance by comparing the Row and Col values and only updating the tool tip property if the values change, if you need more info on that then let me know. Also, Stephan Lebans has some code which can replace the tool tip pop up with a better looking form which may work for you. Have a look HERE for more information (although it is a bit complicated).
If you need more information than is in the cells, i.e. you need to load data from the tables, then that is a lot more complicated as you would need to use the Row and Column values to calculate the identity of the employee and appointment records and read the data directly with a recordset. If you really need that then let me know and I will have a look.
Anyway, try the above first, see how you get on and let us know.
Peter.
JessicaZ
Thanks Peter, Ill take a look. I'm not 100% sure I want to use it, but I thought that might be a nice feature to have to show more information about the appointments, especially for the appointments that only fill up 1 or 2 lines and end up cutting off some of the info.
JessicaZ
I'm making some progress and have come to a point where I want to ask for input from my bosses/coworkers...so I brought it to work with me and am getting a Run-time error '438': Object doesn't support this property or method on the flexgrids on their computer (Access 2007). I have used it on my home computer and my full time job computer with no issues, why would I be getting an issue on theirs?
Peter Hibbs
Hi Jess,
It looks like you haven't installed the FlexGrid ActiveX control on the work computer or you have but not registered it properly (and don't forget that the registration procedure is slightly different for 32 and 64 bit versions of Windows as I mentioned in an earlier post). Can you check that out and let us know. Also, this control will not work with 64 bit versions of Access (but I doubt that this is the case here).
Peter.
JessicaZ
Doh! I should have realized that - thanks once again Peter!
JessicaZ
Interesting turn of events here - I took it to test on the spa's computer and ma having issues. First, I did install the VB 2008 to get the Flexgrid registered, so that is not an issue. What I am getting is this:
irst when I open Access (2007), I am asked to log on. I had thought this was part of the database they are currently using, but it is evidently tied somehow to Access itself. I do have the log in info, but I'm not sure if this is causing any of the other issues I will describe.
Second, I am getting Run time error 438, object doesn't support this property or method for the flexgrids. As I said, I installed VB 2008 and flexgrid works fine in that so it should be registered. So I thought "Hmmm, maybe I will just replace the ones in the database with new ones since the current ones are causing an error" BUT when I go to insert the FG, it tells me the database "doesn't support this active x control"
Third, if I go to the already present FG controls and double click on one in design view I get "Because of your security settings, this control is disabled. To modify your policy and enable the database, use the message bar" There is no message bar and because it talks about the security settings, I wonder if it has something to do with the logging on I mentioned before.
Thoughts?
Peter Hibbs
Hi Jess,
Well, I don't have all the answers but here are some thoughts :-
You didn't need to install VB 2008 on the works computer in order to use the database (unless you are planning to make changes to the database at work) but it should not matter anyway. I am not sure that installing VB 2008 automatically registers the FG control, maybe it does if you say it works. How did you check that anyway, did you write a VB program using the control?
Odon't know what is happening with the Log On issue, I can't see how that is affected by the FG control (although it may be that the control is not registered which can cause an error on start up).
The fact that you are getting the '438' error seems to indicate that the control is not registered correctly so I think you need to check that out first. Did you copy the FG control (MSFLXGRD.OCX) to the works computer and register it with regsvr32.exe? If not then I suggest you do that (and remember to check whether the OS is a 32 or 64 bit version and use the appropriate folder).
I have never encountered the second error ('enable the message bar') so I can't help with that although if the control is not registered correctly, it may be connected with that.
What I suggest you do first is copy my original Flex Grid Demo database program to the works computer (make sure it is in a 'trusted location', of course) and see if that works. If it fails then it is pretty certain that you have not installed the FG control and registered it correctly.
If that works, then try creating a new database on the works computer and then create a form and try to copy the FG control to the form. If that works then it shows the control is registered and that you have installed VB 2008 correctly.
If you get this far then try your database again but bypass the Log On form by holding down the SHIFT key while the database opens. Then you should be able to open the form with the FG control and see if it works. Also, if you get this far, check the References in a VBA window, you should see the FG control as one of them.
Other than that I cannot think of anything else to suggest. Let us know how you get on.
Peter.
JessicaZ
Thanks Peter, I was able to get it to work - I had to register the control with the Command Prompt console in administrator mode, but I got it to work finally!
xcept.....
I keep getting this error with various events on the grid.
"The expression MouseDown you entered as the event property setting produced the following error: There was an error loading and active x control on one of your forms or reports"
It's happened with mousedown, selchange, mouseup, Click, dblclick, keydown, mousemove, scroll
I changed the error trapping to Break on all errors, but it doesn't show me where the issue is actually happening.
Thoughts?
Peter Hibbs
Hi Jess,
don't know the answer (I have not come across that error before) so you will need to do a bit of trouble-shooting yourself.
What I would suggest is to first install my Flex Grid Demo database on the works PC and see if that runs without any errors. If it does - then that means that the FG control has been installed and registered correctly on the PC so the problem is probably with your database. If the demo fails as well with the same sort of errors then it probably means that there is some problem with the PC, which could be with Windows, the version of Access or the FG installation.
Assuming that the demo works OK, the next thing to do is remove the error trapping in your database which should then identify which line of code is failing. To do that you should 'REM out' the line of code - On Error GoTo ErrorCode in the flxDailySchedule_Click routine on the main form frmDailySchedule (if you are not sure what 'REM out' means then post back).
Are you using the database that I posted or have you added a lot more code to it or are you using your original database or what. The reason I ask is because I don't think we are using some of the events that you mentioned like SelChange and MouseUp, etc so I don't know why you should be getting errors on those events. Maybe you have changed something which has broken it!
Anyway, try the above first and let me know how you get on and then we can go from there.
Peter.
JessicaZ
Thanks again Peter. I know its odd - there is nothing in those events. I haven't done a whole lot of messing with your actual flex grid code - some minor tweaks here and there, but nothing major. I have been googling the issue in my spare time and it seems to point to a missing reference, but I've checked and there isn't one missing.
just downloaded and tried your demo database and it is giving me the error too so obviously its the computer. Back to work.....
ETA: If only everything in life were that simple...I unregistered the control and then replaced it with a different copy and reregistered - viola, it works. Obviously something was wrong with my ocx file
Peter Hibbs
Hi Jess,
Could be, or maybe it was the older version. Anyway, good to know everything is working OK now. Let us know if you have any more questions.
Peter.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.