Stuck on Form and query issue Access 2000

[simpsondm]

Could someone Please help? Here’s my dilemma I have two tables the main table in tbl_mainproj the second table is tbl_staff the tables are linked by Project_ID. What I’m trying to do and have been unsuccessful is this.

I want to be able to enter a staff name in one or multiple fields of a form and when doing so have a search done against the staff table if the search finds the name entered listed in the table 5 times or more it will issue a pop up message (stating staff is assigned to 5 or more projects) click continue to assign the person or exit to enter another name.

I also want to be able to do a query that will allow me to enter in a staff name and it will show all records associated with that name.

My staff table has 13 fields for the different work areas such as (QA, Network Engineer, App Support, App Development, Web Development, Security, Project Manager, Tech Lead, etc.) This is the table I need to pull against.

 

[SBendBuckeye]

Let's assume you have a combobox named cboProject which allows you to assign projects to your staff and that the bound column is ProjectID. In the BeforeUpdate code for cboProject put something like the following:

If DCount("Project_ID", "tblStaff", "Project_ID = " _
& Me.cboProject.Value & &quot" > 5 Then
If vbNo = Msgbox("5 or more Projects, Continue?", _
vbYesNo + vbQuestion) Then
Me.cboProject.Undo
Cancel = True
Exit Sub
End If
End If

You use before update because it exposes a Cancel parameter which if set to true returns focus to the control regardless of what key is press or where the mouse clicks.

Hope this helps and good luck!