Using yes/no function

[Peter Simpson]

Is it possible to use a yes/no checkbox on an access form that will hide the corresponding data in the table?

What I want to do is tick the yes/no checkbox on the form when the task is complete which will automatically hide that row of data in the table so only incomplete tasks are visable.

[davegri]

Sure. You'll have to modify the form's recordsource SQL WHERE clause to something like
WHERE chkCompleted <> True

[Peter Simpson]

thank you for that but I have no idea what you just conveyed to me...sorry, I am a novice and what you said went over my head. Can you please simplify?

[ranman256]

your form that has the records is based on the source: table (or a query to show all records)
change the source to a query : qsActiveRecs

this query will filter out the checkmarked recrords

qsActiveRecs= select * from table where [checkbox]=false
set the form to this so users cannot see the checked records.

[Peter Simpson]

Ok so step 1 would be to create a query that is identical to the original table?

[aytee111]

In form design, go to the properties for the form and click on the three dots next to the Record Source property. That will bring up the query design window. Then use the * to select all records from the table, in addition bring in the check box and in the criteria line add =False.

Then, go to the checkbox field. In the AfterUpdate event, select [Event Procedure], click on the three dots and type this:
Me.Requery

That will repaint the screen and will get rid of the record that you have just set to True.

Note: Once you have set this to true the record will disappear. Usually there needs to be a way to reverse this entry in case it was done in error. You will need to decide how you want this to happen.
Also, a checkbox is not a good method of marking something complete, this is usually done with a date field so that there is more information than just a yes or no. Sometimes the user name is also kept track of. You need to think of the future and what you will need to know about completed transactions. This can be automated, as in when they click on the checkbox you set the date to Now().

[June7]

Do you have the Yes/No field in table that the checkbox is bound to? Or a date/time field for storing the date of completion?

[Peter Simpson]

You guys have given me a lot to think about, thank you.

You are right that I don't want the record to disappear forever. Ideally it should be moved into a sort or archive table (not sure that it is even possible though).

Currently, I have a query set up that only shows the records that are unresolved, which gives me the assurance that the resolved records are still available should I need to access them or relook at them. The problem is that in my SplitForm all the records (resolved and unresolved) are displayed. This can be quite cumbersome and would be a "nice to have" if only the unresolved records were displayed.

[davegri]

You can put 2 buttons on the form: "Show Resolved" and "Show Unresolved". The on-click event for each button would change the recordsource for the form to a query that shows the desired records.

[Peter Simpson]

devegri....you are my hero

Can you please help me with that? I have never written proper code using the on-click event

[Peter Simpson]

Ok, so I managed to set up the button for Show Unresolved....when I click it he query opens up perfectly. Can I have the query open up on a section of the form so that I can see both the form and the query on one page?

[davegri]

No, I don't think you want to do that. You don't want to show the actual query itself. You want the form to show the query results. Do this.
Create another query to show resolved.
Let's say your query names are qRes and qUnRes.
On your form you have the buttons named cmdRes and cmdUnRes.
For the on-click event for cmdRes

Me.recordsource = qRes

For the on-click event for cmdUnRes

me.recordsource = qUnRes

[Peter Simpson]

Ok...I did that but then all my fields become errors and unuseable

[Peter Simpson]

Original Form



Form after the "click"

[davegri]

Sorry, add quotes around the query name:
Me.Recordsource = "qRes"
Me.Recordsource = "qUnRes"