I have a form displaying data from a person on a waiting list. Criteria in underlying query is -1 in "waiting" field. One of the buttons on the form is "Send to Archive". The Archive button macro accurately puts a -1 in archive field but cannot change the waiting field to 0. It can make the archive change but cannot zero the waiting value because that is the underlying query criteria. How can I change that field so that person doesn't show up as waiting, either immediately or after form is closed?
Removing altering underlying criteria from a form (1 Viewer)
- Thread starter ellenr
- Start date
Can a record ever be Waiting and Archived? Are they mutually exclusive?
If so, should get rid of both those fields and replace them with a [Status] field which will hold the value 'Archived' or 'Waiting' (and whatever other status they could be in.
If so, should get rid of both those fields and replace them with a [Status] field which will hold the value 'Archived' or 'Waiting' (and whatever other status they could be in.
I cannot get rid of waiting and archived fields because other reports and actions are based on one or the other field's setting. The two are mutually exclusive. However, you have given me an idea how to accomplish this. I can set up another query that changes waiting to 0 if archive is -1. Thanks for your input.
- Local time
- Today, 03:29
- Joined
- Feb 19, 2002
- Messages
- 43,352
You can and should get rid of the separate fields. Rather than making another band aid, maybe you should fix the underlying problem
Now that you understand that with a mutually exclusive value, you use a SINGLE field, that's a step forward. Best practice is to shore up the foundation. Don't just slap some paint on it. The longer you put off fixing the problem, the more things you end up having to change.
Now that you understand that with a mutually exclusive value, you use a SINGLE field, that's a step forward. Best practice is to shore up the foundation. Don't just slap some paint on it. The longer you put off fixing the problem, the more things you end up having to change.You can and should get rid of the separate fields. Rather than making another band aid, maybe you should fix the underlying problem
I agree with you, and if I were starting over, I would certainly follow your advice. In this case, I did opt for the band-aid route. I set up an update query that runs automatically when the screen closes. Took care of the problem without digging into lots of other aspects of the program. I thank you for replying and being willing to help.
- Local time
- Today, 03:29
- Joined
- Feb 19, 2002
- Messages
- 43,352
You're welcome anyway.
PS, changing the value of the criteria field is not being prevented by the form. There is no reason to run an update query. Perhaps in your "save" button, you are not saving the record but instead are requerying the form and that is what is causing your problem. Requery in this case has yet another unwanted side effect when used incorrectly in that it would make the "saved" record disappear. Or maybe, that's exactly what you want to happen. When you close the form, the recordset it created also closes so the next time you open the form, that record would not be selected since the criteria would not be satisfied.
PS, changing the value of the criteria field is not being prevented by the form. There is no reason to run an update query. Perhaps in your "save" button, you are not saving the record but instead are requerying the form and that is what is causing your problem. Requery in this case has yet another unwanted side effect when used incorrectly in that it would make the "saved" record disappear. Or maybe, that's exactly what you want to happen. When you close the form, the recordset it created also closes so the next time you open the form, that record would not be selected since the criteria would not be satisfied.
