Combo Box Requery 1

[EllieFant]

Good Afternoon,

I am creating a database for employees to take a "Quiz". Once they complete the quiz they get to choose from a list of available awards. I have the combo box set up to show only those awards available when the form is opened.

If an employee leaves the form open for a couple minutes before making their selection, that award may not be available any longer. I am going to use the timer to requery the combo box every so many seconds but if they have an award selected (but they haven't pressed the button to process their selection) I would like to be able to remove their selection from the combo box ONLY if that award is no longer available (meaning it wouldn't be in the list anymore).

Any ideas on how to accomplish this? I plan on having code behind the button that will double check the availability of the award before it is processed, but not sure that is enough (for those that leave the form open for an extended period of time).

Thanks!
Lena

 

[4N6Mstr]

Ellie,

Timing will be always an issue in this case. You probably will need to create a table associated with each individual award (one record for each award unit), and that can be very time consuming to maintain.

In terms of attacking the problem on the code, I’d use a triple level availability-confirmation structure (without the timer). When the list of items of the combo box is generated (first level) the user will see everything that is available at that point in time.

The user then makes his / her selection. Then the after-update event generates an updated list (second level) and checks the availability of the item at that point. If the item is not available anymore, the user is informed (with a nice and apologetic msg) and directed to choose another award from the combo box (first level needs to be updated here), otherwise “Confirm your selection!” message is displayed and the corresponding award is put “on hold” (meaning not available for anyone else to select).

After the user confirms the selection, a new list (third level) is generated (this is really a “just-in-case” approach, most likely could be left out). If the award is still available, then the award record status is updated to identify it was “awarded” (probably also good to keep the information of user to whom it was given to), otherwise an even more apologetic msg sends the user back to the combo box selection (don’t forget to update level one here again). If the user changes his / her mind and decides not to select that award, just release it back to the pool of rewards.

In this structure, even if the user leaves the application unattended for a long time in between his / her inputs (selection from the combo box list and confirmation of accepting the award), the comparison with the updated list of the next level would catch that the award isn’t available anymore.

The drawback I see is that, if a user selects the last unit of a given award type and then decides not to take it, that last award of that type will be back on the pool. Other user that tried to select the award while it was on hold and was not allowed could later on see that last award was given to someone else. But that is a HR situation not a programmer problem.


Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[EllieFant]

Thank you for your insight. I know if I don't program in a way to remove the award from the list of available awards, then I will have that problem of two people selecting the same thing at the same time. I figure if I plan for it, then it won't happen (my luck seems to run that way...buy a snow shovel and it don't snow).

I have a combo box they will select the prize from. They will click a button saying that is their selection and that is it...no changing their mind once the selection has been made.

I am not sure how to confirm the award is still available in the after update event unless it is the same way I am going to do it behind the button....create a recordset and if the count is less than 1 then the prize isn't available anymore.

Anymore insight on this would be appreciated! I think I have a path to travel...just hope I don't get lost

Lena/Ellie

 

[4N6Mstr]

Ellie,

That is exactly what the logic that I proposed does. When the combo box is made available for the user, the list of awards (by type) is updated, thus only available awards are listed.

When the user selects the award, in the after update event code, you have to query the awards lists again and check for availability (by award item, not type), if available you put it on hold and show the button to confirm the choice (you can also show a more detailed description of the award here). This approach it's even more user friendly, as it gives the user the chance to go back if he / she decides to get something else after checking the detailed description.

And because the award (unique item) is placed on hold after the selection, nobody else can select the same unique item (that's is why you need the table with one record per each award unit, or two tbl_Items and tbl_Unique_Items, which would be more appropriate in terms of normalization). If the user changes his / her mind, just release the item back to the award pool.

The only chance of two different users choosing the same award unique item would be if they selected the award from the combo box at the absolute exact same time (any difference in time would cause the hold to be placed on another unique item of the same award type). In the extreme case where the selection time was exact the same and that was the last item of that type, only one user will be able to place the hold on the item (the record will be locked by one of the users), thus the "hold that item" will fail for the other useer, whom then would receive the message indicating item is not available anymore. Note that the time that the user takes to select a award from the list has no bearing in this logic, as everything happens immediatelly after the award is selected from the combo box.

As a overkill, you can perform a final check after the comfirm button is pressed.

I would put that on code for you, but I'm short on time, sorry.

Hope this helps.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[EllieFant]

Thanks again for your help. I think I have the basics of it working.

Lena/Ellie