Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Interface Design _ Suggestions: Mouse Drag To Hi-lite Grid For A Schedule

Posted by: slacka Aug 16 2019, 03:22 PM

I'm making some scheduling software.
On my 1day view, I would like a grid, each line representing the hours scheduled for an employee.

Grid with hour header and then 15 minute segments:
7AM 8AM 9AM 10AM etc.
||||||||||||||||||||||||||||| <---each segment representing 15mins.
see attachment

Then i'd like to be able to mouse click and drag to highlite each segment's background color to "draw" the shift range.

So I'm a self taught access dev, so i don't know all the best ways to go about these things. I started with just an hour grid (with 1hour segments) for 25 employees, and using textbox controls for each segment, and i got some "drag and draw" functionality working as desired, but when i went expand to 15 minute segments, i found out there is a limit of like just over 700 controls per form. =(
So just now i decided to try and maybe do this with an unbound object frame of an excel spreadsheet. (i have no experience doing this with anything but a static view of excel). I'm sure i can reimplement the drag/draw code in excel to get the same effect, but is that possible with an embedded excel file? and if so, can yall give me any tips?
or suggest a better solution for what i'm trying to do?

thanks a bunch.


Posted by: dale.fye Aug 16 2019, 04:41 PM

You can have over 250 columns (I think the exact number is 255) in a query, so you might consider creating a query that generates a separate record for each 15 minute time interval, then make a crosstab query from this, max of 96 columns for 24 hours, plus the name column I assume is on the left of each of these, followed by a form (probably a continuous form, but a datasheet might work as well).

The real challenge will be selecting a continuous set of these columns and acting on the selection. You will not be able to click and drag across multiple time segments, instead, you will have to use shift-click and ctrl-click to select more than one column associated with a particular row.

You might want to look at my article about using and specifically look at the section on working with data sheets to get some ideas on how to accomplish this. It starts out with using the Form_MouseUp event to determine a couple of values and then pass that information to another function. In my sample, I use the row and column counts to determine what shortcut menu to display, but you could use that info to popup another data entry form to enter the info that would be included in those time periods.

Posted by: slacka Aug 16 2019, 05:24 PM

Thanks for the suggestion.
I just had lunch and think i might make a subform for each row and try and use my current code. we'll see how it goes.

Posted by: Peter Hibbs Aug 17 2019, 01:47 AM

Hi Slacka,

I think this probably does what you want.

Peter Hibbs.

Posted by: jleach Aug 17 2019, 05:15 PM

I will quite happily endorse Peter's tool: his work is top-notch and I've "borrowed" many times in the past.

Posted by: Mickjav Aug 26 2019, 11:34 AM

I will quite happily endorse Peter's tool: his work is top-notch and I've "borrowed" many times in the past.

Same Here Holiday planner, Guantt Chart and his amazing calendar

Posted by: slacka Sep 2 2019, 11:21 PM

I don't need a 24 hour scheduler. Anyways, i am accomplishing what i desire. Not unlike the usual, the program has forced me to use a better design haha. I'm accomplishing what i had before, I'll share some screenshots when i'm done.

Posted by: Peter Hibbs Sep 3 2019, 03:13 AM

Hi slacka,

OK, good to know you have got your database working and we would be interested to see some screen shots when you have them.

Good luck with the project.

Peter Hibbs.

Posted by: slacka Oct 2 2019, 03:42 PM

Still a work in progress, but here's some of what i have so far.

in 1week view, you can navigate by payperiod (everytwoweeks) or by week, filter to the logged in user, or select employee from dropdown, see hours worked of the scheduled hours (since we also use this system to track when people come in and out of work).
in 1day view, employees can be added to the schedule in different departments, gant style chart (15 minute segments), right now supports single shift mouse-drag (when enabled via checkbox). Or alternatively, by scrolling right, shifts can be entered directly. We allow the potential for two shifts in a day, in the case of split shifts.

only downside, is the design relies on pre-made controls and therefore currently has max support for 50 employees.

figured i'd share updates.