Update Query based on Current Recordset

[NEWER USER]

I have a continuous form with filtered records. I want to run an update
query on a field in a table based on the current filtered records on the
form. What code would I use to accomplish this task? Any help appreciated
in getting me started.

[Gina Whipp]

NEWER USER,

Without a wee bit more information, like what are you trying to update,
field names, table names, do you want this attached to a button or an event,
etc... The best I can do is...

UPDATE SomeTable SET SomeTable.SomeField= WhateverValue
WHERE (((SomeTable.SomeOtherFied)=WhateverValue));

Of course, the above would be an UPDATE query not *code* as you indicated
but it would work.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message
news:1EC82C91-00D2-42C3...@microsoft.com...

[NEWER USER]

Thanks for responding - This is what I have and getting an eror message -
Invalid Operation

Private Sub Update_Click()
On Error GoTo Update_Click_Err

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "UPDATE tblData SET tblData.ThreatStatus = Closed WHERE
(((tblData.ID) = Me.ID))"
Set rst = db.OpenRecordset(strSQL)
DoCmd.RunSQL strSQL

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.description
Resume Update_Click_Exit

End Sub

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

The first thing I spot is Me.ID which should

UPDATE tblData SET tblData.ThreatStatus = Closed WHERE (((tblData.ID)

=[Forms]![YourFormName]![ID]))

I also would not put all all the *extra lines*... (Less typing)

Private Sub Update_Click()
On Error GoTo Update_Click_Err

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblData SET tblData.ThreatStatus = Closed WHERE
(((tblData.ID) =[Forms]![YourFormName]![ID]))"
DoCmd.SetWarnings True

Update_Click_Exit:
Exit Sub

Update_Click_Err:
MsgBox Err.description
Resume Update_Click_Exit

End Sub

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:13A3DF43-73C1-4D00...@microsoft.com...

[NEWER USER]

I added single quotes around the word 'Closed' and only one record gets
updated. I want to update ALL filtered records showing on the continuous
form. Any ideas. Thanks for your help.

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

I answered in your new posting as well... Remove the WHERE portion...

strSql = "UPDATE tblData SET tblData.threatStatus = 'Closed'"

However, I just noticed is your continuous form based on a query or tblData?
Because the above will do all in the table.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:6D92E349-6135-4248...@microsoft.com...

[NEWER USER]

The form is based on a query that opens and displays specific records. I
then filter the records to get even more specific records. This FINAL
recordset are the only records I want to update.

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

Then where it say tblData switch that to the query name that your form is
based on BUT only do so if those fields are in the query. If not then going
to have use a WHERE clause. I would also TEST this on a copy to be sure you
get the desired results.

strSql = "UPDATE YourQueryName SET YourQueryName.threatStatus = 'Closed'"

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:442B9514-AE25-495E...@microsoft.com...

[NEWER USER]

I got lost somewhere.

My form opens with 2000 records. I filter the form where I have 50 records
showing. I want to update the table for the 50 records only, not the 2000.
I could manually use the Advanced Fliter, Save as Query (50 records), and
then run an Update query to update the table. How do I code this new
Recordset (50 records) in my WHERE clause? tblData.ID =
Forms.frnManagebyThreat!ID will only update the current record and not all
50. Any ideas?

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

What do you filter your form on? What field name on your form?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:FFD980BB-6A58-420A...@microsoft.com...

[NEWER USER]

All fields can be filtered to arrive at the final set of records. Your
building a custom filter each time the form opens. Using code, how do we
save this final filter as a query to use in the update query?

Examples of field names Threat, Priority, Host, Name, Port, Protocol

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

That is why I suggested replacing tblData with the name of the query that is
the RecordSource of your form. That query should be filtered and hence you
will be running your Update query against that filtered queries records.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:748A130B-DE14-41A4...@microsoft.com...

[NEWER USER]

Not quite there.

