Subform label creation for time span

[nightsins]

Hi all,



I'm an access newbie, and have a two part question so please bare with me if my terminology (or method of wanting to do this) is incorrect. First, for ease of simplicity, I want to select a start time and an end time from two separate combo boxs and have the values auto populate the table fields with the inbetween. I.e. I select 6am for the start, and 3pm for the end and hit add. This will write the ID associated with PersonA into columns 6am and 3pm. But how do I fill the in between fields automatically ( 7am, 8am, 9am, and so on)?

With the above in mind, in my subform, I would like to have this visually represented with labels as 6am through to 3pm, with the intention of later adding controls to edit 1pm's label or 2pm's label to show different colors for different assignments during that time.

As I am still learning about VBA and access, I just do not have the experience or knowledge to tackle a new problem such as this one. so if someone could please point me in the right direction I would be more than thankful!

Cheers,

[CJ_London]

sounds like your tables are not normalised or I am unable to picture your requirement. Please explain a bit about your business and what you are trying to achieve.

[nightsins]

sounds like your tables are not normalised or I am unable to picture your requirement. Please explain a bit about your business and what you are trying to achieve.

thanks for your reply. I doubt it's a normalization issue at this time...

Let's say there are 96 quarters to 24hours, and 32 quarters in 8 hours. So from 6:00 am to 2:00 pm there are potentially 32 options. Normally, 6am - 2pm will be uneventful and will not require modification, so allowing a user to select 6am - 2pm when they create the initial entry for the day - will save a load of time. (question 1 refers here). When 6am and 2pm are selected, instead of having the user also select 6:15, 6:30, 6:45....1:45pm and 2pm, I would like them just to select 6am and 2pm and have the rows under their respective columns auto populate with the value of that user. i.e.:

tbl_worker
WorkerID (key) | Name
2 | Smith, John

tbl_schedule
ScheduleID (Key) | 6am | 615am | 630am | and so on to fill out 24 hours.
1 | 2 | 2 | 2 | ... so 6am to 2pm have values to return wheras 2:15 to 5:45 the next day have NIL...and for statistical purposes later on this will be important.This is a crude example, but I hope it illustrates what I am trying to accomplish.

Think of a Gantt table, and each block needs a value in order to be represented; however, I plan on just returning tiny labels or text boxes for each time increment between the start and stop time with the assistance of you good people and VBA within the subform. The reason I want it to have a visual reference is for question 2...Sometimes, an special assignment will be tasked. I want to display for the end user whos working on which assignment for that 30min or hour block through use of color coding their labels.

Quick recap:
1) Looking for a way to auto fill the gap of 32 quarters between column X (6am for example) and column y (3pm for example) with the user's id number. If the user is present for 8 hours, 32 labels generated in the subform of the column name.
2) To fill the gap, I want to use a text box to represent each time increment (yes there will be many, and yes tiny tiny tiny ). The reason for this would be for my next step, allowing the user to click on label and modifying say 1pm to 1:15pm to a different color to represent a different task.

I am open to suggestions on how to proceed...I've checked out gantt examples here, and some are cool, but the only one which comes close has its output in a report and the labels are already created along with an excel looking grid...

[CJ_London]

your tblSchedule is not normalised and you are missing other tables as well. As a rough guide I would expect something like


tblWorker
WorkerPK
WorkerName

tblTimeslots (your tblSchedules)
TimeslotPK
TimeSlot

tblTasks
WorkerFK
StartDateTime
EndDateTime
Task

on a form you can set the start/end times by using a combo with tblTimeslots as a rowsource and combining with a date selection control

in a form, you can have your 32? 96? unbound controls in a row but populated with a formula either as the control source, utilising conditional formatting or in vba depending on requirements

basic formula would be

x >=startdatetime and x<enddatetime

where x represents a time element such as 6:15

[nightsins]

Thank you,

I'll restructure the tables.

As per creating the labels or such based on the time range selected, is there a VBA code snippet that you know of or a decent site with examples? I rather have the application make the labels or buttons for each row displayed in the subform. Or am I stuck making my labels and having them invisible until activated?

[CJ_London]

If this is multi row, then you just define for the one row and will need to use conditional formatting - and will need to be textboxes since labels do not have conditional formatting. You can make a textbox 'invisible' by setting the back colour to the same as the form - and why would you want to hide them? if hidden a user cannot select them