Solved Schedule grid (1 Viewer)

[klsblues]

Hi MajP

I don't understand the SQL part of the DB and tried to add the JobComplete field into the SQL statement and it did not work. I have attached V5

Thanks again - Kev.

 

[MajP]

You can select All, Complete, Not Complete.
You can navigate to next week
I did not change the formatting so I am not sure if this works at all for you. But I think you will have to change that in the set control source and the pivot in code.

 

[MajP]

The big problem is still that the grid only shows and allows you to add edit a job once that job has 1 scheduled event. The better solution is to do an outer join and show all jobs. I have not played with that. In the short term you might want a button on this form to "add" a job without an event. That would take you to the jobDetailsForm.

 

[klsblues]

The big problem is still that the grid only shows and allows you to add edit a job once that job has 1 scheduled event. The better solution is to do an outer join and show all jobs. I have not played with that. In the short term you might want a button on this form to "add" a job without an event. That would take you to the jobDetailsForm.

Hi MajP,

This is fantastic, thank you so much. When I commented earlier about working on it for 3 hours, I had failed to add the 'completed field' into the group part of the SQL code and it kept erroring.

I have just added a status column and it has worked a treat! I've also added another button at the top of the form to click back to this current week - so if you've scrolled over many weeks ahead, you can come back to this week with one click.

I take your point about the non-scheduled jobs - I hadn't thought about that. Does that mean that I have to make another query first to get all the jobs (scheduled or not) and then add that query into my Xtab query and then add that into the SQL part of the code?

Again your help is very much welcomed and appreciated.

Kind regards - Kev.

 

[MajP]

and then add that query into my Xtab query

FYI the xtab by itself is actually meaningless. The code does not use the stored query but builds a new query each time via code. To demonstrate you can change that query into anything like a simple select. Then run the Grid. So there is not reason to add or edit it. It will not be used.
Think of a stored query as a container that holds a SQL string. Basically I am just using the container to hold the new string created each change or date or filter.

I take your point about the non-scheduled jobs - I hadn't thought about that. Does that mean that I have to make another query first to get all the jobs (scheduled or not) and then add that query into my Xtab query and then add that into the SQL part of the code?

It would mean changing the current query that is written in code, since as stated above changing the stored query does nothing. If you make an outer join between Table A and Table B then it shows all records in A and matching records in B. An inner join only shows records in A with a match in B. You currently have an inner join. If instead you had an outer join from Jobs to schedule you will get all the jobs and matching schedules. Turning this into a crosstab with a forced set of columns would give you all the jobs as rows in the grid regardless if they have any associated schedules.

I did not try it yet. I assume later down the road you will have lots of closed jobs. I think the default should be Open jobs not all Jobs.

 

[MajP]

Actually that is not going to work. That row will not show in a crosstab because there is no value in a column. Different than a select.
In the short term have a button on the header to "Add Additional Jobs". This will pop up the Schedule Detail.
You could get a little slicker and have a combobox with all jobs not in that period. Then the user can select a job and then click one of the Day labels. If would open the detail schedule to that job and default the day information like it currently does.
On that not you might want to add a Note in the header or footer (Double click grid cells to add / edit time information)

 

[klsblues]

Actually that is not going to work. That row will not show in a crosstab because there is no value in a column. Different than a select.
In the short term have a button on the header to "Add Additional Jobs". This will pop up the Schedule Detail.
You could get a little slicker and have a combobox with all jobs not in that period. Then the user can select a job and then click one of the Day labels. If would open the detail schedule to that job and default the day information like it currently does.
On that not you might want to add a Note in the header or footer (Double click grid cells to add / edit time information)

Hi MajP. I'll take a look next week and see what I can do.

Thanks again and have a wonderful weekend.

Regards - Kev.

 

[klsblues]

Here is a demo to add jobs not currently listed.

Hi MajP,

Thank you very much indeed - works a treat! I have added a date to be shown in the 'pick week' combo, so the week that the selection is on, shows in the cmboDate - it was something an end user picked up on, so I've added it.

