Solved

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

Posted on 2013-01-21
5
428 Views
Last Modified: 2013-01-21
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
Comment
Question by:peispud
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38801078
Try this:
Currentdb.execute ("Update Table1 Set [Tag] = 'y' where [Transaction Type] = 1 and [Shape] = 'Round'"), dbFailOnError

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38801100
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
 

Author Comment

by:peispud
ID: 38801141
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38801196
Are you using a Lookup field for [Shape] in the table?
0
 

Author Comment

by:peispud
ID: 38801243
Yes.. [Shape] is a Lookup field.

I've tried Capricorn1's solution.

It works fine. Thank you both for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now