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.

 

[03SCBeast]

ACE...You hit the nail right on the head as far as the problem I'm trying to resolve is concerned.

PHV...I ran your code as is and then I tried a couple of variations. When I went back to my subform (based on underlying input query) and entered overlapping dates, no VB Message was triggered.

I did not base the subform on the underlying tblMANAGERUPDATES because I wanted to show days a manager was assigned to a project. Would the fact my input subform is based on a query rather than a table be the reason why the code is not working? If so, I'll go back and base the input subform on a table.

Again...many thanks for all of your help and patience.

 

[TheAceMan1]

03SCBeast . . . . .

Did you substitute the proper table name for [blue]your table name here[/blue] in [blue]PHVs[/blue] code?

See Ya! . . . . . .

 

[03SCBeast]

TheACE...yes, I did.

 

[TheAceMan1]

[blue]Would the fact my input subform is based on a query rather than a table be the reason why the code is not working?[/blue]

No! . . . . . if you look at the code [blue]PHV[/blue] gave you, it looks at the table directly! ([purple]doesn't matter what the forms RecordSource is![/purple]), and the code [blue]PHV[/blue] quotes is more or less an everyday occurance. So something else is wrong. This prompts the following questions:
[ol][li]Are you looking at the [blue]proper table[/blue]?[/li]
[li]Is it [blue]Manager[/blue] or [blue]Manager(s)[/blue]?[/li][/ol]
Now, manager_ID pings on a specific record for comparsion ([purple]doesn't allow looking at the entire table[/purple]). Under this premise, I would modify as below:

[blue]If DCount("*", "[[purple][b]your table name here[/b][/purple]]", "#" & Me!EndDate & "# Between [[purple][b]Start_Date[/b][/purple]] And [[purple][b]End_Date[/b][/purple]]") > 0[/blue]

Be sure to [blue]substitute the proper field names[/blue] (in the table) for [purple]Start_Date[/purple] & [purple]End_Date[/purple].

Let us know how this works out! . . . . .

See Ya! . . . . . .

 

[03SCBeast]

TheAce...I'll double check all of my references and substitutions today and get back to you with the results. I'm sure it's going to prove to be something very simple as it's always been.

 

[03SCBeast]

Hello guys...I'm posting back on the results thus far and I'm sure you guys are going to hang me by my toes. I've discovered at least two major factors that may have contributed to code not working after checking all of forms and underlying tables.

Problem 1: The bound column was set to the actual name of the manager instead of the respective ID. When the code searched for an ID in the underlying tables, there were none because the cboManager_ID was storing first names in the table.

Problem 2: I believe some of my IDs were formatted in 00000 format on the forms (Manager_ID # 41 was stored as 00041, etc.) but not in all the underlying tables.

Nonetheless, I should probably clarify exactly how I've got the tables arranged in case there are any more implications.

tblProjects: Project_ID, Project_Name, Project_Type...

tblManager_Updates: Trans_ID, Project_ID, cboManager_ID, Start_Date, End_Date

tblManagers: Manager_ID, Manager_First, Manager_Last...

frmManagers is situated as a subform on mainform frmProjects. cboManager_ID is populated by tblManagers.

Goal 1: To see record of Managers for a particular project when I use the record selector to scroll through projects.

Goal 2: To prevent overlapping dates between the managers for each particular project.

Goal 3: A manager can be assigned to concurrent projects but as stated above, only one manager can be assigned to a particular project at a time.

Goal 4: The same manager can be assigned to a particular project in the future.

 

[03SCBeast]

I think we finally got it guys... :-}.

I've been tinkering around with PHV's code and it worked with a few slight modifications.

Here's the final code for anyone else who runs into this situation down the line:

'Code to prevent overlapping time intervals for a particular project'
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "[tblManager_Updates]", "Project_Id=" & Me!Project_ID & " AND #" & Me!Start_Date & "# 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!Start_Date.SetFocus
Exit Sub
End If
End Sub