Update current record only

[Belinda]

I have a form displaying a list of records that need to be researched. When
the user is ready to update the status of one record, they click on the
status which opens a form & subform. The subform updates a tblDateStamp.
What I need to do next is have the new status that was just entered into the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that updates the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status], tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole table.
....how can I do this?

 

[George Nicholson]

Ideally I just one the one record updated, not update the whole table.

...how can I do this?

Well, is there anything unique about the current record that you could use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it updates one
record and then you want to see that same update reflected on your main
form. I guess I have to assume that your main form is based on a different
table than the one that just got updated, and that requerying the form won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have a form displaying a list of records that need to be researched.
When
the user is ready to update the status of one record, they click on the
status which opens a form & subform. The subform updates a tblDateStamp.
What I need to do next is have the new status that was just entered into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

 

[Belinda]

Hi George!
Thanks for taking the time to reply and your willingness to assist.

The unique record is DBID. The issue is that there are many records in
tblDummies with corresponding records in tblDateStamp. So when the user
clicks on "save record" the SQL that runs updates all records from
tblDateStamp in tblDummies where DBID are equal, so in fact it;s updating
approx 500 records instead of just one.

And yes, the mainform is based on a different table (query) than the
subform. Each record on the mainform may have multiple updates. Each update
is captured in the tblDateStamp and displayed in the subform in datasheet
format. They're linked by the DBID field.

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

Well, is there anything unique about the current record that you could use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it updates one
record and then you want to see that same update reflected on your main
form. I guess I have to assume that your main form is based on a different
table than the one that just got updated, and that requerying the form won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have a form displaying a list of records that need to be researched.
When
the user is ready to update the status of one record, they click on the
status which opens a form & subform. The subform updates a tblDateStamp.
What I need to do next is have the new status that was just entered into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

 

[George Nicholson]

Ok, lets try this from the other end...
What element, or combination of elements, does a record in tblDummies have
that make it unique? Obviously, DBID alone is not unique.

If you can come up with an answer to that, then that's likely what your
WHERE clause needs to contain.
If there isn't an answer to that then you'll have to use a broad WHERE
clause and update 500 records. (You would also have what seems to be a
non-normalized table.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George!
Thanks for taking the time to reply and your willingness to assist.

The unique record is DBID. The issue is that there are many records in
tblDummies with corresponding records in tblDateStamp. So when the user
clicks on "save record" the SQL that runs updates all records from
tblDateStamp in tblDummies where DBID are equal, so in fact it;s updating
approx 500 records instead of just one.

And yes, the mainform is based on a different table (query) than the
subform. Each record on the mainform may have multiple updates. Each
update
is captured in the tblDateStamp and displayed in the subform in datasheet
format. They're linked by the DBID field.

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

Well, is there anything unique about the current record that you could
use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it updates
one
record and then you want to see that same update reflected on your main
form. I guess I have to assume that your main form is based on a
different
table than the one that just got updated, and that requerying the form
won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have a form displaying a list of records that need to be researched.
When
the user is ready to update the status of one record, they click on the
status which opens a form & subform. The subform updates a
tblDateStamp.
What I need to do next is have the new status that was just entered
into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

 

[Belinda]

This is what I have:
o tblDummies - Bill of Lading (BL for short) (indexed, no dups); DBID is
autonumber primary key; and a bunch of other fields.

o tblDateStamp - a record is created in this table from tblDummies
(capture DBID from tblDummies); who made the update, date&time and it also
has an autonumber as primary key.

These two tables are in a one-to-many relationship with tblDummies being the
one side.

Work is assigned to users based on BL status (logged, pending, escalate,
call).
The records on tblDummies are opened via a Form A; if a BL on "logged"
status needs to be changed to "Escalate" the user will click on the status
which opens up another form, Form B. This form has a subform (Form C) based
on tblDateStamp as data entry. The date and user name are captured
automatically, the user assigns a status and a reason for a status change;
user clicks on "save record, " and closes the form. I need to have this new
"status" passed back tblDummies via Form A. This is needed so that the
requery function drops this record from the user's view (they work filtered
forms based on BL Status). So in reality this record will now become part of
someone else's queue when they open their version of Form A.

So how can I say, hey tblDateStamp, take this DBID that is currently on Form
C and update the status on tblDummies that corresponds with this DBID.

Ok, lets try this from the other end...
What element, or combination of elements, does a record in tblDummies have
that make it unique? Obviously, DBID alone is not unique.

