How to select a record based on multiple criteria?

[diberlee]

Hi,

I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:

Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.

Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.

When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.

Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this

I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:

Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.

I think it would be pretty much the same in Access, but I'm having trouble finding a good example that I can use to get started figuring this out. I was hoping that someone could write up some basic code that selects the next record based on what I described above, and passes that info to a form. I'm pretty sure I can figure out the rest of it from there.

Also any suggestions on the approach I have listed would be appreciated, maybe I could make this a lot easier by changing how I plan to do it?

Thanks
Diberlee

 

[diberlee]

I should add that priority and timed clients will have a TimetoCall field set to a later time to control when they are given to another user. So if we try a priority client at 18:10 and make no contact the field will be set to 18:30, and that field will be checked to prevent the priority clients from being given to another user immediately after.

So my background code for the "next" will be doing something like:

IF priority clients exist AND (attempts = 0 OR time to call < Now)
THEN give the next priority client
 
ELSE
 
IF timed client exists AND time to call < Now
THEN give the next timed client
 
ELSE
 
find next record where attempts = 0 or attempts < highest attempts

I'm working through help files trying to figure out how to do all this, but the help files are bad compared to 2003. I always seem to just get a bunch of links to external sites (like VBA for dummies)