Change from days to hours

[jj72uk]

Indeed....maybe I'll start looking into the tables....making tables is the easy part.

 

[jzwp22]

Good luck. Let me know if you have any questions on the table structure I provided earlier.

 

[jj72uk]

jzwp22,

Have come across a bit of a problem!

I'm entering some historical sickness/compassionate leave and it seems to be taking off the managers entitlement so now some managers are in the negative!

I've checked the checked boxes so it shouldn't count on there entitlment, i'm guessing something is amiss in the query?

Also. Is there a way to stop managers 'over booking' so when there entitlement is out they cannot book anymore?

I've attached the most up todate db.

 

[jzwp22]

I had a typo in the qryGatherLeave; I've corrected it.

Also. Is there a way to stop managers 'over booking' so when there entitlement is out they cannot book anymore?

I added code to handle this in the attached DB

 

[jj72uk]

Ah i thought it may have been a typo

I;ve got a problem, but I think its my fault.

I thought I'd test the DB to see if it worked for 2010.

So i changed my system date to 1 jan 2010, went into the DB to book a holiday. I had to intially set the entitlement, which is to be expected, but then when I go to book holiday it kicks up a fuss!!

So then I tried to set the entitlement by actually going into the entitlement table, adding 2010 into it, but still no luck.

Am i being stupid?

 

[jzwp22]

Some of the functionality of the database uses the date() function which pulls its info from the system date. At the beginning of each year, you will have to add a new entitlement record for each employee with the corresponding year field set to the new year.

I would set the system date back to the correct date and add some entitlement records for 2010. Then go in add try to book dates in 2010. In the calendar, see if the dates show up correctly. Some of the other things you will not be able to check because the forms are based on the current year. If you want to see the form based on other years, you would have to add some way of selecting the year or do not limit the form to the current year.

 

[jj72uk]

Sorry to bring this up from the grave but I am getting a couple of issues.

I have manually amended the entitlement table to include entitlement for 2010 for the managers.

I then go and try and book holidays for next year for the system to tell me they have no holiday hours remaining.

I'm guessing this happpend as it was looking at there 2009 entitlement for 2010.

So i changed my system date to jan 2010 and again try and book but I get a error in the VB code.

I tried to remove the dteCurrent text box to undefined so not =date() and manually set the date in there but still got an error.

Is i purley becasue its not 2010 yet. Or are there some other underlying issues?

 

[jzwp22]

The 3 subforms on the form frmEmpBaseData are all based on the date control(dteCurrrent) in the main form header, so the reason you are not seeing the 2010 entitlement is because of that date field. To get around this, you will have to unbind the dteCurrent control by removing the =date() as the control source for the control. This will allow you to enter any date, but you need to requery the form when you input the date. So in the after update event of the dteCurrent control you will need to add the following code:

me.requery

 

[jj72uk]

Doesn't the =date() control source use the system date?

I tried to trick the system by changing this, would this emulate it being 2010?

This didn't work.

I just did what you suggested. Though it will now display the next years entitlement it pulls up a VBA error.

I Made the dteCurrent unbound ( deleted =date() )
Set the after update event, using code builder to me.requery

But the same error.

When debugging the line highlghted is:

holdCurrent = DSum("spHours", "qryGatherLeave", "fkEmpID=" & Me.fkEmpID & " and year(datebooked)=" & Year(Me.DateBooked))

 

[jzwp22]

At the beginning of the year there are no bookings so the DSum() code fails. We'll have to check to make sure there are records first, if not skip over the DSum(). To do this we'll need an IF..THEN statement that utilizes the DCount() function.

Dim holdCurrent As Single
    If DCount("*", "qryGatherLeave", "fkEmpID=" & Me.fkEmpID & " and year(datebooked)=" & Year(Me.DateBooked)) <> 0 Then
        holdCurrent = DSum("spHours", "qryGatherLeave", "fkEmpID=" & Me.fkEmpID & " and year(datebooked)=" & Year(Me.DateBooked))
     End If

 

