[MS_AccessPros] Preventing data entry error

 

I have an EmpProject table which stores Project and employee Information, the table contains these fields: ID (Primary key, Autonumber, an ID for the table.),  ProjectID (number, Yes (Duplicates OK), EmpID (Yes (No Duplicates), EmpName, EmpTitle, DeptCode.

The ProjectID of the EmpProject table is linked to the ProjectID (Primary key, Yes (No Duplicates)) of the Project table in one to many relationship. One project can have more than 1 employee,but cannot assign the same employee twice on the same project. However, the same employee can be assigned to another or different projects. To avoid assigning duplicated employee to the same project, the EmpID was set to "Yes (No Duplicates)". But this will also prevent assigning the employee to another or different projects.

With the tables and relationship set up above and the code that's shown below, it can only prevent users entering the same employee twice in a project, but how do I allow the same employee be assigned in another or different project?

Thanks

Phucon

Private Sub cmdAssign_Click()

On Error GoTo Errorhandler

 

Set f = Me!frmEmpProject.Form

Set rs = f.RecordsetClone

 

rs.FindFirst "[empID] = " & Me.cboEmpInfo.Column(0)

If Not rs.NoMatch Then

     MsgBox "Duplicated employee.", vbExclamation, "Information"

Else

     With f

          !txtempID = Me.cboEmpInfo.Column(0)

          !txtempName = Me.cboEmpInfo.Column(1)

          !txtempTitle = Me.cboEmplnfo.Column(2)

          !txtDeptCode = Me.cboEmplInfo.Column(3)

     End With

End If

 

ExitProcedure:

     Exit Sub

 

Errorhandler:

    MsgBox Err.Number & vbCrLf & Err.Description

    Resume ExitProcedure

End Sub

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___