Clearing Sub Form

[eagle670]

Is there a way to clear a sub form of selections that I made from a previous report? The way it is now I have to go in to the sub form and manually click each item I had previously selected be for I can do a new report with different selections.

[June7]

You are using a Yes/No field in table to allow selection of records? You want to just clear the checkbox for all records? VBA code:

CurrentDb.Execute "UPDATE tablename SET fieldname=False"

[John_G]

Hi -

One way would be to put a command button on the sub-form to reset all the sub-form fields when clicked (using VBA).

If the sub-form is bound to a table or query, you could just delete the entire record, and go to a new one.

That is a bit of a misuse of a sub-form, though - could you not just put the selection criteria on the main form, in unbound controls?

John

[eagle670]

So if I create a command button and then copy and paste the above VBA to the OnClick event procedure, then that should work right?

[June7]

Try it and see. Use your table and field names.

[eagle670]

I don't know how to do that John, can you direct me? I created a command button, but I couldn't get it to work.

[June7]

Select [Event Procedure] in the OnClick event property, click the ellipsis (...) to open VBA editor, type code.

[John_G]

Is the form bound to a table or query, i.e. does it have a table/query as the recordsource?

The code to clear (reset) the form would be something like this:

for text fields:

Me![Control1] = ""
me![Control2] = ""
etc.

and for numeric fields:

Me![Control3] = 0
Me![Control] = 0
etc.

Replace Control1, Control2, .... with the actual control names on your form.

[eagle670]

Yes it is tied to one table called "Sub Category"

[eagle670]

Do I replace the "CurrentDb" with the name of the query or table?

[June7]

No, replace "tablename" and "fieldname" with your actual names.

[eagle670]

Here is what I wrote, but does not work

CurrentDb.Execute "UPDATE Sub Category SET Click=False"

Should I put True or False in the criteria line of the table. Right now it says True.

[June7]

Names with spaces and special characters/punctuation must be enclosed in []: [Sub Category]

Advise to avoid those in naming convention (underscore is exception).

Criteria should not be needed.

[eagle670]

I hate to keep bother you, but I must be missing something, because it still doesn't clear the form.

Here is my code - CurrentDb.Execute "UPDATE[Sub Category]SET[Check]=False"

This was placed in the event procedure VBA area. When I click on the button I get an error. See attached imaged.

[June7]

I advised no spaces in names, sql statements need spaces to define distinct words.

CurrentDb.Execute "UPDATE [Sub Category] SET [Check]=False"