[jj72uk]

Ah!

That makes a lot of sense and works perfectly!

On a side note. Is there an easy way to resize access forms easily.

In this project particularly the calender form and the main booking form.

Dependant on the systems display settings?

 

[jzwp22]

Take a look at the form properties under the Format tab. It looks like the calendar and the booking form both have autosize set to yes. Switch it to no. Also, change the border style to sizeable; the calendar is currently set at dialog. Make sure that both horizontal and vertical scroll bars are active.

 

[jj72uk]

I have done this but still the forms do not down size if the res. is decreased.

The forms are still big looking and one has to use the navigation bars to scroll around the form.

 

[jzwp22]

What do you want to do? Do you want to maximize the forms when they open?

DoCmd.Maximize

If not, then you probably have to redesign the forms. For your frmEmpBaseData, you can add a tabbed subform option and put the 3 subforms there. That way you can reduce the overall size of the form, but you will have to click on a tab to get the info you want. I would put the most used subform on the first tab.

As to the calendar, I'm not sure how you would rework that one.

 

[jj72uk]

What I mean is that the forms were designed on one screen resolution and when opened on a smaller res the forms do not fit the screen, so i am needing something to resize depending on screen rez.

I've tried the shirnkerStrecher from peter's software, but couldn't get it to work.

So I've tried to include this code, but also unable to get it to work:

Private List() As Control
Private curr_obj As Object
Private iHeight As Integer
Private iWidth As Integer
Private x_size As Double
Private y_size As Double

Private Type Control
    Index As Integer
    Name As String
    Left As Integer
    Top As Integer
    width As Integer
    height As Integer
End Type

Public Sub ResizeControls(frm As Form)
Dim i As Integer
'   Get ratio of initial form size to current form size
x_size = frm.height / iHeight
y_size = frm.width / iWidth

'Loop though all the objects on the form
'Based on the upper bound of the # of controls
For i = 0 To UBound(List)
    'Grad each control individually
    For Each curr_obj In frm
        'Check to make sure its the right control
        If curr_obj.TabIndex = List(i).Index Then
            'Then resize the control
             With curr_obj
                .Left = List(i).Left * y_size
                .width = List(i).width * y_size
                .height = List(i).height * x_size
                .Top = List(i).Top * x_size
             End With
        End If
    'Get the next control
    Next curr_obj
Next i
End Sub

Public Function SetFontSize() As Integer
    'Make sure x_size is greater than 0
    If Int(x_size) > 0 Then
    'Set the font size
        SetFontSize = Int(x_size * 8)
    End If
End Function

Public Sub GetLocation(frm As Form)
Dim i As Integer
'   Load the current positions of each object into a user defined type array.
'   This information will be used to rescale them in the Resize function.

'Loop through each control
For Each curr_obj In frm
'Resize the Array by 1, and preserve
'the original objects in the array
    ReDim Preserve List(i)
    With List(i)
        .Name = curr_obj
        .Index = curr_obj.TabIndex
        .Left = curr_obj.Left
        .Top = curr_obj.Top
        .width = curr_obj.width
        .height = curr_obj.height
    End With
    i = i + 1
Next curr_obj
    
'   This is what the object sizes will be compared to on rescaling.
    iHeight = frm.height
    iWidth = frm.width
End Sub

Public Sub CenterForm(frm As Form)
    frm.Move (Screen.width - frm.width) \ 2, (Screen.height - frm.height) \ 2
End Sub

Public Sub ResizeForm(frm As Form)
    'Set the forms height
    frm.height = Screen.height / 2
    'Set the forms width
    frm.width = Screen.width / 2
    'Resize all of the controls
    'based on the forms new size
    ResizeControls frm
End Sub

 

[jzwp22]

Adjusting the form size based on screen resolution is something outside of what I am familiar with. Perhaps a new post with that specific topic might yield some responses from those on the forum who have more knowledge than I.