If you can come up with an answer to that, then that's likely what your
WHERE clause needs to contain.
If there isn't an answer to that then you'll have to use a broad WHERE
clause and update 500 records. (You would also have what seems to be a
non-normalized table.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George!
Thanks for taking the time to reply and your willingness to assist.

The unique record is DBID. The issue is that there are many records in
tblDummies with corresponding records in tblDateStamp. So when the user
clicks on "save record" the SQL that runs updates all records from
tblDateStamp in tblDummies where DBID are equal, so in fact it;s updating
approx 500 records instead of just one.

And yes, the mainform is based on a different table (query) than the
subform. Each record on the mainform may have multiple updates. Each
update
is captured in the tblDateStamp and displayed in the subform in datasheet
format. They're linked by the DBID field.

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

Well, is there anything unique about the current record that you could
use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it updates
one
record and then you want to see that same update reflected on your main
form. I guess I have to assume that your main form is based on a
different
table than the one that just got updated, and that requerying the form
won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



I have a form displaying a list of records that need to be researched.
When
the user is ready to update the status of one record, they click on the
status which opens a form & subform. The subform updates a
tblDateStamp.
What I need to do next is have the new status that was just entered
into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole table.
...how can I do this?

 

[Ron2005]

Have you tried the following:

On the opened form and subform, whereever in the code you are updating
the 'tblDateStamp' field add:

Forms![Original/Calling Form Name]![Subformname if there is a
subform]![fieldname] = me.tblDateStamp

Essentially you are simply having this lowest form field update process
reach back and update the corresponding field name on the form/subform
that is doing the call.

Another way if you have perhaps opened a "hiddenform" in you processing
then:
1) move the current value of tblDateStamp to a field on the hidden form
1) call/open the form/subform as a dialog
2) when updating the tblDateStamp field then also put that value in a
field on the hiddenform
3) on the return in the calling form move the hidden form field into
the actual field on this form.

Ron

 

[George Nicholson]

Going backward about 4 posts, I suggested the following:

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord/Subform

Your response was something along the lines of "..this updates too many
records in tblDateStamp..". I've only now realized that this makes no sense.
The above updates *1 record in tblDummies*, because, per your description,
DBID is unique in tblDummies.

It may well be that the above does not update the fields you want in
tblDummies. If that is the case then the SET clause simply needs editing.

That is a separate issue from "how do I update only 1 record". The UPDATE
and WHERE clauses should have addressed that issue as-is.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

This is what I have:
o tblDummies - Bill of Lading (BL for short) (indexed, no dups); DBID is
autonumber primary key; and a bunch of other fields.

o tblDateStamp - a record is created in this table from tblDummies
(capture DBID from tblDummies); who made the update, date&time and it also
has an autonumber as primary key.

These two tables are in a one-to-many relationship with tblDummies being
the
one side.

Work is assigned to users based on BL status (logged, pending, escalate,
call).
The records on tblDummies are opened via a Form A; if a BL on "logged"
status needs to be changed to "Escalate" the user will click on the status
which opens up another form, Form B. This form has a subform (Form C)
based
on tblDateStamp as data entry. The date and user name are captured
automatically, the user assigns a status and a reason for a status change;
user clicks on "save record, " and closes the form. I need to have this
new
"status" passed back tblDummies via Form A. This is needed so that the
requery function drops this record from the user's view (they work
filtered
forms based on BL Status). So in reality this record will now become part
of
someone else's queue when they open their version of Form A.

So how can I say, hey tblDateStamp, take this DBID that is currently on
Form
C and update the status on tblDummies that corresponds with this DBID.

Ok, lets try this from the other end...
What element, or combination of elements, does a record in tblDummies
have
that make it unique? Obviously, DBID alone is not unique.