I replace tblData with query name

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE qryVulnerabilities SET qryVulnerabilities.ThreatStatus
= 'Closed' WHERE (((qryVulnerabilities.ID) = Forms!frmManagebyThreat!ID))"
DoCmd.SetWarnings True

1. The above updates Current record (1 only).
2. I removed the WHERE string and all records in table get updated

I opened the Properties dialog of the form and looked at the Filter row.
All filters applied are showing in this row. Allow Filters set to Yes. I
looked at the SQL view of the query and only the original text is there and
not the additional filter text. ????

It seems the query is not picking up the filters when running the SQL.
STUMPED!!

'Filter text below
((((((tblData.ThreatStatus) Is Null Or (tblData.ThreatStatus)<>"Closed") AND
((tblThreatLevel.Threat)="High")))) AND ((qryVulnerabilities.Priority=2)))
AND ((qryVulnerabilities.ip="172.17.63.76"))

"Gina Whipp" wrote:

> .
>

[Gina Whipp]

NEWER USER,

Okay, I think I wasn't thinking straight. You want to UPDATE based on
values in your form fields. Since the RecordSource is not controlling that,
let's make the UPDATE query control that.

'UNTESTED MAKE A BACK-UP

DoCmd.SetWarnings False

UPDATE tblData SET tblData.threatStatus = Closed

WHERE (((tblData.threatStatus)=[Forms]![frmManagebyThreat]![threatstatus])
AND ((tblData.priority)=[Forms]![frmManagebyThreat]![priority]) AND
((tblData.ip)=[Forms]![frmManagebyThreat]![ip]) AND
((tblData.threat)=[Forms]![frmManagebyThreat]![threat]));
DoCmd.SetWarnings False

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:BE9F881F-C80F-4B51...@microsoft.com...

[NEWER USER]

I understand what you are saying. However, each time the user opens the
form, a different set of filters will be applied prior to running the update
query. I somehow need to save the query with filters as a new query and link
the new query to the table in an update query on the ID field of the form and
in the table. How might I save the query with filters as a new query(current
filtered records; same query name each time) so it is going to update ONLY
the filtered records?

[NEWER USER]

I replied earlier, but don't think it posted. Once again.. I understand what
you are saying. However, each time the form opens a user will apply
different filters to the form. I think I need to save the filtered form as a
new query and use that query linked on the ID fields in the form and and in
the table for the update. How would I save this query using the same name
each time so the update will work every time?

[Gina Whipp]

NEWER USER

You know, I really need more sleep or coffee... I should have told you
about this right off that bat... Take a look at....
http://allenbrowne.com/ser-62.html Don't be fooled by the name Search
because in essence it filters off enteries entered into fields. Then you
can base your update query on this.

You shouldn't have to save the query/RecordSource to run this Update query.
Do you have a database that doesn't have personal data you can eMail to me?
I can look at it later today. I have to run out and then I have something I
have to do for another poster and you will be next!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:35955A56-19B8-4182...@microsoft.com...

[NEWER USER]

I attached a sample .mdb sent to your AOL address.

[Gina Whipp]

NEWER USER,

I got it and I am looking at it now... not sure what happened with the
link, you might want to try again because it does work.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:12574F82-B3E8-4DE1...@microsoft.com...

[Gina Whipp]

NEWER USER,

See if this is what you want... Just so you know, the reason my Update
query did not work is because on the form threatstatus is actuall threat
status and threat is not in tblData so that table needed to be added to the
query. I am of course assuming you are using the Filter buttons above as
there is no way to filter on your form. So the records will filter using
those buttons but they actually on see the value in the first record. If it
were me I would incorporate Allen Browne's method of filtering and run your
update query against that but what I provided does work.

As a side note, you are using quite a few Reserved Words as field names,
this will cause you problems as Access really doesn't like it, especially
Access 2007. To see the complete list see...
http://allenbrowne.com/AppIssueBadWord.html

