Automatic Annual Leave

[barnstar]

Hi I am quite new to Access but I am desperate to learn as much as I can as
fast as I can.

I am currently working on automating an annual leave process at work & am
struggling & need help.

My problem is that I want the user to complete a form selecting the date(s) &
time(s) of leave they require. The leave pot is in half hour slots for e.g.
8:00 - 8:30 & so on. There would be a % of leave given on each half hour
which is calculated against a staff in post figure.

Once the user submits the form the db would automatically check the slots for
each half hourly slot for the time they requested off & return an immediate
response.

I hope somebody can help me with this problem & it would be much appreciated.

Thanks in advance

 

[Jeff Boyce]

Here's 4 learning curves you'll want to take into account to end up with a
useful (and used) Access application ... sorry, but it's not just one thing!

1) relational database design -- it all starts with the data, and Access
is optimized for data in this structure
2) Access tricks -- some of us have been at it for 15+ years ... and
still learning!
3) Graphical user interface design -- if it isn't easy to understand and
use, they won't!
4) application development -- if you've never built a house, where do you
start?

If you already have background on some of these, congratulations!

If you don't, any one of them could put the kibosh on your "finished"
product...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Steve]

Hello,

A database application starts with designing the structure of the data
tables. It doesn't appear you started here. Perhaps what would help you is
some assistance with the design of your tables. I have provided that help
many times to customers and am willing to help you. I charge a small fee to
work with you to design the tables you need. If you want my help, contact
me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

Hello,

A database application starts with designing the structure of the data
tables. It doesn't appear you started here. Perhaps what would help you is
some assistance with the design of your tables. I have provided that help
many times to customers and am willing to help you. I charge a small fee
to work with you to design the tables you need. If you want my help,
contact me.

Steve

The reason stevie has helped many customers is that they never seem to want
to give hime return business. Possibly due to hiis lack of ethics.


Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[Barry A&P]

Barnstar
I am also new to access an have greatly enjoyed trying to figure out how to
do the things i want to do..
Since i havent seen any specific suggestions on your setup let me throw out
my two cents.. on at least a newbies suggestion how to start.

i would think you need an employees table
T_Employees
EmployeeID (PK)
FName
LName
and whatever else you want in the table

A Leave table
T_Leave
LeaveID (PK)
EmployeeID (FK)
LeaveStartTime
LeaveStopTime

then you could have a form F_LeaveRequest with the employees info and two
unbound boxes for LeavestartTime and LeaveStopTime
and a Submit Leave Button

in the buttons on-click event start with something like this

Private Sub CMD_Submit_Click()
Dim MatchCount As Integer

If IsNull([Forms]![F_LeaveRequest]![LeaveStartTime]) Or
IsNull([Forms]![F_LeaveRequest]![LeavestopTime]) Then
MsgBox "Start or End Time cant be Empty", vbOKOnly
Else

If DCount("leaveID", "T_Leave", "T_Leave.LeaveStartTime Between
[Forms]![F_LeaveRequest]![LeaveStartTime] And
[Forms]![F_LeaveRequest]![LeaveStopTime] OR T_Leave.LeaveStopTime Between
[Forms]![F_LeaveRequest]![LeaveStartTime] And
[Forms]![F_LeaveRequest]![LeaveStopTime]") > 0 Then
MsgBox "somebody allready has requested leave during that period", vbOKOnly
Else
If MsgBox("The leave you requested is available", vbOKCancel) =
vbCancel Then
Exit Sub
Else
'SetWarnings = False 'Un-Comment this to stop the update warnings
Please Be carefull when turning warnings off
DoCmd.RunSQL "INSERT INTO T_Leave ( LeaveStartTime, LeaveStopTime,
EmployeeID ) " & _
"SELECT [Forms]![F_LeaveRequest]![LeaveStartTime] AS LeaveStartTime,
" & _
"[Forms]![F_LeaveRequest]![LeaveStopTime] AS LeavestopTime, " & _
"[Forms]![F_LeaveRequest]![EmployeeID] AS EmployeeID"
SetWarnings = True
End If
End If
End If

End Sub

Of course there is a Ton more stuff you need to accomplish but sometimes a
little something to get the ball rolling helps alot

Duane Hookum has a CalendarReports.mdb sample that might have a good example
of a timeline type report that may help spot available Leave periods

Remember this is newbie ramblings so i Hope i dont send you off on the wrong
track
Have fun
Barry

 

[barnstar via AccessMonster.com]

Barry

Thank you for your help, it is certainly a start but a very long way to go.

Barnstar

Barnstar
I am also new to access an have greatly enjoyed trying to figure out how to
do the things i want to do..
Since i havent seen any specific suggestions on your setup let me throw out
my two cents.. on at least a newbies suggestion how to start.

i would think you need an employees table
T_Employees
EmployeeID (PK)
FName
LName
and whatever else you want in the table

A Leave table
T_Leave
LeaveID (PK)
EmployeeID (FK)
LeaveStartTime
LeaveStopTime

then you could have a form F_LeaveRequest with the employees info and two
unbound boxes for LeavestartTime and LeaveStopTime
and a Submit Leave Button

in the buttons on-click event start with something like this

Private Sub CMD_Submit_Click()
Dim MatchCount As Integer

If IsNull([Forms]![F_LeaveRequest]![LeaveStartTime]) Or
IsNull([Forms]![F_LeaveRequest]![LeavestopTime]) Then
MsgBox "Start or End Time cant be Empty", vbOKOnly
Else

If DCount("leaveID", "T_Leave", "T_Leave.LeaveStartTime Between
[Forms]![F_LeaveRequest]![LeaveStartTime] And
[Forms]![F_LeaveRequest]![LeaveStopTime] OR T_Leave.LeaveStopTime Between
[Forms]![F_LeaveRequest]![LeaveStartTime] And
[Forms]![F_LeaveRequest]![LeaveStopTime]") > 0 Then
MsgBox "somebody allready has requested leave during that period", vbOKOnly
Else
If MsgBox("The leave you requested is available", vbOKCancel) =
vbCancel Then
Exit Sub
Else
'SetWarnings = False 'Un-Comment this to stop the update warnings
Please Be carefull when turning warnings off
DoCmd.RunSQL "INSERT INTO T_Leave ( LeaveStartTime, LeaveStopTime,
EmployeeID ) " & _
"SELECT [Forms]![F_LeaveRequest]![LeaveStartTime] AS LeaveStartTime,
" & _
"[Forms]![F_LeaveRequest]![LeaveStopTime] AS LeavestopTime, " & _
"[Forms]![F_LeaveRequest]![EmployeeID] AS EmployeeID"
SetWarnings = True
End If
End If
End If

End Sub

Of course there is a Ton more stuff you need to accomplish but sometimes a
little something to get the ball rolling helps alot

Duane Hookum has a CalendarReports.mdb sample that might have a good example
of a timeline type report that may help spot available Leave periods

Remember this is newbie ramblings so i Hope i dont send you off on the wrong
track
Have fun
Barry

Hi I am quite new to Access but I am desperate to learn as much as I can as
fast as I can.

[quoted text clipped - 16 lines]

.