Update Query (Select all Records or Select Only Filtered Records)

[Sarah.M]

Hi,
Plz note, My VBA is disabled in my pc, I can use Expression builder, Macros, Queries, tables, Forms and Reports only
I have a Form and a button,
this button has 2 jobs that:
1. if you click it, it will select all the checkboxes control in my form Or
2. if you filter the form, and click it, "it will only select the filtered records"

One of my friends helps me to do it and it is works 100% with only filtered textboxes, but once I filter the table and click the button to select all the filtered records Combo Boxes I got error message
most of my project are comboboxes

I put Sample in my Onedrive plz help me indeed!

Download my Sample
https://1drv.ms/f/s!ApJmppw4FPiTgfR-GXLRWp2Cpl9BNw



Upload your Solution
https://1drv.ms/f/s!ApJmppw4FPiTgfUAQ7dNWrE0GyH8cQ

[CJ_London]

not many people who respond here use macros (I don't). All I can tell you is the error is telling you it can't find that parameter and I suspect you need to refer to it in some other way. It may be you are using lookups in your table design which also causes complications

Many also won't download from unknown sources, much better to attach your file to the thread (you need to go to advanced). Ensure you compact and zip the file before uploading

[June7]

Well, I took a chance and tried download. My system blocked the accdb. Attach your file to post. Follow instructions at bottom of my post.

[davegri]

I was able to download it:

sarah.zip

[June7]

Didn't provide the error message you get. I get a popup prompt for [Lookup_UserNameF].[UserName]. The form Filter property has =([Lookup_UserNameF].[UserName]="Sarah"). This expression is created when using right click menu or ribbon filter because of combobox using alias (happens even if there is no lookup built in table).

TicketT has lookup field where the actual value stored is ID, not UserName, but this alone is not cause of issue - using the form Filter built as described above is the issue. The field in TicketT is UserNameF, not UserName. Cannot use the form Filter as is to run conditional UPDATE on TicketT. You need: UserNameF = 1. Change SQL in macro to not reference form Filter but to pull value from combobox:

="UPDATE TicketT SET TicketT.Download = True WHERE UserNameF = " & Forms![TicketF]![UserNameF]

If you don't want to see the UPDATE warnings, then use SetWarnings method to turn them off and on.

Then in form Close event, macro that sets Filter to "" so next time form is opened the Filter property is blank, otherwise, form will close with the last filter expression saved in Filter.

[Sarah.M]

Thanks for Help!

[Sarah.M]

Thanks, I will try it and let you know soon!

[davegri]

See if this overhaul meets expectations:

sarah-davegri-v01.zip

It was a real effort to keep all code as macros.

[Sarah.M]

See if this overhaul meets expectations:

sarah-davegri-v01.zip

It was a real effort to keep all code as macros.

Hi, you did great job!, but what I mean, If I make single filter or multi filter from the table and then I Click on the button that will select all the filtered records, on [Download] and then I will connected this [Download] with a report to show only the record that I have been filtered and selected by [Download] check boxes

[davegri]

Hi, you did great job!, but what I mean, If I make single filter or multi filter from the table and then I Click on the button that will select all the filtered records, on [Download] and then I will connected this [Download] with a report to show only the record that I have been filtered and selected by [Download] check boxes

You want a report like this?



================================================== ============

Or like this?

[Sarah.M]

Yes! I want picture number 2, If I make single filter or multi filter to the table then I click on the button and it will select [download] for the only filtered record and then I can print report for them for the only filtered and selected by dowload control check boxes
Wonderful! Thanks!
For example, I make filter from the table for today Ticket and then I filter by Sarah1 when I get result I click on the Button and then it will select all the Filtered result only, and then print report as PDF. So 1. I make Filter from Single or Multi, 2. I click on button to select all the result that got from the Filtered, 3. Print the Selected result by download check box
================================================== ============

Or like this?

[/QUOTE]

[davegri]

Tried my best to keep functionality in macros and cannot spend more hours on getting such simple functionality to work.
Here's the working DB with VBA, and it only took 15 minutes.

sarah-davegri-v02.zip

[June7]

Suggestion in post 5 did not resolve issue?

[davegri]

Suggestion in post 5 did not resolve issue?

Don't know it that was directed to me or OP, but June7, did YOU try to get that thing to work with macros using your suggestions? If so, please post here so that I can learn something. I found other issues that cropped up because of macros.

[Sarah.M]

Tried my best to keep functionality in macros and cannot spend more hours on getting such simple functionality to work.
Here's the working DB with VBA, and it only took 15 minutes.

sarah-davegri-v02.zip

Thank you very much!!!