Good Luck,

Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:12574F82-B3E8-4DE1...@microsoft.com...

[Gina Whipp]

NEWER USER

I just returned your sample and it should give you what you want the way you
want it... Let me know!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:12574F82-B3E8-4DE1...@microsoft.com...

[Gina Whipp]

For all those interested... This is what I used...

Dim strWhere As String
strWhere = "WHERE " & Me.Filter

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE qryVulnerabilities " & _
"SET qryVulnerabilities.ThreatStatus = 'Closed'" & strWhere
DoCmd.SetWarnings True

...it allows the update to run only on the records filtered by those
selected. (Filter by Selection)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Gina Whipp" <NotInt...@InViruses.com> wrote in message
news:OVydj9mj...@TK2MSFTNGP05.phx.gbl...

[NEWER USER]

You nailed it with the Me.Filter as the WHERE string. Thank you so much for
your persistence.

[Gina Whipp]

No problem... Glad it's working for you...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"NEWER USER" <NEWE...@discussions.microsoft.com> wrote in message

news:F4E1AC3C-DDE9-46E4...@microsoft.com...

[cenzo]

I've read this thread and have a similar problem.
I have a form which records are randomly selected from a table in the Form_Load event.
I want then to update a field in the main table, counting which records were previously selected. I have just to increment a field, but cannot select the right records.
If i try the following code, all the records will be updated.
Can someone help me?

Private Sub Form_Load()
Me.RecordSource = "SELECT TOP 60 * FROM ingegneri ORDER BY Rnd(int(Now*Num)-Now*Num);"

DoCmd.RunSQL "UPDATE [Forms]![sim_ingegneri] SET [Forms]![sim_ingegneri]![Frequenza] = [Forms]![sim_ingegneri]![Frequenza] + 1;"

End Sub

> On Monday, January 04, 2010 12:01 AM NEWER USER wrote:

> I have a continuous form with filtered records. I want to run an update
> query on a field in a table based on the current filtered records on the
> form. What code would I use to accomplish this task? Any help appreciated
> in getting me started.

>> On Monday, January 04, 2010 12:09 AM Gina Whipp wrote:

>> NEWER USER,
>>

>> Without a wee bit more information, like what are you trying to update,
>> field names, table names, do you want this attached to a button or an event,
>> etc... The best I can do is...
>>
>> UPDATE SomeTable SET SomeTable.SomeField= WhateverValue
>> WHERE (((SomeTable.SomeOtherFied)=WhateverValue));
>>
>> Of course, the above would be an UPDATE query not *code* as you indicated
>> but it would work.
>>

>> --
>> Gina Whipp
>> 2010 Microsoft MVP (Access)
>>
>> "I feel I have been denied critical, need to know, information!" - Tremors
>> II
>>
>> http://www.regina-whipp.com/index_files/TipList.htm

>>> On Monday, January 04, 2010 1:51 PM NEWER USER wrote:

>>> Thanks for responding - This is what I have and getting an eror message -
>>> Invalid Operation
>>>

>>> Private Sub Update_Click()
>>> On Error GoTo Update_Click_Err
>>>

>>> Dim db As DAO.Database
>>> Dim rst As DAO.Recordset
>>> Dim strSQL As String
>>>
>>> Set db = CurrentDb
>>>

>>> strSQL = "UPDATE tblData SET tblData.ThreatStatus = Closed WHERE

>>> (((tblData.ID) = Me.ID))"
>>> Set rst = db.OpenRecordset(strSQL)
>>> DoCmd.RunSQL strSQL
>>>

>>> Update_Click_Exit:
>>> Exit Sub
>>>
>>> Update_Click_Err:
>>> MsgBox Err.description
>>> Resume Update_Click_Exit
>>>
>>> End Sub
>>>

>>> "Gina Whipp" wrote:

>>>> On Monday, January 04, 2010 2:15 PM Gina Whipp wrote:

