Update SQL on non-record set causes requery

[cp2599]

I'm stumped. On my tabbed form, I have a single form that adds a new
record to table A and displays several unbounded fields from Table B.
On this single form I also have a continuous subform that displays all
of the records from Table A which seems to be working okay. As part
of the Add action, I update system fields, save the record,
recalculate the unbounded fields, run an UPDATE sql to update the
unbounded fields in Table B, display a record saved message window,
and go to the new record.

The single form briefly goes to the new record, but then it displays
the first record in Table A. I commented out/executed the code a
section at a time and found that the culprit is the UPDATE sql
statement. It's updating a table that is not part of the form's
record set so it shouldn't cause a requery. The UPDATE sql appears to
be working okay.

Any ideas?

 

[John W. Vinson]

The single form briefly goes to the new record, but then it displays
the first record in Table A. I commented out/executed the code a
section at a time and found that the culprit is the UPDATE sql
statement. It's updating a table that is not part of the form's
record set so it shouldn't cause a requery. The UPDATE sql appears to
be working okay.

Any ideas?

Not without seeing the code, no. Seeing the Form's Recordsource would help
too.

Do note that an update query will invalidate any recordset based on its target
table. You may need to record the current record's primary key, requery the
form, and navigate to the saved record.

 

[cp2599]

Not without seeing the code, no. Seeing the Form's Recordsource would help
too.

Do note that an update query will invalidate any recordset based on its target
table. You may need to record the current record's primary key, requery the
form, and navigate to the saved record.

I have a docmd.gotorecord,,acNewRec at the end of the Add (on click).
This is where I want to go.
It briefly goes there then changes back to the first record. What
actions take place after hitting a command button besides the code
that is part of the OnClick function? Why does everything work fine
when I comment out the SQL UPDATE?

 

[John W. Vinson]

I have a docmd.gotorecord,,acNewRec at the end of the Add (on click).
This is where I want to go.
It briefly goes there then changes back to the first record. What
actions take place after hitting a command button besides the code
that is part of the OnClick function? Why does everything work fine
when I comment out the SQL UPDATE?

As I said... a SQL UPDATE will invalidate the form's recordsource and probably
cause a requery (if it works at all, which surprises me somewhat).

I presume there is an error in your code, but you have chosen not to post it,
so I'm unable to comment further.