Priority Field

[CJ_London]

I've been trying to figure out Ajax's method as it seems exactly what I'm looking for, but the query keeps asking for a newRanking value

show your form and the code you are using at the moment

[ArviLaanemets]

Is there anyway to work with just one column (Ranking) and not have any of the numbers excluded (would like number 1 to be the highest priority on the list).

With lesser value being higher priority there will be problems, as there will be lower limit. E.g. you have events with priorities 1, 2 and 3. You add a new event which you want to have priority 1, and you need to reset priorities for other events too. When 3 is highest priority instead, you simply set the priority of new event to 4.

A possible setup with my p.1.
You create a procedure, which queries active events in ascending order of priority into recordset. Then processes this recordset, and sets priorioties to values e.g. 10, 20, 30, etc., and after that updates priorities for all active events with values from recordset;
User can run this procedure at any time, or it is run automatically whenever the database is opened;
Whenever the user enters a new event, or wants to move any existing active event to new position between 2 existing events, the event priority entered must be between those 2 values (e.g. 25). The next new event may have the priority e.g. 27, etc. Whenever some priority range will be too crowded, the procedure must be run again to reset priorities for all active events with default spacing between them.

You can't do without EventStatus field (or without EventClosed date[time] field which will work same way).

[Micron]

I started to type this answer yesterday but cancelled because I thought the logic was already touched on by someone else. Upon review, I can't find that, so I'm either losing my mind (likely) or someone edited their prior post. Here's the gist of it:

You have 4 events in 1 to 4 priority order. You add a 5th event (which at first has no priority) and want to make it 1. Using vba, update all existing event priority numbers to +1, then make event 5 = 1.

Scenario 2: same events & order, but the new event is to be priority 2. Start with 2, update it and all higher events to existing priority +1. Leave priority 1 alone, make the new event 2.

Basically, you'd have to know the number of events and where the new event fits in. The code would probably be the same execution regardless but some validation might be desired in order to prevent attempts to set a priority less than 1 or greater than the number of events +1. Having said that, I find the notion of re-ordering a listbox interesting but at this point am not sure if the suggestion was based on doing so with a similar procedure.

I also wonder what the method/control is for the tab order that I mentioned. That might be the simplest and most efficient. I think I will try to find out.

EDIT: come to think of it, if priority is an integer with a default of 0, a new event can always come before the highest priority by saving the new record with the default, then updating all to +1. So I guess a procedure would/could be different based on a new event's placement. Being first should make it even simpler.

[CJ_London]

I can't find that

post #11

[Micron]

post #11

I did see your suggestions involving queries, but I thought there was more about code. Probably just that word stuck in my head and I thought a code solution was proposed although not detailed.

At this point, my thoughts are around a form with unbound controls so as to not haphazardly edit values belonging to an event and its details, plus a subform datasheet that is locked. If you select a record (yes, you still can if it's locked) the main form controls can load the details for editing. As they're not bound, it can only be done via edit command button, which would enable validation. Another button could reorganize the ranking based on user input, which as I said, could default to zero. Not sure if that's of any interest to OP.

[linvall]

Attached is what I have done for your Q1 on post #11.
Capture.zip

[CJ_London]

you need a form because you are processing two queries, you are just providing a query - and for future reference please copy/paste the sql text (go to sql window) for your query.

I've modified the code based on what I can glean from your images

Q1. to move existing rankings down

UPDATE [Priorities for today] SET [Ranking]=[Ranking]+1 WHERE [Ranking]>=[Enter New Rank]

just copy and paste into your sql window then go back to the query builder so you can see what it looks like

I can't do any more with the other queries because you have not supplied the information I requested, hopefully the above will get you on the right track

[linvall]

Wait, do i need to put these set of queries into a macro and then place them in the before update section of the form?

[CJ_London]

you need to execute them from a form, no idea which event you would use since I've no idea what your form looks like or how it is to be used.

By the sound of it you are allowing your users to access your table directly or through a query

[linvall]

They have access to the data through a subform.

[CJ_London]

so far, you have asked me to fix your car, but all you have told me is it has 4 wheels

for the final time, please provide a screenshot of your form, together with the sql of your form recordsources (main and sub) and any code you are currently using in either of these forms. Otherwise all I can go is provide what I have done already

[Micron]

No idea where this is at present, but I looked into how others might do this. The most complicated took 2 YouTube videos for the task, complete with redim'd arrays and much more. The simplest used the add method, which as I recall, can only be done with value lists. There were disconnected ADO recordsets, solutions with many lines to make one move and lots more.

Moving everything down 1 in priority (+1) and inserting a new event at position 1 is not difficult. Moving something from 5 to 1 in a 6 row list is another story. Only the rows that came after (before the move) need to be reset. I came up with what I think is fairly simple, given my propensity to complicate things. It is coded to

- move the last value to the top and renumber
- or move a 'middle' value to the top and renumber
- ensures an attempt to move the 1st value up does not execute

While I did add form buttons for moving items up or down by 1, there's no code for that in the attached version. For a moment here, I thought those would be redundant but realized that to move row 5 to row 4 in a 10 row list would take a lot of moving things around when the only option is to move a row to first place. What would probably have very limited use would be to move something to the last position given the original request. I realize the original request didn't include moving n to n-1, but heck, the OP can't think of everything! I might play with it some more if there's any encouragement to do so, otherwise y'all can read the code notes and follow the logic to make 1 row moves. I think the code is pretty concise considering what it does.

The file is small enough that zipping was not required. Not sure if an Access 2016 accdb is OK for some of the older versions. I can upload a 2003 version if anyone wants to take a look.
EventSort.accdb

[linvall]

Here is the information I think you are asking for (I hope). There isn't much, basically copied the table into a blank for and it outputted this subform for me (the record source is just a table).

g.zip

[linvall]

And thanks micron, I really like the way this is setup. I've never coded VBA myself (only used the macro converter) so I'll try to understand what each part of this code does.

[linvall]

Here is a very reduced version of what I'm working with, I've only included the parts that will allow the macro to run because of company policies.
h (4).zip