Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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?
0
peispud
Asked:
peispud
  • 2
  • 2
1 Solution
 
IrogSintaCommented:
Try this:
Currentdb.execute ("Update Table1 Set [Tag] = 'y' where [Transaction Type] = 1 and [Shape] = 'Round'"), dbFailOnError

Open in new window

0
 
Rey Obrero (Capricorn1)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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now