I have been trying to work out how to use VBA to set the colour of the field background. I created a table to store the users choices of colours, tried using a tempVars to set the colours using the colourNumber field from the colours table and then tried using IF, Then to format the JobStatus Field depending on the status number (i.e. If me.jobStatus = 1 then me.jobstatus = "Tempvars", but to no avail. is this something that can be done?

Thanks again and regards - Kev.

 

[MajP]

I have been trying to work out how to use VBA to set the colour of the field background. I created a table to store the users choices of colours, tried using a tempVars to set the colours using the colourNumber field from the colours table and then tried using IF, Then to format the JobStatus Field depending on the status number (i.e. If me.jobStatus = 1 then me.jobstatus = "Tempvars", but to no avail. is this something that can be done?

I am confused since you already are doing this with conditional formatting. That is the proper way to do it, so not sure what different result you are looking for.

 

[klsblues]

I am confused since you already are doing this with conditional formatting. That is the proper way to do it, so not sure what different result you are looking for.

The end user has asked me the question, “what if I want to pick my own colours or add another status and apply a colour to that status as you won’t be around forever to add the conditional formatting!”

 

[klsblues]

The end user has asked me the question, “what if I want to pick my own colours or add another status and apply a colour to that status as you won’t be around forever to add the conditional formatting!”

I have added the TempVars and the rest of the code to OnLoad of the frmGrid, but it errors.

 

[Gasman]

I am confused since you already are doing this with conditional formatting. That is the proper way to do it, so not sure what different result you are looking for.

PMFJI but "Tempvars" means you are setting it to that word, not the tempvar which anyway would be TempVar!YourName.Value or TempVar("YourName").Value
Plus you need to mention the BackColor property?

So

If me.jobStatus = 1 then me.jobstatus = "Tempvars"

means if the status is 1 make it "TempVar". If the status is numeric, which it appears to be that is never going to work.

 

[klsblues]

PMFJI but "Tempvars" means you are setting it to that word, not the tempvar which anyway would be TempVar!YourName.Value or TempVar("YourName").Value
Plus you need to mention the BackColor property?

So

If me.jobStatus = 1 then me.jobstatus = "Tempvars"

means if the status is 1 make it "TempVar". If the status is numeric, which it appears to be that is never going to work.

Hi Gasman,

I have actually used this;

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblDefault", dbOpenSnapshot, dbReadOnly)

TempVars("ColourRGB01") = rs!ColourRGB01.Value
TempVars("ColourRGB02") = rs!ColourRGB02.Value
TempVars("ColourRGB03") = rs!ColourRGB03.Value
TempVars("ColourRGB04") = rs!ColourRGB04.Value

Dim Colour01 As Long
Colour01 = RGB & ColourRGB01
Dim Colour02 As Long
Colour02 = RGB & ColourRGB02
Dim Colour03 As Long
Colour03 = RGB & ColourRGB03
Dim Colour04 As Long
Colour04 = RGB & ColourRGB04

If Me.JObStatus = 1 Then
Me.JObStatus.BackColor = Colour01
Else
If Me.JObStatus = 2 Then
Me.JObStatus.BackColor = Colour02
Else
If Me.JObStatus = 3 Then
Me.JObStatus.BackColor = Colour03
Else
If Me.JObStatus = 4 Then
Me.JObStatus.BackColor = Colour04

The default table stores (235,012,005) in ColourRGB01 field, which is the RGB colour01

 

[Gasman]

I would be debug.print(ing) the Colour0x to see if they have been formatted correctly.
I would also be using a Select Case statement instead of all those Ifs.

Learn to walk your code, set breakpoints and inspect/debug.print variables/controls values.
You could also test those statements in the immediate window.

 

[klsblues]

I would be debug.print(ing) the Colour0x to see if they have been formatted correctly.
I would also be using a Select Case statement instead of all those Ifs.

Learn to walk your code, set breakpoints and inspect/debug.print variables/controls values.
You could also test those statements in the immediate window.

Hi Gasman,

Thanks for your help.

I have added a debug.print and I am going crazy!

If I use the
Me.JObStatus.BackColor = RGB(235, 12, 5) format, I get no errors (although it doesn't format my fields correctly, as they are all the same colour),
but as soon as I replace the 'RGB(235, 12, 5)' for the Dim, it errors with a run-time error 13 - Type mismatch, yet the debug shows it is returning the correct information. Any ideas?

Here's the full code (Colour 2 has been changed to use the Dim):

Private Sub Form_Load()
If IsDate(SelectedDate) Then
SelectedDate = Now()
UpdateQuery SelectedDate, Nz(Me.frameComplete, 1)
SetControlSource (SelectedDate)
Me.RecordSource = "qryXTabSchedule"
End If

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblDefault", dbOpenSnapshot, dbReadOnly)

Dim ColourRGB01 As String
ColourRGB01 = "Me.JObStatus.Backcolor = RGB(" & DLookup("[ColourRGB01]", "tblDefault") & ")"
Dim ColourRGB02 As String
ColourRGB02 = "RGB(" & DLookup("[ColourRGB02]", "tblDefault") & ")"
Dim ColourRGB03 As String
ColourRGB03 = "Me.JObStatus.Backcolor = RGB(" & DLookup("[ColourRGB03]", "tblDefault") & ")"
Dim ColourRGB04 As String
ColourRGB04 = "Me.JObStatus.Backcolor = RGB(" & DLookup("[ColourRGB04]", "tblDefault") & ")"


Debug.Print ColourRGB01
Debug.Print ColourRGB02
Debug.Print ColourRGB03
Debug.Print ColourRGB04

If Me.JObStatus = 1 Then
Me.JObStatus.BackColor = RGB(235, 12, 5)
Else
If Me.JObStatus = 2 Then
Me.JObStatus.BackColor = ColourRGB02
Else
If Me.JObStatus = 3 Then
Me.JObStatus.BackColor = RGB(8, 194, 23)
Else
If Me.JObStatus = 4 Then
Me.JObStatus.BackColor = RGB(45, 64, 227)
End If
End If
End If
End If


' DoCmd.Maximize
End Sub


Heres the Debug showing the outcomes:

TRANSFORM First(Format([ScheduleStartTime],'hh:mm am/pm') & ' - ' & format([ScheduleEndTime],'hh:mm am/pm')) AS Timings SELECT [CandidateFirstName] & ' ' & [CandidateSurName] AS Candidate, tblPlacement.PlacementName, tblJob.JobRef, jobComplete, JobStatus FROM tblPlacement INNER JOIN ((tblCandidate INNER JOIN tblSchedule ON tblCandidate.CandidateID = tblSchedule.ScheduleCandidateID) INNER JOIN tblJob ON (tblCandidate.CandidateID = tblJob.JobCandidate) AND (tblJob.JobID = tblSchedule.ScheduleJobID)) ON tblPlacement.PlacementID = tblJob.JobPlacement WHERE JobComplete = False GROUP BY [CandidateFirstName] & ' ' & [CandidateSurName], tblPlacement.PlacementName, tblJob.JobRef, tblJob.JobComplete, tblJob.JobStatus ORDER BY [CandidateFirstName] & ' ' & [CandidateSurName], tblSchedule.ScheduleDate PIVOT tblSchedule.ScheduleDate IN (10/02/2023, 10/03/2023, 10/04/2023, 10/05/2023, 10/06/2023, 10/07/2023, 10/08/2023)
10/02/2023
10/03/2023
10/04/2023
10/05/2023
10/06/2023
10/07/2023
10/08/2023
Me.JObStatus.Backcolor = RGB(235, 12, 5)
RGB(245,141, 5)
Me.JObStatus.Backcolor = RGB( 8,194, 23)
Me.JObStatus.Backcolor = RGB( 45, 64,227)

TRANSFORM First(Format([ScheduleStartTime],'hh:mm am/pm') & ' - ' & format([ScheduleEndTime],'hh:mm am/pm')) AS Timings SELECT [CandidateFirstName] & ' ' & [CandidateSurName] AS Candidate, tblPlacement.PlacementName, tblJob.JobRef, jobComplete, JobStatus FROM tblPlacement INNER JOIN ((tblCandidate INNER JOIN tblSchedule ON tblCandidate.CandidateID = tblSchedule.ScheduleCandidateID) INNER JOIN tblJob ON (tblCandidate.CandidateID = tblJob.JobCandidate) AND (tblJob.JobID = tblSchedule.ScheduleJobID)) ON tblPlacement.PlacementID = tblJob.JobPlacement WHERE JobComplete = False GROUP BY [CandidateFirstName] & ' ' & [CandidateSurName], tblPlacement.PlacementName, tblJob.JobRef, tblJob.JobComplete, tblJob.JobStatus ORDER BY [CandidateFirstName] & ' ' & [CandidateSurName], tblSchedule.ScheduleDate PIVOT tblSchedule.ScheduleDate IN (10/02/2023, 10/03/2023, 10/04/2023, 10/05/2023, 10/06/2023, 10/07/2023, 10/08/2023)
10/02/2023
10/03/2023
10/04/2023
10/05/2023
10/06/2023
10/07/2023
10/08/2023
Me.JObStatus.Backcolor = RGB(235, 12, 5)
RGB(245,141, 5)
Me.JObStatus.Backcolor = RGB( 8,194, 23)
Me.JObStatus.Backcolor = RGB( 45, 64,227)

 

[Gasman]

Not played around much with colours, but this is how I would approach it and I have just tested that this works.
The experts here might be able to give a better method.?

Sub SetBC()
Dim iRed As Integer, iGreen As Integer, iBlue As Integer
iRed = 235
iGreen = 12
iBlue = 5

Me.cboDates.BackColor = RGB(iRed, iGreen, iBlue)


End Sub

I was unable to get the colour using a string, but the experts might know?

 

[MajP]

You need this table, not what you have
tblStatus tblStatus

StatusID	StatusDescription	BackColor	BackcolorDescription	ForeColor	ForecolorDescription
1​	Not Allocated	255​	Red	0​	Black
2​	Allocated	33023​	Orange	0​	Black
3​	Complete	32768​	Green	0​	Black
4​	Cancelled	16711680​	Blue	16777215​	White

Then this code to add and remove format conditions

Public Sub ClearFormatConditions()
  Dim con As FormatCondition
  For Each con In Me.JObStatus.FormatConditions
    con.Delete
  Next con
End Sub
Public Sub ApplyFormatConditions()
Dim con As FormatCondition
Dim rs As DAO.Recordset
Dim Status As Long
Dim BackColor As Long
Dim ForeColor As Long
Set rs = CurrentDb.OpenRecordset("tblStatus")
Do While Not rs.EOF

    Status = rs!StatusID
    BackColor = GetBackcolor(Status)
    ForeColor = GetForeColor(Status)
    Debug.Print BackColor & " " & ForeColor
    Set con = Me.JObStatus.FormatConditions.Add(acExpression, , "[jobStatus] like " & Status)
    With con
      .ForeColor = ForeColor
      .BackColor = BackColor
    End With
rs.MoveNext
Loop
Me.Refresh
End Sub

Need a form to add and show colors for each status. Now status and colors are completely editable.

Then you can get format like so

Solved - Programatically change color on continous form conditional formatting

Hi, I'm trying to change the color of a textbox on a continous form. I need to color to change based on the RGB values in the text box. Is it possbile to do?

www.access-programmers.co.uk

 

[MajP]

My guess is that on the grid you want to Show the status description and not the status number. So you need to likely modify the query to do that or use a combo. The correct way to do this would be to modify the crosstab query that the code generates. However, I did not want to mess with that so instead built a calculated control.

 

[klsblues]

Hi there,
@Gasman - thanks for your input.
@MajP - absolutely brilliant work! I have made the changes you suggested and I have formatted the frmStatus and it works fantastically - so thank you very much indeed. I really am thankful for your time with this.

I have been asked if the time picker could be a simple combo box which drops down to show the15 minute incremented times and when OK is clicked, it does what the current time picker does (this would be in place of the current time grid). I have looked through your code, but it is beyond my capabilities to use the combo instead of your grid. I have attached the DB with the combo box added to the picker form, can you advise on how to do this please?

Once again, many thanks for all your help.
Regards - Kev.

 

[MajP]

I have been asked if the time picker could be a simple combo box which drops down to show the15 minute incremented times and when OK is clicked,

Do you want 1 combo with all 15 minute choices (i.e. 12:00 am 12:15 am, .... 11:30 Pm, 11:45 pm)? I would think, that would be a lot of choices to scroll through. Does not make it easier. However that could be done without a pop up form. Or do you want two combos, hours (12 Am, 1 AM, 2 AM .... 11 pm) and minutes (00,15,30, 45)