Update Query based on Current Recordset

  • Thread starter Thread starter NEWER USER
  • Start date Start date
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 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...
 
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 said:
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 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

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

message
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...
 
You nailed it with the Me.Filter as the WHERE string. Thank you so much for
your persistence.

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

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
You nailed it with the Me.Filter as the WHERE string. Thank you so much
for
your persistence.

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

:

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

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

message
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

in
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

"NEWER USER" <[email protected]>
wrote
in
message
I added single quotes around the word 'Closed' and
only
one
Click to expand...
Click to expand...
Click to expand...
 
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
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
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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/tutorial...978f60ec671/kentico-cms-for-aspnet-sites.aspx
 
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.
 
Back
Top