>>>> NEWER USER,
>>>>

>>>> The first thing I spot is Me.ID which should
>>>> UPDATE tblData SET tblData.ThreatStatus = Closed WHERE (((tblData.ID)
>>>> =[Forms]![YourFormName]![ID]))
>>>>
>>>> I also would not put all all the *extra lines*... (Less typing)
>>>>
>>>> Private Sub Update_Click()
>>>> On Error GoTo Update_Click_Err
>>>>
>>>> DoCmd.SetWarnings False
>>>> DoCmd.RunSQL "UPDATE tblData SET tblData.ThreatStatus = Closed WHERE
>>>> (((tblData.ID) =[Forms]![YourFormName]![ID]))"
>>>> DoCmd.SetWarnings True
>>>>
>>>> Update_Click_Exit:
>>>> Exit Sub
>>>>
>>>> Update_Click_Err:
>>>> MsgBox Err.description
>>>> Resume Update_Click_Exit
>>>>
>>>> End Sub
>>>>
>>>>

>>>> --
>>>> Gina Whipp
>>>> 2010 Microsoft MVP (Access)
>>>>
>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>> II
>>>>
>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>> On Monday, January 04, 2010 3:31 PM NEWER USER wrote:

>>>>> I added single quotes around the word 'Closed' and only one record gets

>>>>> updated. I want to update ALL filtered records showing on the continuous
>>>>> form. Any ideas. Thanks for your help.
>>>>>
>>>>> "Gina Whipp" wrote:

>>>>>> On Monday, January 04, 2010 3:40 PM Gina Whipp wrote:

>>>>>> NEWER USER,
>>>>>>
>>>>>> I answered in your new posting as well... Remove the WHERE portion...
>>>>>>
>>>>>> strSql = "UPDATE tblData SET tblData.threatStatus = 'Closed'"
>>>>>>
>>>>>> However, I just noticed is your continuous form based on a query or tblData?
>>>>>> Because the above will do all in the table.
>>>>>>
>>>>>> --
>>>>>> Gina Whipp
>>>>>> 2010 Microsoft MVP (Access)
>>>>>>
>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>> II
>>>>>>
>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>> On Monday, January 04, 2010 4:21 PM NEWER USER wrote:

>>>>>>> The form is based on a query that opens and displays specific records. I
>>>>>>> then filter the records to get even more specific records. This FINAL
>>>>>>> recordset are the only records I want to update.
>>>>>>>
>>>>>>> "Gina Whipp" wrote:

>>>>>>>> On Monday, January 04, 2010 4:57 PM Gina Whipp wrote:

>>>>>>>> NEWER USER,
>>>>>>>>
>>>>>>>> Then where it say tblData switch that to the query name that your form is
>>>>>>>> based on BUT only do so if those fields are in the query. If not then going
>>>>>>>> to have use a WHERE clause. I would also TEST this on a copy to be sure you
>>>>>>>> get the desired results.
>>>>>>>>
>>>>>>>> strSql = "UPDATE YourQueryName SET YourQueryName.threatStatus = 'Closed'"
>>>>>>>>
>>>>>>>> --
>>>>>>>> Gina Whipp
>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>
>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>> II
>>>>>>>>
>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>> On Monday, January 04, 2010 5:54 PM NEWER USER wrote:

>>>>>>>>> I got lost somewhere.
>>>>>>>>>
>>>>>>>>> My form opens with 2000 records. I filter the form where I have 50 records
>>>>>>>>> showing. I want to update the table for the 50 records only, not the 2000.
>>>>>>>>> I could manually use the Advanced Fliter, Save as Query (50 records), and
>>>>>>>>> then run an Update query to update the table. How do I code this new
>>>>>>>>> Recordset (50 records) in my WHERE clause? tblData.ID =
>>>>>>>>> Forms.frnManagebyThreat!ID will only update the current record and not all
>>>>>>>>> 50. Any ideas?
>>>>>>>>>
>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>> On Monday, January 04, 2010 6:03 PM Gina Whipp wrote:

