Table should not be edited 2

[barnard89]

Hi

I am using Access 2000
I have an employee table with empno , deptno, empname , designation fields

The designation is like manager , clerk, officer etc
For any dept no .. there can be only one employee whose designation is 'MANAGER'
However there can be any number of 'OFFICER' or 'CLERK'
in the table
The table already has one employee whose desination is 'MANAGER'
How can I lock the table so that no one can edit the table so that they can try to insert a record or modify a record where the designation is 'MANAGER'
This should happen only if a 'MANAGER' is already present in the table.

They can however be able to change the 'MANAGER' to 'OFFICER ' or 'CLERK' etc
How can I do this
Please suggest

Thanks

 

[Remou]

Hi
You could use the Before Update event to open a recordset, which selects from your table where designation is Manager (or DlookUp). If a record is found with a different ID from the current record, cancel the update with a warning.

 

[TheAceMan1]

How are ya barnard89 . . . . .

Using Access Security you can't set permissions for individual records, and since your user interface should prevent table access, your left with controlling thru forms.

Example in the OnCurrent event of a form:

[blue]   If Me!Designation = "Manager" then
      Me!AllowEdits = False
   Else
      Me!AllowEdits = True
   End If[/blue]

See Ya! . . . . . .

 

[Remou]

Hi AceMan
Considering:

They can however be able to change the 'MANAGER' to 'OFFICER ' or 'CLERK' etc

Will that work?

 

[TheAceMan1]

Howdy [blue]Remou![/blue]

Yes I saw that. Also saw:

[blue]lock the table so that [purple]no one can edit the table so that they can try to insert a record or modify a record where the designation is 'MANAGER'[/purple][/blue]

Put the two together and it becomes ambiguous. So I just picked a side.

See Ya! . . . . . .