Update Query based on Current Recordset

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

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.
 
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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

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.


.
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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

:

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

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.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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.

:

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

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

:

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

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.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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.

:

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

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

:

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

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.


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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.

:

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

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.

:

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

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

:

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

message
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.


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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.

:

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

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.

:

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

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

:

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

message
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.


.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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?

:

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

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.

:

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

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.

:

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

message
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

:

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

message
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.


.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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?

:

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

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.

:

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

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.

:

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

message
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

:

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

message
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.


.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

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?

:

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

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.

:

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

message
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.

:

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

message
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

:

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

in
message
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.


.



.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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

:

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

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?

:

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

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.

:

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

message
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.

:

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

message
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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

:

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

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?

:

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

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.

:

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

message
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.

:

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

message
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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"))

:

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

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

:

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

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?

:

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

message
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.

:

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

message
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.

:

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

in
message
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
I attached a sample .mdb sent to your AOL address.

Gina Whipp said:
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 said:
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 said:
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

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"))

:

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

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

:

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

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?

:

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

message
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.

:

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

message
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.

:

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

in
message
Thanks for responding - This is what I have and getting
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I attached a sample .mdb sent to your AOL address.

Gina Whipp said:
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 said:
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?

:

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

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"))

:

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

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

:

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

message
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?

:

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

message
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.

:

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

in
message
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.

:

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" <[email protected]>
wrote
in
message
Thanks for responding - This is what I have and
getting
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I attached a sample .mdb sent to your AOL address.

Gina Whipp said:
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 said:
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?

:

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

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"))

:

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

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

:

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

message
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?

:

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

message
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.

:

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

in
message
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.

:

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" <[email protected]>
wrote
in
message
Thanks for responding - This is what I have and
getting
Click to expand...
Click to expand...
Click to expand...
 
Back
Top