Prevent Overlapping Dates

  • Thread starter Thread starter gallidor via AccessMonster.com
  • Start date Start date
G

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
 
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.
gallidor via AccessMonster.com said:
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
Click to expand...
 
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?

Allen said:
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.
Click to expand...
[quoted text clipped - 32 lines]
End Sub
Click to expand...
Click to expand...
 
Back
Top