update query...HELP!

[kiermarie]

I am working on a query which will reassign a priority number to each record once a check box representing completion of a project for that contact is marked.

I'm having trouble designing the update query. Ideally, what I want is for the "Priority Number" field to have a null value when the "Completed" check box is checked. Additionally, I want the remaining records to be renumbered in the "Priority Number" field so that there are no numbers missing in the sequence and the field maintains referential integrity. (Frequently the project is completed out of order of priority numbers because emergencies arise.) Is this possible?

My current query is designed to subtract one (1) from the priority number every time I run the query. (The query is run by an event procedure triggered by updating the "completed" check box.) This is not working because numbers are skipped in the priority number sequence. Additionally, I have multiple New Priority Number-1's. I tried to attach the query, but could only figure out how to attach the whole database.

Thank you for helping!

 

[eforce]

Try Conditional Statements

I'm still a newbie and haven't provided the exact code but how bout:

something like - [finishedjobscheckbox] = true then [priority number] = null

then have it redo what ever query you had it originally do to assign the priority numbers in the first place. So whenever the checkbox is checked (meaning, "TRUE") the "Priority Number" will be "Null" (Meaning. blank) and you should be good to go.

Hope this helps

E-Force
"Delta's Last Resort"