OK sorry to come back to this one again but I have made it work in Excel but can't even begin to think of the code for Access. Here is the Excel code which should explain what I am trying to do. It is a huge loop that goes through every single record until it reaches the end (bascially column...
I have a modal form which has a single record which is then linked to a sub-form on the same modal form. This all works fine and shows the relevant record and sub records but I want the modal form main record to change dependant on the record selected in the subform (which is basically order...
OK I think I have got it! Three fields Current, Proposed and New. The New field is created by looking at the current number (starting at 1) and seeing if there is anything in the corresponding priority and, if not it looks through all records in the Proposed field to see if there is another...
The best way to understand my confusion is to sit with a piece of paper and write the numbers 1 to 15 in one column and then in the next column write 1 adjacent to 7, 2 adjacent to 13 and 3 adjacent to 15 and then try to think how to number them all based on the new priorities for the three...
Thanks for the response. Yes there will be a field that is for the new number. Say field 1 has the numbers 1 - 100 and field 2 is used just to designate a new number (where relevant). So in field 1 the number might be 4 but in field 2 the new number would be 1. If that were the only change...
VBANET - I don't think your way (copied below) will work because they wouldn't start re-numbering after the point that priority 3 was changed to 1 but also above that point (because the current 1 and 2 will also move to 2 and 3.
Also it may not be one number change (which should be quite...
JDRAW - I don't need to log changes as I was only planning on letting one user do this and it will be a task they will undertake perhaps once a fortnight or so. I would imagine the database will need locking to other users to do this.
The database will have password controls and no other user will be able to access the priority fields so it will be a password protected single button that provides this facility that will only be available to one person.
Only 1 person will be able to set the priorities and it will be done following a single review meeting - so a one-off exercise possibly fortnightly or monthly.
I have a table which has 2 fields 1) Project_Priority_Number and 2) Previous_Priority_Number. If there were 100 records these would be numbered 1-100 in the order that the user originally sets the priority (this number is in addition to the record ID number). I have created a form with code...
Thank - I ran the query again (tried a few attempts at different code but got there in the end). The key code that I have just put in that made the whole thing work was to Refresh the subform. That then updated the control with the query results. Thanks so much for your help.
OK - I will try to explain better.
My main form has a customer number amongst other fields.
The sub form populates with the further info pertaining to that customer number (from a different table which is linked to the main by Customer Number) and the user is allowed to add a record...