So I have a Access 2007 split form that a user can enter info into. I have a checkbox only visible in the datasheet named "Print". Then the user selects a report from the dropdown and the info on the report is that of the selected record(s). I have a button called "Clear that clears all the checkboxes so the user can create another report. The code for the button is as follows,
Private Sub Clear_Click()
DoCmd.RunSQL "UPDATE tblTenants SET Print = No;"
Me.Requery
End Sub
Now of course the user wants to be able to filter out a bunch of records, which they can do via the Access filters, but if they are left with 20 records they want a button that will select all the print checkboxes so they don't have to manually check each one. The problem I am having is if I take the above code and change No to Yes, all the records of the database are checked. How can I just select the records that are left after filtering?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Ok, let's say they are filtering off of a field such as Company.
How about doing something like:
Private Sub Clear_Click()
DoCmd.RunSQL "UPDATE tblTenants WHERE Company = " & Company & "SET Print = No;"
Me.Requery
End Sub
This should create a prompt asking for what company they are running the report on and pull only those companies in the SQL results, and having their Print = No. You can add additional fields as needed using similar concept.
If your "Filtering" system actually sets the filter for the form, then you can toggle ON all the "Print" checkboxes by interrogating the Filter Property of the form.
Like this:
DoCmd.RunSQL "UPDATE tblTenants SET Print = True WHERE " & Me.Filter
Here is a sample
On the form, use the filter by Selection utility to filter all Dramas, then click the "Select All Filtered" button.
This will check On the "Print" CheckBox for all Dramas.
(You can open the raw table to verify this.)
;-)
JeffCoachman
Business Accounts
Answer for Membership
by: dpavlickPosted on 2009-10-08 at 11:10:01ID: 25528311
Don't let the User input a Filter as it "hides" records but does not reduce the real list. Create a way for them to perform a Search of the data without having to use Filters by using queries instead. This will remove the "hidden" records that the Filter doesn't actually remove. Then run your Update based off of that query.
Create a text box with a "Search" button next to it. When the User inputs data into the text box and clicks Search they initiate the running of your query and the data gets displayed in a Subform. It is recommended the checkbox for Print yes/no is available so the User can make adjustments as needed. You can add a Select All checkbox in the subform's header that if checked performs a loop that checks all the records and if unchecked it loops through and unchecks all the records, giving them more options to choose from. You can also have the query be based off multiple text boxes in the main form allowing the user to further limit the list as needed.