Update fields in filtered view (filter based on two tables)

Using Access 2010,  continuous form.  This form has read-write access to Table1 but only reads (displays fields) from Table2

I have enabled the user to dynamically filter the form with criteria from both tables. This works fine for a form filter.  Everything is great but.....

I have a field in Table1 called [Tag].   I would like the user to create a filtered result of all the records.  I've got that part done.  Next,  I would like to enable the user to click a button and cause all the records in the current view to have the [Tag] field set to "y".

My form filter looks like this.     Me.Filter = "[Transaction Type] = 1 and [Shape] = 'Round'"    

I tried the following command to update Table1.[Tag]

Currentdb.execute "Update Table1 Set Table1 & [Tag] = 'y' where [Transaction Type] = 1 and [Shape] = 'Round'"

As far as I can tell, this command fails because it refers to a field in Table2.
Solution?
peispudAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you update the filtered recordsets..after you apply the filter

with me.recordsetclone
     do until .eof
     .movefirst
        .edit
        !Tag="Y"
        .update
     .movenext
loop
end with


or just use

with me.recordset
     do until .eof
     .movefirst
        .edit
        !Tag="Y"
        .update
     .movenext
loop
end with
0
 
IrogSintaCommented:
Try this:
Currentdb.execute ("Update Table1 Set [Tag] = 'y' where [Transaction Type] = 1 and [Shape] = 'Round'"), dbFailOnError

Open in new window

0
 
peispudAuthor Commented:
I changed "table1" and [Tag]  and tried to follow your example.  The first one fails.  The second one works.  The difference between the two is  .... and [Shape] = 'Round'.

That field refers to table2  


CurrentDb.Execute ("Update [tblTransaction] Set [Invoice Tag] = 'y' where [Transaction Type] = 1 and [Shape] = 'Round'"), dbFailOnError

CurrentDb.Execute ("Update [tblTransaction] Set [Invoice Tag] = 'y' where [Transaction Type] = 1"), dbFailOnError
0
 
IrogSintaCommented:
Are you using a Lookup field for [Shape] in the table?
0
 
peispudAuthor Commented:
Yes.. [Shape] is a Lookup field.

I've tried Capricorn1's solution.

It works fine. Thank you both for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.