Solved Update Query to Select only filtered records

[Sarah.M]

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
I link the update query to form Filter, but I got this error message

Plz help me to fix it plz
Sample attached

 

[theDBguy]

This sounds familiar. Didn't we fix this before already. I'll have to check my previous posts.

 

[Sarah.M]

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]

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]

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

 

[theDBguy]

Normally, you would assign things to a local variable, so you can examine what you're getting using a MsgBox.

 

[Sarah.M]

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

 

[arnelgp]

check if this is what you intend to do.

 

[Sarah.M]

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

 

[Sarah.M]

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]

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

 

[arnelgp]

check and see if this is a Yes.

 

[Sarah.M]

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!

 

[Sarah.M]

Please! I want to see it in Update Query Interface

 

[theDBguy]

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]

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?

 

[Sarah.M]

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 100018View 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

 

[arnelgp]

sorry i tried, but cant make it work.

 

[Sarah.M]

sorry i tried, but cant make it work.

Thanks indeed for your try

 

[theDBguy]

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.