If you can come up with an answer to that, then that's likely what your
WHERE clause needs to contain.
If there isn't an answer to that then you'll have to use a broad WHERE
clause and update 500 records. (You would also have what seems to be a
non-normalized table.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George!
Thanks for taking the time to reply and your willingness to assist.

The unique record is DBID. The issue is that there are many records in
tblDummies with corresponding records in tblDateStamp. So when the
user
clicks on "save record" the SQL that runs updates all records from
tblDateStamp in tblDummies where DBID are equal, so in fact it;s
updating
approx 500 records instead of just one.

And yes, the mainform is based on a different table (query) than the
subform. Each record on the mainform may have multiple updates. Each
update
is captured in the tblDateStamp and displayed in the subform in
datasheet
format. They're linked by the DBID field.


:

Ideally I just one the one record updated, not update the whole
table.
...how can I do this?

Well, is there anything unique about the current record that you could
use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it
updates
one
record and then you want to see that same update reflected on your
main
form. I guess I have to assume that your main form is based on a
different
table than the one that just got updated, and that requerying the form
won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



I have a form displaying a list of records that need to be
researched.
When
the user is ready to update the status of one record, they click on
the
status which opens a form & subform. The subform updates a
tblDateStamp.
What I need to do next is have the new status that was just entered
into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that
updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole
table.
...how can I do this?

 

[Belinda]

About 80% of the records in tblDummies have been worked previously and nnow
have multiple correspoding entries in tblDateStamp. If I update where
tblDateStamp.DBID = tblDummies.DBID I'll be updating well over 1,500 records
in the tblDummies. Also, there might be as many as 5 or 20 entries in
tblDataStamp.DBID (therefore the need to specify to use max
tblDateStamp.Date).

I'm trying to use the suggestion you made, can you clarify: "WHERE DBID = "
& ValueFromCurrentRecord/Subform ? I think that's what I have been asking
all along, how do I write that, to update just the current record? Do I
enter what you typed in there? i.e., " & ValueFromCurrentRecord/Subform"
(sorry, know it sounds dumb, but I just don't know).

Going backward about 4 posts, I suggested the following:

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord/Subform

Your response was something along the lines of "..this updates too many
records in tblDateStamp..". I've only now realized that this makes no sense.
The above updates *1 record in tblDummies*, because, per your description,
DBID is unique in tblDummies.

It may well be that the above does not update the fields you want in
tblDummies. If that is the case then the SET clause simply needs editing.

That is a separate issue from "how do I update only 1 record". The UPDATE
and WHERE clauses should have addressed that issue as-is.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

This is what I have:
o tblDummies - Bill of Lading (BL for short) (indexed, no dups); DBID is
autonumber primary key; and a bunch of other fields.

o tblDateStamp - a record is created in this table from tblDummies
(capture DBID from tblDummies); who made the update, date&time and it also
has an autonumber as primary key.

These two tables are in a one-to-many relationship with tblDummies being
the
one side.

Work is assigned to users based on BL status (logged, pending, escalate,
call).
The records on tblDummies are opened via a Form A; if a BL on "logged"
status needs to be changed to "Escalate" the user will click on the status
which opens up another form, Form B. This form has a subform (Form C)
based
on tblDateStamp as data entry. The date and user name are captured
automatically, the user assigns a status and a reason for a status change;
user clicks on "save record, " and closes the form. I need to have this
new
"status" passed back tblDummies via Form A. This is needed so that the
requery function drops this record from the user's view (they work
filtered
forms based on BL Status). So in reality this record will now become part
of
someone else's queue when they open their version of Form A.

So how can I say, hey tblDateStamp, take this DBID that is currently on
Form
C and update the status on tblDummies that corresponds with this DBID.

Ok, lets try this from the other end...
What element, or combination of elements, does a record in tblDummies
have
that make it unique? Obviously, DBID alone is not unique.

If you can come up with an answer to that, then that's likely what your
WHERE clause needs to contain.
If there isn't an answer to that then you'll have to use a broad WHERE
clause and update 500 records. (You would also have what seems to be a
non-normalized table.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hi George!
Thanks for taking the time to reply and your willingness to assist.

The unique record is DBID. The issue is that there are many records in
tblDummies with corresponding records in tblDateStamp. So when the
user
clicks on "save record" the SQL that runs updates all records from
tblDateStamp in tblDummies where DBID are equal, so in fact it;s
updating
approx 500 records instead of just one.

And yes, the mainform is based on a different table (query) than the
subform. Each record on the mainform may have multiple updates. Each
update
is captured in the tblDateStamp and displayed in the subform in
datasheet
format. They're linked by the DBID field.


:

Ideally I just one the one record updated, not update the whole
table.
...how can I do this?

Well, is there anything unique about the current record that you could
use
to replace your current WHERE clause? (like, I would hope, a Unique ID
field?)

Maybe something like:
strSQL = "UPDATE tblDummies "
strSQL = strSQL & "SET tblDummies.Status = [tblDateStamp]![Status], "
strSQL = strSQL & "tblDummies.Modified = [tblDateStamp]![DSDate], "
strSQL = strSQL & "tblDummies.ModifiedBy = [tblDateStamp]![AgentID] "
strSQL = strSQL & "WHERE DBID = " & ValueFromCurrentRecord

What I'm not 100% clear about is you say you open a subform & it
updates
one
record and then you want to see that same update reflected on your
main
form. I guess I have to assume that your main form is based on a
different
table than the one that just got updated, and that requerying the form
won't
show the update.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



I have a form displaying a list of records that need to be
researched.
When
the user is ready to update the status of one record, they click on
the
status which opens a form & subform. The subform updates a
tblDateStamp.
What I need to do next is have the new status that was just entered
into
the
tblDateStamp updated on the main form.

The only way I know how to do it is using an SQL statement that
updates
the
whole table:

Private Sub cmdSaveCR_Click()
DoCmd.SetWarnings False
CurrentDb.Execute ("UPDATE tblDummies INNER JOIN tblDateStamp ON
tblDummies.DBID = tblDateStamp.DBID" _
& " SET tblDummies.Status = [tblDateStamp]![Status],
tblDummies.Modified
= [tblDateStamp]![DSDate]," _
& " tblDummies.ModifiedBy = [tblDateStamp]![AgentID]" _
& " WHERE (((tblDateStamp.DSDate)=(SELECT Max([DSDate]) FROM
tblDateStamp AS D WHERE D.[DBID] = tblDateStamp.[DBID])))")
Me.Refresh
DoCmd.SetWarnings True

Ideally I just one the one record updated, not update the whole
table.
...how can I do this?