Priority Field

[linvall]

To give a little background on how it works, I have a vba that runs on a timer(every 10 mins) that refreshes the form (requery) to update and reorder any new ranks that are imputed. Within that vba, it orders changes the numbers in a 6-digit format and reorders them so that they can be placed within the correct order in the database. I also have a filler that replaces any blanks with NR (No Rank) as without this, the form would order all the blanks first, then order the rows with actual number values.

[Micron]

None of your tables have a primary key, which I'd advise to (almost) always have. Also,
- almost every field is text and you cannot reliably sort on text that looks like numbers as 11 comes before 2.
- table names suggest similarity (stuff for today, stuff finished, etc) yet don't have the same fields
- tables are probably not normalized. This is based on my experience with a work order system and may not be relevant to your process. For example, a scheduling table should only show scheduling data, including the work order ID from a work order table. You have all/most of the work order data in scheduling. In fact, you have no work order table as such. Another clue re not being normalized is the amount of empty fields in scheduling records.
Not sure if all that is because you quickly threw the db together for posting or if that's the real design. If the latter, you might be OK for the time being, but I think you'll find it gets harder to query, modify and report with this setup.

I'll also mention that I don't normally show the ID field in any form results. I found it useful for observing the re-ordering of the records and I just left it visible so as to be informative to anyone who might check out the db.

[linvall]

To answer so of these questions,

This data is being pulled from an ODBC that doesn't have a primary key, so having a primary key wouldn't work as new data is introduced and deleted everyday. These tables in this database is currently pulling from 10 different tables in the ODBC and placing the key information in one central place so there's no need to waste time searching for information. The reason why the ranking is in short text is due to the fact that when numbers are placed in a number field, access sorts the blanks first before the rankings with actual numbers, which means you have to scroll to the bottom to see the actual priorities. Is there a way i can change it to a number field and have them sort correctly?

[Micron]

so having a primary key wouldn't work

If you mean for re-ordering using the method I came up with, then I disagree completely.

placing the key information in one central place so there's no need to waste time searching for information.

Then this approach will only cause you grief or at least impose limitations on what you can do with it. Sorry but the comment makes no sense to me. A properly normalized structure doesn't make presentation of data more difficult. If anything, it makes it better. Seems to me that you should be updating existing tables based on ODBC data, not replacing everything each time you get the data. At least that has been my approach.

I suppose a number field that defaults to 0 would solve having non-prioritized events at the bottom of a list, but so would basing any form on a query instead of a table, which is my usual practice. You would find unaddressed items in a snap via query criteria.

I see that you have 'section manager' as priority value in your form in one record. Is that valid, or a mistake? The form priority field is free text, so I could just as easily typo HIHG as I could HIGH. You might find that you're overlooking records when such important data is free-form. A better approach might be a single form with datasheet. While combos provide a nice way of controlling such input, they are a bit problematic when used on continuous forms and if I recall, not functional in a datasheet at all.

EDIT:
I went ahead and tweaked the db that I posted earlier. Now it has the ability to move up one instead of just to the top. I think it's pointless to have the ability to move down as one would simply choose the one below and move it up. Also, there is a test to see if the listbox has header row or not and adjusts the list count accordingly rather than simply hard coding that in as I did in the prior version. I left the ID field visible as I find it easier to monitor the results against expectations, removed the module level variable and moved code off of the current event. That's about it, I guess.

EventSort.zip