How Do I Prevent Overlapping Dates?

[03SCBeast]

I'm trying to write code to prevent overlapping periods a manager is assigned to a project.

Here's the code:

If Me!EndDate <= Forms!frmManager!EndDate And _
Me!EndDate >= Forms!frmManager!StartDate Then _ MsgBox "The start date is included in a previous manager's period.", vbInformation,
Me.Caption
Cancel = True
Me!EndDate.SetFocus
Exit Sub
End If
End Sub

The error message I keep getting is something like "cannot find form frmManager referred to in macro or visual code"

Any help would be greatly appreciated.

 

[Lightning]

You don't say where you are running this code from, so this is a bit of a guess.

You would get that error message if the frmManager form is not open when the code runs. Try opening the form in the background before the code runs.

HTH
Lightning

 

[lamarw]

Try using the other method of referencing forms:
'form_frmManager!EndDate' Notice it's not forms! but form_

See if that makes the difference. Don't forget to refer to the .value property or the .text property in your EndDate and StartDate objects (if they ARE objects)

Lamar

 

[TheAceMan1]

How are ya 03SCBeast . . . . .

If [purple]frmManager[/purple] is a subform, try this:

[blue]   Dim sfrm As Form, Msg As String, Style As Integer, Title As String
   
   Set sfrm = Forms![purple][b]MainFormName[/b][/purple]!frmManager.Form
   
   If Me!EndDate <= Forms!frmManager!EndDate And _
      Me!EndDate >= Forms!frmManager!StartDate Then
      Msg = "The start date is included in a previous manager's period."
      Style = vbInformation + vbOKOnly
      Title = Me.Caption
      MsgBox Msg, Style, Title
      Cancel = True
      Me!EndDate.SetFocus
   End If

   Set sfrm = Nothing[/blue]

See Ya! . . . . . .

 

[03SCBeast]

Thanks everyone for your input. I'll try it all out today and post back on results. frmProject is the main form, frmManager is one of the tab-controlled pages of frmProject.

 

[TheAceMan1]

03SCBeast . . . . .

Forgot to add certain changes. Corrected code below . . .

[blue]CODE
   Dim sfrm As Form, Msg As String, Style As Integer, Title As String
   
   Set sfrm = Forms![purple][b]MainFormName[/b][/purple]!frmManager.Form
   
   If Me!EndDate <= [b]sfrm[/b]!EndDate And _
      Me!EndDate >= [b]sfrm[/b]!StartDate Then
      Msg = "The start date is included in a previous manager's period."
      Style = vbInformation + vbOKOnly
      Title = Me.Caption
      MsgBox Msg, Style, Title
      Cancel = True
      Me!EndDate.SetFocus
   End If

   Set sfrm = Nothing[/blue]

See Ya! . . . . . .

 

[03SCBeast]

I've borrowed a little from all of the input above and here are the results.

My Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim sfrm As Form, Msg As String, Style As Integer, Title As String

Set sfrm = Form_frmManagers.Form

If Me!ENDDATE <= sfrm!ENDDATE And _
Me!ENDDATE >= sfrm!STARTDATE Then
Msg = "The start date is included in a previous manager's period."
Style = vbInformation + vbOKOnly
Title = Me.Caption
MsgBox Msg, Style, Title
Cancel = True
Me!ENDDATE.SetFocus
End If

Set sfrm = Nothing

End Sub

Results- both amendments to referencing the form solved the error message I kept getting. The only problem I have now is that I get the error message no matter what ENDATE Date I enter.

 

[TheAceMan1]

03SCBeast . . . . .

Replace:
Set sfrm = Form_frmManagers.Form (improper reference!)
with:
Set sfrm = Forms![purple]MainFormName[/purple]!frmManager.Form

See Ya! . . . . . .

 

[03SCBeast]

ACE...I made your substitution but that generated "run time error 2465, can't find field frmManager referred to in your expression".

 

[earthandfire]

03SCBeast, I don't know whether this was just a typing error in you last post, but you have frmManager, whereas in your earlier code you use frmManagers

 

[03SCBeast]

It was a typo on this post. My actual code has frmManagers as in previous posts. It seems like the code ACE suggested is looking for a field rather than a table according to the run time error.

 

[earthandfire]

In which case try:

Set sfrm = Form!frmManagers.Form

or

Set sfrm = Form!["frmManagers"]

 

[TheAceMan1]

03SCBeast . . . . .

So close but so far away. This appears to be a referencing (done everyday) problem:
[ol][li]Be sure of [blue]spelling[/blue] for frmManager(s).[/li]
[li]Is frmManagers a Mainform, subForm, previously opened form?[/li]
[li]Which form/event is the code running from?[/li][/ol]

See Ya! . . . . . .

 

[03SCBeast]

I tried out both of your codes for Set sfrm and got error messages both times. The Set sfrm setting listed below is the only one that does not trigger the error message so I think we've made some progress.

The only problem now is that the "The start date is included in a previous manager's period" error message pops up no matter what date I enter in the [TO] field so I think the problem lies with the If statement.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim sfrm As Form, Msg As String, Style As Integer, Title As String

Set sfrm = Form_frmManagers.Form (works fine)


If Me!TO <= sfrm!TO And _
Me!TO >= sfrm!FROM Then
Msg = "The start date is included in a previous manager's period."
Style = vbInformation + vbOKOnly
Title = Me.Caption
MsgBox Msg, Style, Title
Cancel = True
Me!TO.SetFocus
End If

Set sfrm = Nothing

End Sub

 

[earthandfire]

Have you got <= and >= the right way round?

 

[03SCBeast]

Sorry Ace...missed your post.

frmProjects is main form and frmManagers is subform.
Code is running on BeforeUpdate event of frmManagers.

I tried other If statements in lieu of the one we're trying to get to work and they worked fine so I think the form referencing issues are resolved.

 

[TheAceMan1]

03SCBeast . . . .

OK . . . . Me!ENDDATE is apparently on the subform (frmManagers), so I expect the dates you pinging against (Start/End Date) are on the mainform (frmProjects). If this is true the following should work:

[blue]   Dim frm As Form, Msg As String, Style As Integer, Title As String
   
   Set frm = Forms!frmProjects
   
   If (Me!ENDDATE <= frm!ENDDATE) And _
      (Me!ENDDATE >= frm!STARTDATE) Then
      Msg = "The start date is included in a previous manager's period."
      Style = vbInformation + vbOKOnly
      Title = Me.Caption
      MsgBox Msg, Style, Title
      Cancel = True
    End If

   Set frm = Nothing[/blue]

See Ya! . . . . . .

 

[03SCBeast]

ACE...frmManagers is indeed the subform. Hoever, the dates I wish to ping against are the START/END Dates of any previous records in the same subform, not the main form.

Thanks so much for your patience and help.

 

[TheAceMan1]

03SCBeast . . . . .

T[blue]he cat finally comes out of the bag![/blue] I had an experienced feeling there was more under the hood! Before I attempt any more code, lets see if I read you correctly . . . .

[blue]Your entering a new record, and you want to insure, your ENDDATE entry is not included in any span of dates (Start to End) for all previously saved records![/blue]

[purple]Modify the quote as necessary . . . . .[/purple]

See Ya! . . . . . .

 

[PHV]

If DCount("*", "[your table name here]", "Manager_Id=" & Me!Manager_Id & " AND #" & Me!EndDate & "# Between Start_Date And End_Date") > 0 Then
MsgBox "The start date is included in a previous manager's period.", vbInformation, Me.Caption
Cancel = True
Me!EndDate.SetFocus
Exit Sub
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244