By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Display an Alert

P: n/a
Hi All

Our Company is provided courses to employees for one or more days i.e.
there are three field (EmpNo, StartDate and EndDate) of course. I am
trying to display an Alert message when a perticular employee has to
attend a new course If he is busy in the other course between these
two dates.

Thanks in advance

Naushad
Nov 17 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You have an overlap between event A and event B if:
- A starts before B ends, AND
- B starts before A ends, AND
- it's the same employee, AND
- it's not the same event.

To achieve the 4th part, you will need a primary key in your table.
The example below assumes an AutoNumber primary key named ID.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If ((Me.EmpNo = Me.EmpNo.OldValue) AND _
(Me.StartDate = Me.StartDate.OldValue) AND _
(Me.EndDate = Me.EndDate.OldValue)) OR _
IsNull(Me.EmpNo) OR _
IsNull(Me.StartDate) OR _
IsNull(Me.EndDate) Then
'do nothing
Else
strWhere = "(EmpNo = " & Me.EmpNo & ") AND (StartDate <= " & _
Format(Me.EndDate, strcJetDate) & ") AND " & _
Format(Me.StartDate, strcJetDate) & _
" <= EndDate) AND (ID <" & Me.ID & ")"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clashes with event id " & varResult
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Naushad" <nh*****@kockw.comwrote in message
news:b3**********************************@r36g2000 prf.googlegroups.com...
Hi All

Our Company is provided courses to employees for one or more days i.e.
there are three field (EmpNo, StartDate and EndDate) of course. I am
trying to display an Alert message when a perticular employee has to
attend a new course If he is busy in the other course between these
two dates.

Thanks in advance

Naushad
Nov 17 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.