Prevent Overlapping Dates

[gallidor via AccessMonster.com]

I have an entry subform where I am trying to prevent overlapping dates.

TABLE NAME: tblMeasuresStaffLink
FIELD NAMES SUBFORM NAMES
StaffID Staffcmb
AssignmentTypesID AssignmentTypescmb
MeasuresID Measurescmb
AssignmentBeginningDate AssigneeBeginning Date
AssignmentEndingDate AssigneeEndingDate

The table has four primary keys: StaffID, AssignmentTypesID, MeasuresID,
AssignmentBeginningDate.

This code isn't giving me any error messages, but it doesn't seem to be
working.

Please provide feedback on the code or suggest an alternative.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "(" & Me!AssigneeBeginningDate & " > [AssignmentBeginningDate] And
(" & Me!AssigneeBeginningDate & " <= [AssignmentEndingDate]) And [StaffID] =
" & Me.Staffcmb & " And [AssignmentTypesID] = " & Me.AssignmentTypescmb & "
And [MeasuresID] = " & Me.MeasuresCMB & ")"
varResult = DLookup("[StaffID]", "tblmeasuresstafflink", strWhere)
If Not IsNull(varResult) Then
MsgBox "Beginning Date Overlaps with Prior Assignment." & varResult
Cancel = True
End If

End Sub

 

[Allen Browne]

Suggestions to help you debug this:

1. Test if any of the fields are null. (If they are, the Criteria won't be
valid.

2. Explicitly format the dates and delimit with #.

3. If any of these are Text fields (not Number fields), you need extra
quotes as delmiters. Details:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

4. Bracketing the phrases in the Criteria can help.

5. Debug.Print the result to see what comes out.

6. Add a Stop (or breakpoint) so you can see if it's executing. You can then
press Ctrl+G to see what you got in the Immediate Window, and compare it to
a query that works with similar criteria, i.e. a query you created in design
view, and then switch to SQL View (SQL View on View menu, in query design.)

7. Make sure you are being notified of errors. In the code window, choose:
Tools | Options | General
and set Error Traping to:
Break on Unhandled Errors.
(Make sure Compile On Demand is unchecked while you are there.)

Example:

Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not (IsNull(Me.AssignmentBeginningDate) _
OR IsNull(me.AssignmentEndDate) _
OR IsNull(Me.Staffcmb) _
OR IsNull(Me.AssignmentTypescmb) _
OR IsNull(Me.MeasureID)) Then

strWhere = "(AssignmentBeginningDate < " & _
Format(Me.AssignmentEndDate, strcJetDate) & ") AND (" & _
Format(Me.AssignmentBeginningDate, strcJetDate) & _
" < AssignmentEndDate) AND (StaffID = " & Me.Staffcmb & _
") AND (AssignmentTypesID = " & Me.AssignmentTypescmb & _
") AND (MeasuresID = " & Me.MeasuresCMB & ")"

Debug.Print strWhere
Stop
varResult = DLookup(...
'etc
End If


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an entry subform where I am trying to prevent overlapping dates.

TABLE NAME: tblMeasuresStaffLink
FIELD NAMES SUBFORM NAMES
StaffID Staffcmb
AssignmentTypesID AssignmentTypescmb
MeasuresID Measurescmb
AssignmentBeginningDate AssigneeBeginning Date
AssignmentEndingDate AssigneeEndingDate

The table has four primary keys: StaffID, AssignmentTypesID, MeasuresID,
AssignmentBeginningDate.

This code isn't giving me any error messages, but it doesn't seem to be
working.

Please provide feedback on the code or suggest an alternative.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "(" & Me!AssigneeBeginningDate & " > [AssignmentBeginningDate]
And
(" & Me!AssigneeBeginningDate & " <= [AssignmentEndingDate]) And [StaffID]
=
" & Me.Staffcmb & " And [AssignmentTypesID] = " & Me.AssignmentTypescmb &
"
And [MeasuresID] = " & Me.MeasuresCMB & ")"
varResult = DLookup("[StaffID]", "tblmeasuresstafflink", strWhere)
If Not IsNull(varResult) Then
MsgBox "Beginning Date Overlaps with Prior Assignment." & varResult
Cancel = True
End If

End Sub

 

[gallidor via AccessMonster.com]

Thank you for the code. I am now getting a "method or data member not found"
error.

I have been reading up on this error and trying to fix, but to no avail.

Any suggestions?

Suggestions to help you debug this:

1. Test if any of the fields are null. (If they are, the Criteria won't be
valid.

2. Explicitly format the dates and delimit with #.

3. If any of these are Text fields (not Number fields), you need extra
quotes as delmiters. Details:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

4. Bracketing the phrases in the Criteria can help.

5. Debug.Print the result to see what comes out.

6. Add a Stop (or breakpoint) so you can see if it's executing. You can then
press Ctrl+G to see what you got in the Immediate Window, and compare it to
a query that works with similar criteria, i.e. a query you created in design
view, and then switch to SQL View (SQL View on View menu, in query design.)

7. Make sure you are being notified of errors. In the code window, choose:
Tools | Options | General
and set Error Traping to:
Break on Unhandled Errors.
(Make sure Compile On Demand is unchecked while you are there.)

Example:

Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not (IsNull(Me.AssignmentBeginningDate) _
OR IsNull(me.AssignmentEndDate) _
OR IsNull(Me.Staffcmb) _
OR IsNull(Me.AssignmentTypescmb) _
OR IsNull(Me.MeasureID)) Then

strWhere = "(AssignmentBeginningDate < " & _
Format(Me.AssignmentEndDate, strcJetDate) & ") AND (" & _
Format(Me.AssignmentBeginningDate, strcJetDate) & _
" < AssignmentEndDate) AND (StaffID = " & Me.Staffcmb & _
") AND (AssignmentTypesID = " & Me.AssignmentTypescmb & _
") AND (MeasuresID = " & Me.MeasuresCMB & ")"

Debug.Print strWhere
Stop
varResult = DLookup(...
'etc
End If

I have an entry subform where I am trying to prevent overlapping dates.

[quoted text clipped - 32 lines]

End Sub