ACCESS FEED: Re: [MS_AccessPros] Preventing data entry error

Senin, 10 Februari 2014

Re: [MS_AccessPros] Preventing data entry error

Phucon-

No code needed. Instead of a single index on EmpID (no duplicates), create a compound key on ProjectID / EmpID and make that Unique. That will keep an employee from being assigned to the same project twice, but will leave the employee free to be assigned to other projects.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 10, 2014, at 9:22 PM, <saigonf7q5@yahoo.com> <saigonf7q5@yahoo.com> wrote:

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