Solved Schedule grid (1 Viewer)

[klsblues]

Hi there,

Is it possible to create a schedule grid like the attached picture?

I haven’t started this yet, so I’m open to all ideas, but I was thinking along the lines of having an Employee table (with all the employee details in), a Schedule table (which would have date, start time and end time) and a job placement table with the job placement details in.

I was hoping to use a form which would have relationships from the Job and the schedule to the employee and each employee will be setup (weekly) from there – and edited if things change.

The issue I have is that my boss wants to see the information displayed like the attached picture and when any ‘cell’ is clicked on, it will open the form with the placement and employee details on.

It needs to be dynamic to allow many weeks ahead to be scheduled and viewed.

Can this be done – or something similar?

Regards - Kev.

 

[MajP]

Yes. That cannot be done in native bound form, but you can do that with a little code and potentially a crosstab query. The trick is do not try to make your tables look like that. That presentation is likely done through some code.

Your data needs to be stored in a normalized fashion and you can display that way.
tblAppointments
-- appointmentID
-- appointmentStart (date time)
-- appointEnd (date time)
-- personID_FK (foreign key to the person's table)

To enter data you will probably need a pop up when you click on a cell, but unlikely to allow you to type directly into the cell.

This thread shows several calendars that present information in a way very different than the table structure.

Date Pickers, Calendar Controls, and Calendar resources

There has been some recent interest in a replacement for the MS calendar control that existed prior to 2010. I have been collecting date pickers, calendar controls, and calendars for a long time and thought it would be a good idea to have one thread with lots of good examples. Some of these...

www.access-programmers.co.uk

If you can provide your db with the tables and some representative data we can probably help build something like that.

 

[theDBguy]

Just to add something, you could also try to search for gantt chart implementations in Access. Good luck!

 

[klsblues]

Thank you both for your replies.

I haven't started this project yet, so I'll put together the tables and a form and I'll also check out Gantt charts and let you know when I've done that.

I have a funeral today, so it will probably be tomorrow before I can get to it.

Thanks again and I'll post something later this week.

Regards - Kev.

 

[klsblues]

Hi there,

I have put together the barebones of what I need and added it to this thread.

I have the relationships for each job, so that an employee and a place can be selected against each job and I have created the Crosstab query (qryXTabSchedule). The query populates as I need it to, but how do I create a form to display the information like it is displayed in the crosstab query and allow the user to select the any of the populated (time) fields and open the frmJobDetails against that selection.

Any help will be very much appreciated.

Thanks guys.

 

[mike60smart]

This is a very good example provided by Davegri

 

[klsblues]

Hi Mike60smart,

Thanks for this and for taking the time to look at my issue, but the 'standard' calendar view is not suitable as there could be as many as 50 or 60 candidates placed in any one day per week. This means that I need to display as per the grid/spreadsheet above and be able to click on any of the time 'cells' to open up the form with the job details.

Unfortunately my VBA skills are not that good, but if the calendar example you suggested could display a week view with each day across the top and the day fields be the height of the form (top to bottom for one day, next to each other), this could well work.

Thanks again - Kev.

 

[MajP]

The query populates as I need it to, but how do I create a form to display the information like it is displayed in the crosstab query and allow the user to select the any of the populated (time) fields and open the frmJobDetails against that selection.

The form takes a little code. You will have your Generic textboxes at the top of the form. You will have a feature to select the start date. This will add a filter to the xTab query definition to just those dates. It will then modify the Column headers to pivot into. This will give you a xtab for the period desired. Now you have to modify the control source of the columns to match the xtab column headers.
Now when you double click on a cell it opens a form to allow you to edit that time slot for that person. This means you now can add / edit periods using just your grid and a pop up.

Sounds like a lot, but not that bad. I will demo when I get time.

 

[klsblues]

Thanks MajP - I await your help!

Regards - Kev.

 

[isladogs]

Have a look at the interactive Gantt chart scheduler demonstrated by Aleksander Wojtasz to the Access Europe User Group in July

Interactive Gantt Chart Scheduler in Access

This month’s session will be led by Aleksander Wojtasz and used to demonstrate the use of his interactive Gantt chart scheduler in Access

www.isladogs.co.uk

There is a video of the session on YouTube and an example app you can download

 

[MajP]

There are a lot of moving pieces to this. If Access had a native Grid control this would be a piece of cake.

1. First thing you need is to get the start date for the week in question. Here is a function where you would pass in a date. You want to start on Saturday

Public Function GetStartOfWeek(Optional dtmDate As Date = 0, Optional StartOn As VbDayOfWeek = vbSaturday)
  If dtmDate = 0 Then dtmDate = Date
  Do Until Weekday(dtmDate) = StartOn
    dtmDate = dtmDate - 1
  Loop
  GetStartOfWeek = dtmDate
End Function

2. You want a xtab that pivot into the seven days for that week. Here is a function that you pass in a start day and gives you the string

Public Function GetPivotIn(startDate As Date, Optional NumberDays As Integer = 7)
  Dim strPivot As String
  Dim I As Integer
  Dim fDate As String

   For I = 1 To NumberDays
    fDate = Format(startDate, "MM/dd/yyyy")
    If strPivot = "" Then
      strPivot = fDate
    Else
      strPivot = strPivot & ", " & fDate
    End If
    startDate = startDate + 1
  Next I
  strPivot = "IN (" & strPivot & ")"
  GetPivotIn = strPivot
End Function

That makes a string like

IN (09/23/2023,09/24/2023, 09/25/2023,09/26/2023,09/27/2023,09/28/2023,09/29/2023)

Now you can modify the total xtab
3.

Public Function GetPivotSQL(Optional startDate As Date = 0)
  Dim qdf As QueryDef
  Dim strSql As String
  Dim strPivot As String

   strSql = "TRANSFORM First(Left([ScheduleStartTime],5) & ' - ' & Left([ScheduleEndTime],5)) AS Timings "
   strSql = strSql & "SELECT [CandidateFirstName] & ' ' & [CandidateSurName] AS Candidate, tblPlacement.PlacementName, tblJob.JobRef "
   strSql = strSql & "FROM tblPlacement INNER JOIN ((tblCandidate INNER JOIN tblSchedule ON tblCandidate.CandidateID = tblSchedule.ScheduleCandidateID) "
   strSql = strSql & "INNER JOIN tblJob ON (tblCandidate.CandidateID = tblJob.JobCandidate) AND (tblJob.JobID = tblSchedule.ScheduleJobID)) ON tblPlacement.PlacementID = tblJob.JobPlacement "
   strSql = strSql & "GROUP BY [CandidateFirstName] & ' ' & [CandidateSurName], tblPlacement.PlacementName, tblJob.JobRef "
   strSql = strSql & "ORDER BY [CandidateFirstName] & ' ' & [CandidateSurName], tblSchedule.ScheduleDate "
   strSql = strSql & "PIVOT tblSchedule.ScheduleDate "
  'Debug.Print strSql
  If startDate = 0 Then startDate = Date
  'take a day in the week and get the Saturday
  startDate = GetStartOfWeek(startDate)
  strPivot = GetPivotIn(startDate, 7)
  strSql = strSql & strPivot
  GetPivotSQL = strSql
End Function

4. Wrap that up

Public Sub UpdateQuery(DayInWeek As Date)
  Dim qdf As QueryDef
  Dim strSql As String
  Set qdf = CurrentDb.QueryDefs("qryXtabSchedule")
  strSql = GetPivotSQL(DayInWeek)
  qdf.SQL = strSql


End Sub

In the form you then have to bind the generic columns to match the pivot

Private Sub cmboDate_AfterUpdate()
  If IsDate(Me.cmboDate) Then
    UpdateQuery (Me.cmboDate)
    SetControlSource (Me.cmboDate)
End If
Me.RecordSource = "qryXTabSchedule"

End Sub

Public Sub SetControlSource(startDate As Date)
  Dim aDates() As String
  Dim strPivot As String
  Dim I As Integer
  startDate = GetStartOfWeek(startDate)
   For I = 1 To 7
    Me.Controls("txtDay" & I).ControlSource = Format(startDate, "mm/dd/yyyy")
    Me.Controls("lblDay" & I).Caption = Format(startDate, "MM/dd/yyyy")
    startDate = startDate + 1
  Next I
End Sub

Now you can double click to edit a cell. This could be improved to go directly to that date for that Job. Now it goes to that day in the subform and defaults the value for a new date. Also the selection of a date using a combo is for demo only. You likely want a calendar control or a better means to pick a week Also you want to make the controls unbound and rebind all control sources so you do not see "#name# when you start.

You might want to consider a pop up time pikcer. May be faster than typing.

Solved - Date to DateTime

So here we go again ... I don't think so. Your question was how to convert a date column to hold datetime which somehow took 18 responses before you got a non-ambiguous answer that you DO NOT need to convert anything. The problem being you asked the wrong question. Now that we know what the...

www.access-programmers.co.uk

 

[klsblues]

Hi everyone,

first of all thanks for your help and in particular MajP for that work.

However, I think I have solved the problem, but I am that bemused at the simplicity of it, I am doubting myself. Can I ask that someone has a look a the code behind my form (frmXTabSchedule) and just reassure me that it is working as it should please!

Many thanks - Kev.

The code is:

------------------------------------------------------------------------------------------------------------------

Private Sub Form_Load()

Dim rst As New ADODB.Recordset
rst.Open "select * from " + "qryXTabSchedule" + " where 1=0", _
CurrentProject.Connection

'Add the Caption to each column heading

Me.Candidate_Label.Caption = rst.Fields(0).Name
Me.PlacementName_Label.Caption = rst.Fields(1).Name
Me.JobRef_Label.Caption = rst.Fields(2).Name
Me.JobID_Label.Caption = rst.Fields(3).Name
Me.FirstDate_Label.Caption = rst.Fields(4).Name
Me.SecDate_Label.Caption = rst.Fields(5).Name
Me.ThirdDate_Label.Caption = rst.Fields(6).Name
Me.FourthDate_Label.Caption = rst.Fields(7).Name
Me.FifthDate_Label.Caption = rst.Fields(8).Name
Me.SixthDate_Label.Caption = rst.Fields(9).Name
Me.SevDate_Label.Caption = rst.Fields(10).Name
Me.EightDate_Label.Caption = rst.Fields(11).Name
Me.NineDate_Label.Caption = rst.Fields(12).Name
Me.TenDate_Label.Caption = rst.Fields(13).Name

'Select the controlSource name for each field

Me.Candidate.ControlSource = rst.Fields(0).Name
Me.PlacementName.ControlSource = rst.Fields(1).Name
Me.JobRef.ControlSource = rst.Fields(2).Name
Me.JobID.ControlSource = rst.Fields(3).Name
Me.Date01.ControlSource = rst.Fields(4).Name
Me.Date02.ControlSource = rst.Fields(5).Name
Me.Date03.ControlSource = rst.Fields(6).Name
Me.Date04.ControlSource = rst.Fields(7).Name
Me.Date05.ControlSource = rst.Fields(8).Name
Me.Date06.ControlSource = rst.Fields(9).Name
Me.Date07.ControlSource = rst.Fields(10).Name
Me.Date08.ControlSource = rst.Fields(11).Name
Me.Date09.ControlSource = rst.Fields(12).Name
Me.Date10.ControlSource = rst.Fields(13).Name

End Sub

 

[isladogs]

Yes it seems to work correctly though you should be able to streamline that repeating code
Suggestion - add a double click event code to a date/job slot on the Schedule form which will go direct to the JodDetails form with that that already entered
Can the scheduler handle adding more than one session per candidate on a specific day?

 

[MajP]

I doubt this will work in for anything useable. Unless you are telling me you only want to display these 13 dates and no others ever.
There is no way to display unfilled dates. Don't you want to see unused das. If you do not have 13 dates it errors. Start this with no data to test. How do you plan to show other 13 dates outside this window of dates.

 

[MajP]

Working solution
Shows days without events
Can show any week
Time pickers for ease
Doubleclick to edit at the correct day

Untested, but to run this in DD/MM/YYYY region look for "MM/DD/YYYY" in code and replace

 

[klsblues]

Working solution
Shows days without events
Can show any week
Time pickers for ease
Doubleclick to edit at the correct day

MajP - This is Excellent, thank you very much indeed. I can see why you commented on my simple code as you did!

Three questions:

1. Can I just go through the vba to change the date format to English UK (dd/mm/yy)?
2. How do I get my week on the grid to start on Monday - not Saturday?
3. I have added a checkbox to the tbljob to identify completed jobs, as eventually one person could have hundreds of jobs and these will all show on any given week selected. How do I add a toggle button to show/hide any completed jobs?

Thanks again - Brilliant work.
Regards - Kev.

 

[MajP]

I can see why you commented on my simple code as you did!

It is not a problem with the code, but all the additional features you need to make this work. The issue with a crosstab, is that it only shows columns where you have a value. If you want a grid to show the complete week then you need to force a column with a Pivot In statement to add all potential columns. You need a way to either filter to a current week or pivot into those columns. If you do not pivot the UI will be strange. You only get columns after a value is added for that day

To start on Monday this function takes an argument

Public Function GetStartOfWeek(Optional dtmDate As Date = 0, Optional StartOn As VbDayOfWeek = vbSaturday)
  If dtmDate = 0 Then dtmDate = Date
  Do Until Weekday(dtmDate) = StartOn
    dtmDate = dtmDate - 1
  Loop
  GetStartOfWeek = dtmDate
End Function

You can pass in the startday as vbMonday without code modification. But I will modify the default to
Optional StartOn As VbDayOfWeek = vbMonday
then you do not have to change anything

1. Can I just go through the vba to change the date format to English UK (dd/mm/yy)?

Should be able to. Only question is if you also have to do that in the query. I did not have to specify the format in the query so it should work.
Give it a try then if not change the query to force the column format.

strSql = strSql & "ORDER BY [CandidateFirstName] & ' ' & [CandidateSurName], Format([tblSchedule.ScheduleDate],'DD/MM/yyyy') "
strSql = strSql & "PIVOT Format([tblSchedule.ScheduleDate],'DD/MM/yyyy')"

If you want a feature to toggle completed jobs then you would simply modify the query.

I think it would be something like

strSql = "TRANSFORM First(Format([ScheduleStartTime],'hh:mm Am/pm') & ' - ' & format([ScheduleEndTime],'hh:mm am/pm')) AS Timings "
   strSql = strSql & "SELECT [CandidateFirstName] & ' ' & [CandidateSurName] AS Candidate, tblPlacement.PlacementName, tblJob.JobRef "
   strSql = strSql & "FROM tblPlacement INNER JOIN ((tblCandidate INNER JOIN tblSchedule ON tblCandidate.CandidateID = tblSchedule.ScheduleCandidateID) "
   strSql = strSql & "INNER JOIN tblJob ON (tblCandidate.CandidateID = tblJob.JobCandidate) AND (tblJob.JobID = tblSchedule.ScheduleJobID)) ON tblPlacement.PlacementID = tblJob.JobPlacement "

If ShowCompleted = False then
  strSql = strSql & "WHERE completed = FALSE"
end if

   strSql = strSql & "GROUP BY [CandidateFirstName] & ' ' & [CandidateSurName], tblPlacement.PlacementName, tblJob.JobRef "
   strSql = strSql & "ORDER BY [CandidateFirstName] & ' ' & [CandidateSurName], Format([tblSchedule.ScheduleDate],'MM/dd/yyyy') "
   strSql = strSql & "PIVOT Format([tblSchedule.ScheduleDate],'MM/dd/yyyy')"

You will have to pass the argument into the procedure.

 

[MajP]

I noticed you have a candidtate ID in the job table and a candidate ID in the schedule table. However schedule is realted to a job. It either goes in the job table meaning a job has one candidate or it goes in the schedule table where a job can have many people scheduled. Now you may want to consider an outer join so that your grid starts with all days of the week and all jobs.

 

[klsblues]

I noticed you have a candidtate ID in the job table and a candidate ID in the schedule table. However schedule is realted to a job. It either goes in the job table meaning a job has one candidate or it goes in the schedule table where a job can have many people scheduled. Now you may want to consider an outer join so that your grid starts with all days of the week and all jobs.

Hi MajP,

Thanks for this, your advice is sound - I have taken the relationship out of the schedule table, as it is one person to one job.

I have changed the dates to UK (dd/mm/yy) and the grid now starts on Monday, so thanks forthat (see attachment).

Also, I have spent the last 3 hours trying to get the JobComplete (Yes/No field) to work in order to show/hide completed jobs on the grid and whatever I try, doesn't work. I've tried adding the tbljob.JobComplete in the SQL part of the code by following what you've done and added the

If JobComplete = False then
strSql = strSql & "WHERE completed = FALSE"
end if

but it just does not work. Can you help?

Also, I have shown the end users what you've done and they are delighted, but I have been asked is there a way to add a left and right arrow button either side of the select date combo and each time it is clicked, the grid moves on a week (or back a week if the left arrow is clicked).

If you can help, I would be really thankful.

Regards - Kev.

 

[MajP]

Also, I have shown the end users what you've done and they are delighted, but I have been asked is there a way to add a left and right arrow button either side of the select date combo and each time it is clicked, the grid moves on a week (or back a week if the left arrow is clicked).

I actually already did that and posted in V5.

If JobComplete = False then
strSql = strSql & "WHERE completed = FALSE"
end if

but it just does not work. Can you help?

You will either have to post the entire code, or send the updated database back. Do a debug.print to show the resolved SQL. I have a couple in the code already, they just need to be uncommented. What does not work mean? Filter not applied or code errors. It may be as simple as putting a Space behind false.
StrSql = strSql & "WHERE completed = FALSE "
If not False will run into the next line.