Need: To AutoFill Value From Table/Query

[kh59743]

I'm hoping someone here can help me out with existing code or at least point me in the right direction... In one of the databases I am developing, I need to set up the ability for one field in NEW records to be autofilled with the next value from a table or query.

Details
My database includes the following objects:

• tblSupervisors - supervisor information (including an indicator if they are 'active')
• tblCases - case information including name of supervisor assigned to review this case
• frmNewCases - allows data entry of new information to tblCases

If someone enters a new case, the Supervisor field would (in an ideal world) automatically fill in with the name of the next active supervisor from tblSupervisors.

Example
The active supervisors in tblSupervisors are Joan, Mark, Lisa, and Gary.

Case #1 is entered and the Supervisor field is automatically pre-filled as Joan.

When Case #2 is entered later, the Supervisor field shows Mark.

When Case #3 is entered later, the Supervisor field shows Lisa. However, because the person entering the case knows Lisa is on vacation, s/he can click a button and Lisa's name is replaced with Gary.

Finally (for this example), Case #4 is entered and Joan is assigned as Supervisor again.


Whew!

Okay. That's it. (I hope.) Any and all help would be GREATLY appreciated!

 

[WayneRyan]

kh,

You can use the BeforeInsert event of the form to do a DLookUp:

Me.Supervisor = Nz(DLookUp("[Supervisor]", "tblSupervisors", "???"), "None.")

But what do you put for the criteria "???"
How do you know which one to assign?

I think what you really need is a child table for tblCases. You can assign
a supervisor/Date Stamp when the assignment changes.

You refer to the same case as Case #1, Case #2, etc. If tblCases is used
to track the chronology of the case, then tblCaseSupervisors can track the
chronology of the Supervisors.

Wayne

 

[kh59743]

Sorry for the confusion. Once the case is assigned to a supervisor, there should be no change. In other words if Joan is assigned the case, she keeps the case until it's been resolved.

As for the criteria (and thus the reason I need assistance), the default value should be whichever supervisor is next on the list since the last time a case was entered/assigned. So if the previous case (i.e. record) was assigned to Joan, then I'd like Access to look at the table of supervisors, find the next active supervisor and assign that supervisor to the new case.

So basically, it needs to find the value stored in that field in the last record as a comparison. It also needs to be "smart" enough to know that if there are no more active supervisors in the table to go back to the beginning of the table and start over again.

Again, any input someone might add would be greatly appreciated.