Combo Box Updates

[wop0703]

I have a time database which has a form to enter time. When entering time, The user must also select an item in a combo box that gives what category the user worked in. There are about 40 or so different categories. I want to know if it is possible to update the cobo box to the user's most recent selections. For example: if the user selects Management, which is at the bottom of the list, I would like to see Management at the top of the next list for the next time entry so that the user does not have to scroll down to the bottom again. Anyone have any ideas???

Thanks

 

[cheuschober]

To do this, you'll need to have a datestamp attached to this time field, one would assume there is.

So when you put together the recordsource combobox do a sort descending order by linking in the table your form is based on.

Example, if your form is based on tblWorkTime and has the field WorkEnded (as a date/time) and the field WorkType we assume that you populate your combobox from a lookup table that we'll call lkupWorkType with the field WorkType.

So, that said, your recordsource should look like:

SELECT lkupWorkType.WorkType
FROM lkupWorkType INNER JOIN tblWorkTime ON tblWorkTime.WorkType = lkupWorkType.WorkType
ORDER BY tblWorkTime.WorkEnded

Of course, this is an oversimplified example but it should illustrate how your recordsource can be sorted.

Regards,
~Chad

Edit: Gah, forgot it would have to be an OUTER JOIN to include all your entries. Trust SJ on these things. He wears the epalets of 'sagedness' with far more regality than I!

 

[Mile-O]

You could have an extra field in your category table called Sequence. You may have to set the sequence by hand for the first time. If you have 40 categories then number them from 40 down to 1; alphabetically first, to begin with.

In the combobox select the categories and order by the new Sequence field descending.

Each time something is selected in the combobox, use the AfterUpdate event to run an UPDATE query to change that single record to the Max value of the column+1. So, if you pick category 1 it will become 41 and, because the combo is sorting descending the picked category will move to the top.

 

[wop0703]

SJ, I like your idea alot, but not sure on how to update that single entry and not all of the entries and also, how to sort the table correctly.

 

[Mile-O]

SJ, I like your idea alot, but not sure on how to update that single entry and not all of the entries

Use an UPDATE query with the criteria being the primary key of the category.

and also, how to sort the table correctly.

In the query set the sort order of the relevant field.

 

[wop0703]

I have done that, but it is adding 40 to all of the categories' key value (Sequence). any more suggestions?