Solved Update Query to Select only filtered records (1 Viewer)

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
Hi, Plz note, My VBA is blocked by IT for security reason (Cybersecurity).
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I am trying to make checkbox to run Update Query to Select only filtered records and unselect only filtered records, I got this error :cautious:
I link the update query to form Filter, but I got this error message

Plz help me to fix it plz 🙏
Sample attached

1650314225417.png
1650314212829.png
 

Attachments

  • 17UpdateQ.accdb
    3.5 MB · Views: 211

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,358
This sounds familiar. Didn't we fix this before already. I'll have to check my previous posts.
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
This sounds familiar. Didn't we fix this before already. I'll have to check my previous posts.
Hi yes i have it we did by macro.

Now I want this to be by update query interface not by RunSql, i want to know how it is looks like in update query interface plz 🙏
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,358
Hi yes i have it we did by macro.

Now I want this to be by update query interface not by RunSql, i want to know how it is looks like in update query interface plz 🙏
If you saved the SQL as an UPDATE query, then you would use OpenQuery instead of RunSQL.
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
This sounds familiar. Didn't we fix this before already. I'll have to check my previous posts.
this code from the MS Community post, I have it from onedrive, I past it on update query SQL interface but it does not accepted
="UPDATE UserTQ SET UserTQ.Active = True WHERE " & [Forms]![MeetingListF].[Filter]
UPDATE UserTQ SET UserTQ.Active = True WHERE " & [Forms]![MeetingListF].[Filter]
="UPDATE UserT SET UserT.Active = True WHERE " & [Forms]![MeetingListF].[Filter]

I tried, but I could not convert it to Update Query Interface :( , I got errors message

plz help 🙏
1650322610366.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,358
Normally, you would assign things to a local variable, so you can examine what you're getting using a MsgBox.
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
Normally, you would assign things to a local variable, so you can examine what you're getting using a MsgBox.
I put Local Var on [CheckBoxFilteredRecords] is that what you mean?
plz help! 🙏
Sample attached

1650355576480.png
 

Attachments

  • 18UpdateQ.accdb
    3.5 MB · Views: 234

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:08
Joined
May 7, 2009
Messages
19,175
check if this is what you intend to do.
 

Attachments

  • 18UpdateQ.accdb
    3.5 MB · Views: 181

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
check if this is what you intend to do.
Thanks for replying!
No,
1. First If I make filter to the form for any fields Then,
2. If I click on [CheckBoxFilteredRecords]=True he is going to mark only [ActiveUser] by update Query Interface based on [Forms]![MeetingListF].[Filter]
3. If I click on [CheckBoxFilteredRecords]=False he is going to mark only [ActiveUser] by update Query Interface based on [Forms]![MeetingListF].[Filter]

Plz

for example I did this, but access dose not accpeted
1650368108028.png

1650367485104.png
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
check if this is what you intend to do.
I mean I want to convert this code that run by RunSQL Macro Action
="UPDATE UserTQ SET UserTQ.ActiveUser = True WHERE " & [Forms]![MeetingListF].[Filter]
to Run by Open Query Macro Action by Update Query Interface
Convery from RunSQL to OpenQuery Macro by Update Query
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
check if this is what you intend to do.
I made another try, I linked the [UserTQ] query with [FilteredActiveToYes] update query, but still he mark all records to Yes he did not mark filtered records to Yes based on the query filter 😞
see my try
1650370032364.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:08
Joined
May 7, 2009
Messages
19,175
check and see if this is a Yes.
 

Attachments

  • 18UpdateQ.accdb
    3.5 MB · Views: 202

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
check and see if this is a Yes.
Thanks, but it has small problem
for example UserName "a" and "b" has 8 Records I want him to checked/Unchecked only those ActiveUser.
I make some changes to make it more clear
I am sorry!

1650377512427.png
1650377379712.png
 

Attachments

  • 20UpdateQ.accdb
    3.5 MB · Views: 213

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,358
Now I want this to be by update query interface not by RunSql, i want to know how it is looks like in update query interface plz 🙏
In this case, I am not sure it's possible to replace RunSQL with a stored UPDATE query. Stored queries are not usually constructed dynamically without using VBA. I am not sure macros have the capability to modify a stored query at runtime. I hope I'm wrong, but I haven't found a way yet.
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
In this case, I am not sure it's possible to replace RunSQL with a stored UPDATE query. Stored queries are not usually constructed dynamically without using VBA. I am not sure macros have the capability to modify a stored query at runtime. I hope I'm wrong, but I haven't found a way yet.
Thanks for replying
I mean I want to do same this code in Update Query interface, plz see the attached picture

This Code is working good with RunSql
="UPDATE UserTQ SET UserTQ.ActiveUser = True WHERE " & [Forms]![MeetingListF].[Filter]

I do it same at Query interface it doesn't work I got error message, what do you think the problem is?
1650497372606.png

1650497699394.png
 

Sarah.M

Member
Local time
Today, 13:08
Joined
Oct 28, 2021
Messages
335
Thanks, but it has small problem
for example UserName "a" and "b" has 8 Records I want him to checked/Unchecked only those ActiveUser.
I make some changes to make it more clear
I am sorry!

View attachment 100018 View attachment 100017
@arnelgp Kindly, can you fix what you did, for example if I chose "a" and "b" I got 8 records, If I click it he will mark 21 Active user not 8, plz can you check that plz 🙏 😞

sample attached

1650525839867.png
 

Attachments

  • 21UpdateQ.accdb
    3.5 MB · Views: 192
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:08
Joined
May 7, 2009
Messages
19,175
sorry i tried, but cant make it work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,358
I do it same at Query interface it doesn't work I got error message, what do you think the problem is?
The problem is as I said earlier, stored queries cannot be composed dynamically. To do what you want, you'll have to create the query with all the possible filters already included. That's why RunSQL works and OpenQuery doesn't, in your case.
 

Users who are viewing this thread

Top Bottom