Full Version: Form Question with Checkbox
UtterAccess Forums > Microsoft® Access > Access Forms
BrianUrban
I have added a checkbox to my form. The goal of the checkbox is if checked it will place a date 90 days from the record date NOW() function to follow up on the status of that record. Once done I can run query on certain follow up dates.
The issue is if I uncheck the box the box value is at 0 and will not clear the date in the control source.
How can I get the control source where the date is being stored to be blank if I uncheck the checkbox?
Thanks for any suggestion.
Jack Cowley
You do not want to place the a date that is 90 days from the 'record date' in your table. You can calculate this date whenever you need it as the date will be wrong as soon as tomorrow... You only need a query based on your table and criteria in the RecordDate field to find all records that are 90 days from now or whenever....
th,
Jack
austenr
Why not do it all in code? Why do you want to store the date in the control source?
theDBguy
Agree with Jack, you don't need to store calculated values in your table. What is the purpose of unchecking the checkbox? Is it because the follow up status has been completed? If so, you might need to add a field to indicate completed follow ups. Then in your query, you can check for records with outstanding follow ups and then compute the follow up date from the record date.
Hope that helps...
BrianUrban
Sure Unbound would be fine, but the ideal is when I receive a record, I want to have a 90 day default follow up date, if I check the box, I may want to change the 90 days if record isn't completed to another date in the future, but defaut could be 90 then allow me to change date.
The purpose of unchecking it is yes when it's completed and the (UNBOUND Field, should be blank), but keeps the date.
Also if I check the box by mistake and checked I record I thought I wanted to follow up on but then changed my mind and decided I don't want to follow up on it I wanted the source to be blank when not checked.
Example how I want it to work...
X Unbound Field ( Record Input DATE + 90 days ) EG July 6, 2008
unchecked Unbound field (blank box).
If the record isn't completed in the 90 days I wanted the ability to change if not completed in the 90 days.
I hope that makes sense.
Brian
BrianUrban
In an upbound field, trying to use an IF statement but not working
To the effect:
If Chkbox=-1, then add 90 days to a bound field (Date_Rec), if Null or "0", then ""
This doesnt seem to work. I'm new to code, but was looking for suggestions. Thanks in advance.
B
theDBguy
I'm not sure if I fully understand your request, but based on your statement that you want flexibility, that is, being able to independently enter a new follow up date, then I thik you may have enough reason to store that date on your table. However, I would store it in a field called FollowUpCompleted, which will contain either the follow up date or the completion date. I will also have an extra boolean field to indicate if the record is in a completed status.
ssuming that when you "get the record" means that you are creating it, then I would make the Default Value of the FollowUpCompleted field to:
DateAdd("d", 90, Date())
In your form, checking the checkbox for the status would mean that the follow up is completed, so you would update the date in the field to today's date:
Me.FollowUpCompleted = Date()
In your queries, you can filter for incomplete follow ups if the checkbox is not checked, or completed followups if the checkbox is checked.
Hope that helps...
BrianUrban
My appologies for not being clear. Your suggestion works perfectly for new records, but not all records I want to follow up on. Some I just want to input into the dbase for reference and I was using the chkbox function to state if checked, then in the FollowupCompleted field would generate a date 90 days from when I received the record or input. I have a "Date_Rec" field which default value is Now() and works fine.
hope that makes it clearer
theDBguy
Just to make sure I follow you, this is what I think you have now:
. Date_Rec field with default value of Now() - Do you need the time too? If not, make the default Date().
2. FollowupCompleted field with default value of Date()
3. Yes/No field for the record status - checked means "completed"
4. Some existing records with empty FollowupCompleted fields that you want to change to 90 days past Date_Rec
If the above assumptions are correct, then I think you can do the following:
1. If all Date_Rec fields are not empty, then you can create an update query to change empty FollowupCompleted fields with 90 days past Date_Rec
2. If some Date_Rec fields are empty, you'll have to fill them up first before doing step 1 above. You can do this manually or with an update query as well.
3. Once you have all Date_Rec and FollowupCompleted dates filled in, then my previous suggestion of using the Status checkbox should take care of updating the FollowupCompleted date. Also, all new records are taken cared of with the Default value in the FollowupCompleted date.
4. To update the status of already completed records as of today, first create a Select query to get the records with FollowupCompleted date that is less than to today's date. If all of the records returned have been completed, you can change the Select query to an Update query to change the Status field to Yes/True/Checked. You will only have to this once.
5. If you want to change a specific record's follow up date to other than the default 90 days, you will have to manually input the new date into the FollowupCompleted field. Of course, you can use a Calendar control for that.
Hope that helps...
BrianUrban
BELOW IN RED CAPS is my response
. Date_Rec field with default value of Now() - Do you need the time too? If not, make the default Date(). NO, just the date
2. FollowupCompleted field with default value of Date() ONLY IF I USE CHKBOX SHOULD IT TRIGGER A FOLLOW UP DATE
3. Yes/No field for the record status - checked means "completed" NO IF CHECKED THEN I WANT TO FOLLOWUP ON RECORD
4. Some existing records with empty FollowupCompleted fields that you want to change to 90 days past Date_Rec
ONLY IF I CLICK THE CHKBOX SHOULD THE DEFAULTDATE() APPEAR + 90 DAYS
If the above assumptions are correct, then I think you can do the following:
1. If all Date_Rec fields are not empty, then you can create an update query to change empty FollowupCompleted fields with 90 days past Date_Rec
2. If some Date_Rec fields are empty, you'll have to fill them up first before doing step 1 above. You can do this manually or with an update query as well.
3. Once you have all Date_Rec and FollowupCompleted dates filled in, then my previous suggestion of using the Status checkbox should take care of updating the FollowupCompleted date. Also, all new records are taken cared of with the Default value in the FollowupCompleted date.
4. To update the status of already completed records as of today, first create a Select query to get the records with FollowupCompleted date that is less than to today's date. If all of the records returned have been completed, you can change the Select query to an Update query to change the Status field to Yes/True/Checked. You will only have to this once.
5. If you want to change a specific record's follow up date to other than the default 90 days, you will have to manually input the new date into the FollowupCompleted field. Of course, you can use a Calendar control for that.
Hope that helps...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.