>>>>>>>>>> NEWER USER,
>>>>>>>>>>
>>>>>>>>>> What do you filter your form on? What field name on your form?
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Gina Whipp
>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>
>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>> II
>>>>>>>>>>
>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>> On Monday, January 04, 2010 6:56 PM NEWER USER wrote:

>>>>>>>>>>> All fields can be filtered to arrive at the final set of records. Your
>>>>>>>>>>> building a custom filter each time the form opens. Using code, how do we
>>>>>>>>>>> save this final filter as a query to use in the update query?
>>>>>>>>>>>
>>>>>>>>>>> Examples of field names Threat, Priority, Host, Name, Port, Protocol
>>>>>>>>>>>
>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>> On Monday, January 04, 2010 8:36 PM Gina Whipp wrote:

>>>>>>>>>>>> NEWER USER,
>>>>>>>>>>>>
>>>>>>>>>>>> That is why I suggested replacing tblData with the name of the query that is
>>>>>>>>>>>> the RecordSource of your form. That query should be filtered and hence you
>>>>>>>>>>>> will be running your Update query against that filtered queries records.
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>
>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>> II
>>>>>>>>>>>>
>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>> On Monday, January 04, 2010 10:53 PM NEWER USER wrote:

>>>>>>>>>>>>> Not quite there.
>>>>>>>>>>>>>
>>>>>>>>>>>>> I replace tblData with query name
>>>>>>>>>>>>>
>>>>>>>>>>>>> DoCmd.SetWarnings False
>>>>>>>>>>>>> DoCmd.RunSQL "UPDATE qryVulnerabilities SET qryVulnerabilities.ThreatStatus
>>>>>>>>>>>>> = 'Closed' WHERE (((qryVulnerabilities.ID) = Forms!frmManagebyThreat!ID))"
>>>>>>>>>>>>> DoCmd.SetWarnings True
>>>>>>>>>>>>>
>>>>>>>>>>>>> 1. The above updates Current record (1 only).
>>>>>>>>>>>>> 2. I removed the WHERE string and all records in table get updated
>>>>>>>>>>>>>
>>>>>>>>>>>>> I opened the Properties dialog of the form and looked at the Filter row.
>>>>>>>>>>>>> All filters applied are showing in this row. Allow Filters set to Yes. I
>>>>>>>>>>>>> looked at the SQL view of the query and only the original text is there and
>>>>>>>>>>>>> not the additional filter text. ????
>>>>>>>>>>>>>
>>>>>>>>>>>>> It seems the query is not picking up the filters when running the SQL.
>>>>>>>>>>>>> STUMPED!!
>>>>>>>>>>>>>
>>>>>>>>>>>>> 'Filter text below
>>>>>>>>>>>>> ((((((tblData.ThreatStatus) Is Null Or (tblData.ThreatStatus)<>"Closed") AND
>>>>>>>>>>>>> ((tblThreatLevel.Threat)="High")))) AND ((qryVulnerabilities.Priority=2)))
>>>>>>>>>>>>> AND ((qryVulnerabilities.ip="172.17.63.76"))
>>>>>>>>>>>>>
>>>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>>>> On Monday, January 04, 2010 11:23 PM Gina Whipp wrote:

>>>>>>>>>>>>>> NEWER USER,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Okay, I think I was not thinking straight. You want to UPDATE based on

>>>>>>>>>>>>>> values in your form fields. Since the RecordSource is not controlling that,

>>>>>>>>>>>>>> let us make the UPDATE query control that.

