I did not look at the calendar, so I'm not sure what is involved to get that to work with the new structure.
I know the report you made shows all the details in regards to that managers entitlement, but can i also add something on the main form to show what that managers entitlement is left without having to run the query to get the report up?
You should be able to recreate the old reports, it would be just a matter of creating a query that pulls the applicable information and changing the report's record source to the new query.
I'm trying to amend the entitlement sub form to add another coloum for entitlement remaining.
I've linked the boxes control source to the qryHolidaySummery using the expression building, but it doesn't want to work?
EDIT:
Thought I could get away with not including the calander, but found out it is needed. And again I'm getting problems...hands up, I'm a noob....
This is what I;ve done with the calander:
To save time I imported the form. Within the VBA I found what I thought was the code that linked the qry. In this case it was 2 peices, 1 for holidays and 1 for appointments.
Code 1
Code:
Set db = CurrentDb
strSQL = "SELECT Q1.LastName,Q1.DateBooked,q1.Department,Q1.Appointment "
strSQL = strSQL & "FROM Qry_CalData AS Q1 "
strSQL = strSQL & "WHERE (Q1.DateBooked) Between " & lngFirstOfMonth & " And " & lngLastOfMonth & " ORDER BY "
strSQL = strSQL & "Q1.DateBooked;"
This would say to me that I needed a Qry called Qry_CalData
So I created the above named qry and entered this code into it, as it's what I thoght was right
Code:
SELECT T1.pkEmpID, T1.LastName, T1.Department, T2.DateBooked AS Appointment
FROM BaseData AS T1 INNER JOIN HolidayBookings AS T2 ON T1.pkEmpID=T2.fkEmpID;
This code was taken from the 'orginal' but with the name changes to link the tables.
When I now open the calander form it asks me for the paremeter for
Qry_CalData.DateBooked
So somethng can;t be right there. I addtionally get this message
The expression On Open you entered as the event property setting prodcued the following error: User-Defined type not defined.
In the query text you provided you gave datebooked an alias (Appointment), so now SQL will identify the field with the alias rather than the original field name
Code:
SELECT T1.pkEmpID, T1.LastName, T1.Department, [COLOR="Red"]T2.DateBooked AS Appointment[/COLOR]
FROM BaseData AS T1 INNER JOIN HolidayBookings AS T2 ON T1.pkEmpID=T2.fkEmpID;
If you attach the DB, I can take a look at it when I have time.
OK, I got the entitlement remaining issue to work using some code and the DCount() and DLookup() functions in the on current event of the subform.
As to the calendar, I had to change quite a few things, but it appears to be work. I was curious as the the point of the list box on the right hand side of the form. It did not show anything in the original database you posted, so I was wondering what is its purpose?
I guess all I need to do is add the code when the calander button is clicked. Easy enough!
As for the list box, in the orginal DB and the one which you have just uploaded, if you click on a day of the month where there is a holiday booked it will display the detials, it was put in there so if there were multiple appointments that day, plus holidays which all wouldn't display in the calander box it would give a complete breakdown.
Thank you for all your help. Is there anything I can do to repay you?
As for the list box, in the orginal DB and the one which you have just uploaded, if you click on a day of the month where there is a holiday booked it will display the detials, it was put in there so if there were multiple appointments that day, plus holidays which all wouldn't display in the calander box it would give a complete breakdown.
Currently the code which you added to check if 2 managers have a day off below is as below:
Code:
rivate Sub Form_BeforeUpdate(Cancel As Integer)
Dim holdcount As Long
holdcount = DCount("datebooked", "HolidayBookings", "DateBooked=#" & Me.DateBooked & "#")
If holdcount >= 2 Then
bookresp = MsgBox("At least two people have already booked time off for this date, do you still want to book this date?", vbYesNo)
If bookresp = 6 Then 'if yes
Exit Sub
Else
Me.Undo
End If
End If
End Sub
It works perfectly... but...to put a spanner in the works, is it possible to amend this coding so the rule of 2 persons off a once applies on all days apart from weekends where its 1 person off only?
Not a problem. Here is the code to do it. I set up a couple variables, one to hold the allowance (1 person for weekends and 2 for weekdays) and another just to hold text for the message to be grammatically correct. I then used the weekday() function to determine whether the datebooked was a Sunday (weekday() function returns 1) or Saturday (weekday() function returns 7) or neither. I'm not sure, but your regional settings might impact the weekday() function, so you will want to check that the code works properly by adding book dates for weekends. If you notice a problem, you will have to adjust the IF...THEN shown in red below based on the number returned by the weekday() function. In other words, Sunday might be considered the 7th day and not the 1st day of the week, and if so the Saturday would be 6. Alternatively, you can force Access to set a certain day of the week as 1, just check out the help section on the weekday() function. The modified DB is attached
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim holdcount As Long
Dim allowedoff As Long 'hold allowance before trigger
Dim allowedofftext As String
holdcount = DCount("datebooked", "HolidayBookings", "DateBooked=#" & Me.DateBooked & "#")
[COLOR="Red"]If Weekday(Me.DateBooked) = 1 Or Weekday(Me.DateBooked) = 7 [/COLOR]Then
allowedoff = 1
allowedofftext = "one person has"
Else
allowedoff = 2
allowedofftext = "two people have"
End If
If holdcount >= allowedoff Then
bookresp = MsgBox("At least " & allowedofftext & " already booked time off for this date, do you still want to book this date?", vbYesNo)
If bookresp = 6 Then 'if yes
Exit Sub
Else
Me.Undo
End If
End If
End Sub
Sorry to bother you yet again but I have ran into a problem and am stuck!
I started to update the Db with past holidays etc getting it up to date year to date.
But there seems to be a couple of problems. When I enter a holiday I am using the USA syntax of mm/dd/yyyy as previous discussed. Now im not used to using the USA style, so maybe its my fault, but if i enter in 08/01/2009 I am expecting it to show 1st Aug 2009 on the calander...but it shows 1st Jan 2009.
Additionally if I enter 08/01/2009 on 3 people I get no pop up message in regards to voloume, BUT if i have 2 people on 08/01/2009 and then add 1 more to 01/08/2009 ( my way of entering 1st aug ) then I do get the pop up!!
I think the problem still lies in way the dates are handled. Going back to Neil's post:
This isn't actually true. Access stores dates as a decimal number with the integer part being the number of days since 31 Dec 1899 and the fractional part, the time as a fraction of a day. It then formats this as a date 'on the fly'.
Access tries to handle dates according to the regional setting on your PC. However, SQL expects dates in US format, and this can sometimes cause an issue. I tend to use and unambiguous format such as dd-mmm-yyy to get around this.
I also noticed that in the code for the calendar, the developer handled dates as long number fields which would be the integer portion that Neil discussed (time portion ignored). In the code I wrote for checking the booking date against other booking dates already entered, I used a domain aggregate function. I believe these functions are based on SQL which uses the US format. So I modified my code to change the date being fed into the domain aggregate function to a long number datatype. I'm hoping that this will resolve your date issue, but I cannot check it here since my regional settings are in the US format. So what I would recommend, is use the format you commonly use. I would add some dates such as the 01/08/2009 and 08/01/2009 to test the attached database to see if the code change I made is giving what you would expect based on your normal settings.
That seems to have done the trick. I've tried various dates and it seems to work.
I guess the fail safe way is to input dates as dd/mmm/yyyy.
On a side note, this might seem a bit over ambitious...
Like on the main holiday booking form is there a way to add an entitlement subform to the holiday calander form, reducing the list box to lets say half size, and putting a like for like sub form in the calander form?
Or is there a way to link the sub form on the main page to the calander page, so whatever happens to the entitlemt subform on the main page also shows up on the one on the calander, I'm guessing a drop down box will be needed on the calander to select the rep??
Do you want to show the entitlement data for all employees in a subform on the calendar form based on the month currently being shown and ignoring data that may be entered in future months?
