Help with a 'Rolling List'

[svenman]

Hi all!

I am looking for a bit of help with an application in Access '97. I
am trying to come up with a method to show the next available
salesperson, and present it in a listbox. Let's say that I have a
list of 5 salespeople and the initial order is like this:

'Bob, Mary, Alice, Dave, and Bruce'

If a customer walks into the show room, Bob should be the salesperson
to work with the customer. Bob's name should be presented at the top
of a list box (and highlighted for selection). Once the system admin
selects Bob's name and creates a new record, the listbox should be
updated for the next scenario like this:

'Mary, Alice, Dave, and Bruce, Bob'

When the next customer walks into the showroom, 'Mary' would then be
presented at the top of the list (and highlighted for selection).

Any suggestions for this?

Thanks,

Svenman

 

[tina]

one solution would be to add a Date/Time field to the table where the
records are being created, with a DefaultValue of

=Now()

then you can populate the listbox from a Totals query that groups the table
records by salesperson and pulls the Max value for each from the Date/Time
field and orders that field Ascending (oldest date/time to newest).

hth

 

[Tom Wickerath]

Hi Svenman,

I threw together a quick sample tonight, which you are welcome to download
if you want. It is 283 KB zipped (I retained the photos from the Northwind
2000 database).

http://home.comcast.net/~tutorme2/samples/roll_em.zip

I have to say that I liked the effect of using a combo box in leiu of a list
box. Initially, when the form first opens, the combo box is not sorted by
last selected, since I left this data nulled out.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

You have a good solution, but may I suggest that you don't even show a sales
person who isn't available. That way, there will never be any confusion.

 

[Svenman]

Hi Svenman,

I threw together a quick sample tonight, which you are welcome to download
if you want. It is 283 KB zipped (I retained the photos from the Northwind
2000 database).

http://home.comcast.net/~tutorme2/samples/roll_em.zip

I have to say that I liked the effect of using a combo box in leiu of a list
box. Initially, when the form first opens, the combo box is not sorted by
last selected, since I left this data nulled out.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks! I can't tell you how much I appreciated the responses. I
added a small flag that indicates if the salesperson is out of the
office and excluded that item from the selection. It works perfectly
now.

I think I just tried to over-complicate the process, and didn't see
the solution.

Thanks again!

Svenman