>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 'UNTESTED MAKE A BACK-UP
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> DoCmd.SetWarnings False
>>>>>>>>>>>>>> UPDATE tblData SET tblData.threatStatus = Closed
>>>>>>>>>>>>>> WHERE (((tblData.threatStatus)=[Forms]![frmManagebyThreat]![threatstatus])
>>>>>>>>>>>>>> AND ((tblData.priority)=[Forms]![frmManagebyThreat]![priority]) AND
>>>>>>>>>>>>>> ((tblData.ip)=[Forms]![frmManagebyThreat]![ip]) AND
>>>>>>>>>>>>>> ((tblData.threat)=[Forms]![frmManagebyThreat]![threat]));
>>>>>>>>>>>>>> DoCmd.SetWarnings False
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 9:56 AM NEWER USER wrote:

>>>>>>>>>>>>>>> I understand what you are saying. However, each time the user opens the
>>>>>>>>>>>>>>> form, a different set of filters will be applied prior to running the update
>>>>>>>>>>>>>>> query. I somehow need to save the query with filters as a new query and link
>>>>>>>>>>>>>>> the new query to the table in an update query on the ID field of the form and
>>>>>>>>>>>>>>> in the table. How might I save the query with filters as a new query(current
>>>>>>>>>>>>>>> filtered records; same query name each time) so it is going to update ONLY
>>>>>>>>>>>>>>> the filtered records?
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 10:18 AM NEWER USER wrote:

>>>>>>>>>>>>>>>> I replied earlier, but do not think it posted. Once again.. I understand what

>>>>>>>>>>>>>>>> you are saying. However, each time the form opens a user will apply
>>>>>>>>>>>>>>>> different filters to the form. I think I need to save the filtered form as a
>>>>>>>>>>>>>>>> new query and use that query linked on the ID fields in the form and and in
>>>>>>>>>>>>>>>> the table for the update. How would I save this query using the same name
>>>>>>>>>>>>>>>> each time so the update will work every time?
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 10:48 AM Gina Whipp wrote:

>>>>>>>>>>>>>>>>> NEWER USER
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> You know, I really need more sleep or coffee... I should have told you
>>>>>>>>>>>>>>>>> about this right off that bat... Take a look at....
>>>>>>>>>>>>>>>>> http://allenbrowne.com/ser-62.html Don't be fooled by the name Search
>>>>>>>>>>>>>>>>> because in essence it filters off enteries entered into fields. Then you
>>>>>>>>>>>>>>>>> can base your update query on this.
>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>> You should not have to save the query/RecordSource to run this Update query.
>>>>>>>>>>>>>>>>> Do you have a database that does not have personal data you can eMail to me?

>>>>>>>>>>>>>>>>> I can look at it later today. I have to run out and then I have something I
>>>>>>>>>>>>>>>>> have to do for another poster and you will be next!
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 6:31 PM NEWER USER wrote:

>>>>>>>>>>>>>>>>>> I attached a sample .mdb sent to your AOL address.
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 7:05 PM Gina Whipp wrote:

>>>>>>>>>>>>>>>>>>> NEWER USER,
>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>> I got it and I am looking at it now... not sure what happened with the
>>>>>>>>>>>>>>>>>>> link, you might want to try again because it does work.
>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 7:33 PM Gina Whipp wrote:

>>>>>>>>>>>>>>>>>>>> NEWER USER,
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> See if this is what you want... Just so you know, the reason my Update
>>>>>>>>>>>>>>>>>>>> query did not work is because on the form threatstatus is actuall threat
>>>>>>>>>>>>>>>>>>>> status and threat is not in tblData so that table needed to be added to the
>>>>>>>>>>>>>>>>>>>> query. I am of course assuming you are using the Filter buttons above as
>>>>>>>>>>>>>>>>>>>> there is no way to filter on your form. So the records will filter using
>>>>>>>>>>>>>>>>>>>> those buttons but they actually on see the value in the first record. If it
>>>>>>>>>>>>>>>>>>>> were me I would incorporate Allen Browne's method of filtering and run your
>>>>>>>>>>>>>>>>>>>> update query against that but what I provided does work.
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> As a side note, you are using quite a few Reserved Words as field names,

