Stop Booking if entry already exists

[mosh]

Hello All,

I've recently created a holiday booking form for employees. The only problem I have noticed is the user can double book someone even though the employee has the requested day already booked.

Is there a way where to refuse booking if the employee has already booked the day already? (Leave Type - Holiday)

Process:

1) Select employee via combo
2) Select Leave Type - [leave_type]This only applies to Holiday
3) Select part of day
4) Select Day to book (once date has been entered a subform shows who is off on that day). [date_booked]

At 4) I want it to check to see if there is already a booking by the employee, If so to either delete current.

Thanks
________
Live Sex Webshows

 

[mosh]

Hello All,

I've recently created a holiday booking form for employees. The only problem I have noticed is the user can double book someone even though the employee has the requested day already booked.

Is there a way where to refuse booking if the employee has already booked the day already? (Leave Type - Holiday)

Process:

1) Select employee via combo
2) Select Leave Type - [leave_type]This only applies to Holiday
3) Select part of day
4) Select Day to book (once date has been entered a subform shows who is off on that day). [date_booked]

At 4) I want it to check to see if there is already a booking by the employee, If so to either delete current.

Thanks
________
Web shows

 

[ColinEssex]

Try this or do a search - it's been covered loads of times.

here

Col

 

[mosh]

Try this or do a search - it's been covered loads of times.

here

Col

Thanks, its slightly more complicated as it's reqired in two fields that needs to be checked for duplicated (payroll number & date booked). I'll have a go at adjusting the code for this.

Any help is appreciated.

Thanks once again.
________
Cheap glass pipes

 

[ted.martin]

You will need to use the DLOOKUp function. This code checks for the existence of a record; maybe you can adapt it to your needs.

Private Sub DateField_LostFocus()

Dim varX As Variant

varX = DLookup("[A Field]", "A Table", "[A Field] = ' " & DateField.Value & " ' ")
'
If IsNull(varX) Then
'OK = blank

Else
MsgBox "Holiday already booked etc"
' so do something else

End If
'
End Sub

 

[mosh]

This is what i've adjusted so far, but I cannot work out how to check two fields (date and the payroll number), the parts in bold is where theres needs to be another field?

Thanks


Private Sub date_booked_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim var As Date
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strpayroll_number.Value
var = Me.date_booked.Value
stLinkCriteria = "[strpayroll_number]=" & "'" & SID & "'"

'Check Holiday table for duplicate Holiday
If DCount("strpayroll_number", "qryHoliday", stLinkCriteria) > 1 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Employee " _
& SID & " has already Booked this day off." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
________
Child nexium

 

[mosh]

Update;

Private Sub date_booked_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim var As Date
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strpayroll_number.Value
var = Me.date_booked.Value
stLinkCriteria = "[strpayroll_number]&[date_booked]=" & "'" & SID & "'"

'Check Holiday table for duplicate Holiday
If DCount("strpayroll_number", "qryHoliday", "date_booked", stLinkCriteria) > 1 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Employee " _
& SID & " has already Booked this day off." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
________
SweatDreams

 

[neileg]

Why not create a compound index, no duplicates on the employee and the date. This will stop the booking and there's no code unless you want to trap the error message.

 

[mosh]

that would mean an employee cant be sick on a day he has booked holiday.

Only if the leave_type is holiday then stop the booking.

Just to reiterate; user selects employee via combo box, then leave type etc. then once the date is selected it should see whether the employee has booked a holiday already on that date.

But if the employee is sick / AWOL then another booking can be made.

HTH
________
White Girls Webcams

 

[ColinEssex]

But if a sick day is entered and holiday is already booked, the holiday should be cancelled for the period of sickness and the days holiday should be added back on the persons holiday allocation.

Also, how can an employee be AWOL during a holiday period?

Col

 

[mosh]

But if a sick day is entered and holiday is already booked, the holiday should be cancelled for the period of sickness and the days holiday should be added back on the persons holiday allocation.

Also, how can an employee be AWOL during a holiday period?

Col

your correct, but sickness is a leave type, reason why I want it like this is I am running a report where an employee has booked a holiday but then goes sick on the same day. What should happen is once the user enters a "sick" booking on the same day it removes the hours from the holiday booking but keeps both records.

Is there a way of doing this?

Thanks
________
Avandia class action settlement

 

[mosh]

any more help?

mos
________
Paxil Death