Auto-populating the "next" in line value on form

[Scoob55k]

I have a form of which will open and I want to auto populate a text box in the form with the "next" in line value/person. The values are in a table, ordered alphabetically.

The twist added is that I have a yes/no field that will be "Yes" if the employee is in the office and of course "No" if out of the office or unavailible.

I need the process to fill the text box at the start of every new record and basically skip a person out of the office. It will then populate the text box with the next availible or "in office" employee.

I have some ideas, but want to put a feeler out there for a backup plan if I cannot figure it out soon?

Thanks!

 

[Remou]

What's your idea? How do you define 'next'?

 

[Scoob55k]

The values are 4 characters long (ex. "A1XY") and I need it to select the next one after I use "A1XY" in the form to create a record. Example being maybe "ADVB" and the next few being selected from the list below in alphabetical order:

CODF
F678
F78B
SI5K

There will be hundreds of these values eventually.

I used a Dlookup in a previous DB, but actually didn't write the code that populated the last date entered on the form. I figure I need to rewrite that and I'll get it. Just may take some work to get there considering the beginning of the list, "out of office" employee ID's, and the end of the list to start from the top.

Thanks!!

 

[Remou]

Is this a bound form? Is it not possible to bind the form to a query that excludes out of office people? You do not say if a flag exists to show if a person is selected.

Just a thought, will this not overburden employees on the lower end of the alphabet?

 

[Scoob55k]

Yes. It is bound to a table that will keep the data entered. The table has only 3 fields (tasknumber, ID, DateStamp). The [DateStamp] is kind of an AutoNumber that uses Now() for it's default value. This will assist in reporting later. I can sort decending on this value in a query to get the last ID entered, but getting the next "in office" ID is my problem.

Basically this is a task assignment DB. Fairly simple other than getting it to auto populate the ID field with the next "in office" ID. We need it to skip the "out of office" ID's and simply move to the next. Once it reaches the end of the list, it will then begin at the top ("Axxx") again.

I had other ideas that queried the amount of tasks per ID and sorted them ascending by count and ID. The assigner would then just pick the top ID from a combo box on the form. This posed an issue if the employee was not availible (using the yes/no check box field) and would play "catch up" when returned as that ID had the fewest entries. Trying to avoid that scenerio and automate it a little. Also thought about having some sort of code to add a "dummy" record for those "out of office" and using the count ID record entires to give me the next value.

I know this is simple once someone understands what I'm looking to do. Thanks for your help. Keep digging!

Clayton

 

[Remou]

I suggest you create a query. A DlookUp on this:

[tt]SELECT tblEmployees.ID
FROM tblEmployees
WHERE (((tblEmployees.ID) Not In (Select ID From tblTasks Where TaskDate=Date())) AND ((tblEmployees.OutOfOffice)=False))
ORDER BY tblEmployees.ID;[/tt]

Should suit.

 

[Scoob55k]

I back. Thanks for your previous help. I have a new problem concerning the same issue.

I got a query that gives me the next employee ID however my issue comes when more than one person is using the DB. I had it set up to populate the other fields once the Task field GotFocus. To avoid more than one user creating a record for the same task I had to create the record immediately by using Me.Refresh and make the Task field Not Required.

A problem I run into here is when a user is done or closes the form without entering data in the task field. The record is still created and moves onto the next Employee ID which is incorrect.

To remedy this I created a Delete Query to delete the "blank task" records upon closing. Seemed to work locally or at least when one user is using the database, but creates issues when more than one do so.

I've tried using on Change, Dirty, etc. with no true success as somewhere we end up with either a duplication of data, skipping an employee, slow data entry, or an error. I can give you a copy of the code I am using if needed. Any suggestions?

 

[Remou]

The way to do this is to use a separate table for the numbers. That way, you can lock the small table when you are creating the number and it won't matter if the number is not used. In addition, you will be able to undo the main table record if it is not completed.
You can see the professional way to do this here:
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184