>>>>>>>>>>>>>>>>>>>> this will cause you problems as Access really does not like it, especially

>>>>>>>>>>>>>>>>>>>> Access 2007. To see the complete list see...
>>>>>>>>>>>>>>>>>>>> http://allenbrowne.com/AppIssueBadWord.html
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Good Luck,
>>>>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 8:27 PM Gina Whipp wrote:

>>>>>>>>>>>>>>>>>>>>> NEWER USER
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>> I just returned your sample and it should give you what you want the way you

>>>>>>>>>>>>>>>>>>>>> want it... Let me know!

>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 8:31 PM Gina Whipp wrote:

>>>>>>>>>>>>>>>>>>>>>> For all those interested... This is what I used...
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> Dim strWhere As String
>>>>>>>>>>>>>>>>>>>>>> strWhere = "WHERE " & Me.Filter
>>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>>> DoCmd.SetWarnings False

>>>>>>>>>>>>>>>>>>>>>> DoCmd.RunSQL "UPDATE qryVulnerabilities " & _
>>>>>>>>>>>>>>>>>>>>>> "SET qryVulnerabilities.ThreatStatus = 'Closed'" & strWhere
>>>>>>>>>>>>>>>>>>>>>> DoCmd.SetWarnings True
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> ...it allows the update to run only on the records filtered by those
>>>>>>>>>>>>>>>>>>>>>> selected. (Filter by Selection)
>>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 8:41 PM NEWER USER wrote:

>>>>>>>>>>>>>>>>>>>>>>> You nailed it with the Me.Filter as the WHERE string. Thank you so much for
>>>>>>>>>>>>>>>>>>>>>>> your persistence.
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> "Gina Whipp" wrote:

>>>>>>>>>>>>>>>>>>>>>>>> On Tuesday, January 05, 2010 8:47 PM Gina Whipp wrote:

>>>>>>>>>>>>>>>>>>>>>>>> No problem... Glad it is working for you...

>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>>>> Gina Whipp
>>>>>>>>>>>>>>>>>>>>>>>> 2010 Microsoft MVP (Access)
>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>> "I feel I have been denied critical, need to know, information!" - Tremors
>>>>>>>>>>>>>>>>>>>>>>>> II
>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>> http://www.regina-whipp.com/index_files/TipList.htm

>>>>>>>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>>>>>>>>>>>>>>>>>> Mocking WCF Services Using Moq
>>>>>>>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/b76105f1-f832-4b52-82ba-d5a61d435d81/mocking-wcf-services-using-moq.aspx

[cenzo]

The main table is [ingegneri]
The form name is [sim_ingegneri]
This form is loaded with 60 random records from the main table, setting the RecordSource in Form_Load event.
Then i would like to increment the filed [frequenza] in the main table, but only for those records selected in the form. This code will update all the table and i cannot figure out how i can update only the selected records.
Thanks for your help

Private Sub Form_Load()
Me.RecordSource = "SELECT TOP 60 * FROM ingegneri ORDER BY Rnd(int(Now*Num)-Now*Num);"

DoCmd.RunSQL "UPDATE [ingegneri] SET [ingegneri].[Frequenza] = [ingegneri].[Frequenza] + 1;"
End Sub

Submitted via EggHeadCafe - Software Developer Portal of Choice
Kentico CMS for ASP.NET Sites
http://www.eggheadcafe.com/tutorials/aspnet/ee551a85-2206-446e-bc7d-c978f60ec671/kentico-cms-for-aspnet-sites.aspx

[Douglas J. Steele]

You'll have to loop through the data on the form record by record to get its
ID, and issue a separate update statement for each one.

Another approach would be to store the 60 records in a temporary table, and
then use that temporary table in your single update statement.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)

"cenzo" <dev...@teletu.it> wrote in message
news:201091871...@eggheadcafe.com...