main form and continuous subform requery

[Falcon88]

In the AfterUpdate event of the subform, you can recalc the main form control.

Unless you have a save button on the subform, the record you changed will not be updated until you move to a new subform record or back to the main form. That may be why you are not seeing the update immediately.

the problem of requery and recalc is when user insert new record on the sub form it jumps to the first record on that subform ....

 

[theDBguy]

the problem of requery and recalc is when user insert new record on the sub form it jumps to the first record on that subform ....

That could be a problem with Requey (if you don't use the Recordset method), but it shouldn't be a problem when using Recalc.

 

[Pat Hartman]

Requery runs the form's RecordSource again so that is why it always pops back to the first row. I believe Refresh does that also. Recalc should not move the record pointer because it doesn't involve the Recordset. It just recalcs the controls on the form.

There are a great many bad examples that show Requery and Refresh being used to save the current record. While it happens that both methods force Access to save the current record, that is NOT the purpose of either method. Saving the record is only a byproduct of the actual action the method performs which is to rerun (requery) the form's recordSource which will pick up records added/changed/deleted to your bound RecordSource regardless of whether they are made locally or by a different user. Refresh does not pick up newly added records and if another process deletes a record, you still see it except that the spot where the record was shows #deleted#.

If you want to force the current record to save without setting focus to a different form or advancing the record selector to a different record, you have two choices:

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

I prefer the former. The latter is obtuse and it is not obvious that you are telling Access to save the record. When I first saw the expression, I thought it was telling Access to discard the changes so if you use this method,please add a comment so others won't make the mistake I made if they've never seen the expression before.

The second expression is sometimes recommended by experts to overcome some anomaly but they never explain what problem they are trying to avoid. I have never had a problem with the save command so I am inclined to stick with it because it is obvious.

 

[Falcon88]

Requery runs the form's RecordSource again so that is why it always pops back to the first row. I believe Refresh does that also. Recalc should not move the record pointer because it doesn't involve the Recordset. It just recalcs the controls on the form.

There are a great many bad examples that show Requery and Refresh being used to save the current record. While it happens that both methods force Access to save the current record, that is NOT the purpose of either method. Saving the record is only a byproduct of the actual action the method performs which is to rerun (requery) the form's recordSource which will pick up records added/changed/deleted to your bound RecordSource regardless of whether they are made locally or by a different user. Refresh does not pick up newly added records and if another process deletes a record, you still see it except that the spot where the record was shows #deleted#.

If you want to force the current record to save without setting focus to a different form or advancing the record selector to a different record, you have two choices:

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

I prefer the former. The latter is obtuse and it is not obvious that you are telling Access to save the record. When I first saw the expression, I thought it was telling Access to discard the changes so if you use this method,please add a comment so others won't make the mistake I made if they've never seen the expression before.

The second expression is sometimes recommended by experts to overcome some anomaly but they never explain what problem they are trying to avoid. I have never had a problem with the save command so I am inclined to stick with it because it is obvious.

i try all this way but it can't update the textbox on the main form without move the record pointer to the first ......

 

[Pat Hartman]

i try all this way but it can't update the textbox on the main form without move the record pointer to the first ......

Did you remove all the Requery and Refresh code?

 

[Falcon88]

Did you remove all the Requery and Refresh code?

of course .... yes

 

[Pat Hartman]

Access doesn't requery all by itself. You have code that is causing the form to reposition the recordset. If you want to post your db, we'll find it for you.