Solved Find & Replace: Update Query and Form (1 Viewer)

[Sarah.M]

Hi, Plz note, My VBA is disabled for security reason.
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
Hello,
1.I have a closet with a shelf and a folder. I want to do a search and replace, so that if I want to change some data or bulk of data.
2.if I chose User to filler the table I got Parameter I do not know why? if I type the name it is work but it does not work if I choose from drop down list
I tried but it does not work with me plz help
Sample Attached

 

[arnelgp]

check and test your replace form.
see also changes to your update query.

 

[Sarah.M]

Wonderful!

check and test your replace form.
see also changes to your update query.

Wonderful!
Thanks Indeed!

 

[Sarah.M]

check and test your replace form.
see also changes to your update query.

I'm curious for learning,
why my way SetFilter or ApplyFilter did not work? why he keeps show me [Parameter]
I used it before it works fine, but now I got this [Parameter]
Just for learning
Thanks!

 

[Sarah.M]

check and test your replace form.
see also changes to your update query.

You did great job @arnelgp , but I have small problem, if I click it does not changed
For example I want to move all "Jan" Files from Cabinet "1" to Cabinet "10"

Also, I want small thing that if I click on Replace/Transfer I got Msg box
If it ok show " Changed Successfully"
if my input in search area not correct "Please try again"

Plz help me!

 

[arnelgp]

here test it again.

 

[Sarah.M]

here test it again.

Hi, thank you for your reply, I got this error message
my Access is 2016 64bit I think Nz working with only 32bit
plz help!

 

[arnelgp]

i am using A2021 x64.
copy this query to your update query:

UPDATE Q1Find SET
Q1Find.UserNameFK = Nz([Forms]![F1]![RepUser],[UserNameFK]),
Q1Find.CabinNum = Nz([Forms]![F1]![RepCabin],[CabinNum]),
Q1Find.ShelfNum = Nz([Forms]![F1]![RepShelf],[ShelfNum]),
Q1Find.FolderNum = Nz([Forms]![F1]![RepFolder],[FolderNum])
WHERE (((Q1Find.UserNameFK) Like [Forms]![F1]![SerUser] & "*") AND
((Q1Find.CabinNum) Like [Forms]![F1]![SerCabin] & "*") AND
((Q1Find.ShelfNum) Like [Forms]![F1]![SerShelf] & "*") AND
((Q1Find.FolderNum) Like [Forms]![F1]![SerFolder] & "*"));

 

[Sarah.M]

i am using A2021 x64.
copy this query to your update query:

Hi, I did it ,but if I open the form F1, I got Nz error

 

[arnelgp]

ok, i don't know if you have the complete "reference" in your vba.
you might try replacing the Nz() with IIF(), example:

...
Q1Find.UserNameFK = IIF(IsNull([Forms]![F1]![RepUser]), [UserNameFK], [Forms]![F1]![RepUser]),
...