Set Employee On Call Order and Rotate

[burrina]

I have a employees form and on it there are fields named On Call which is a yes/no and Order which is a number and also of course their name and employeeID

I can easily set their on call status by using the yes/no field and maybe enter a number manually into the field Order which is set to be indexed with no duplicates. How can I automate this? Also when its time to start their on call rotation, how would I approach this?

Fields in db are OnCall, EmployeeID,Order, and EmpName.

Thanks,

I updated the db and attached.

[Robeen]

You haven't given enough information about what must happen automatically.
You will have to explain the logic behind how you want to automate.

[burrina]

I have 3 fields on my form
ExpName
Order
On Call

I have set in place a increment counter for the order field which is a number data type
On the On Current Procedure here is my code
If Not IsNull(Me.Order) Then
Me.OnCall = True
Else
Me.OnCall = False
End If


And on the before update event of the field named Order here is my code

Nz (DMax("Order", "Employees")) + 1


I want the form to accomplish a numerically incremental counter so I can set the employees
on call order automatically. Also if the user tries to use one field without the other one
having data in it then it will not be allowed.

I also want to be able to re-set this counter at some point since the employees will need to
start over in their on call order and maybe I will need a way to adjust this.

Any thoughts if I am doing this right or is there a better way? Hope I have explained it better,sorry for my lack of communication skills. my brain does not work most of the time. I have Multiple Sclerosis and I am doing this to try and keep my brain active. I wrote this db over 12 yrs ago.

Thanks,