requery and return to the same record

[gizzu]

I have a form in spreadsheet view. Basically all I want is to update the information which is viewed, but Form.ReCalc doesn't requery the database. So I tried Form.Requery. But after doing Form.Requery, the first record of the form becomes the active record, and in some cases I have to scroll to see the record that was active before the requery. Is there any way to avoid this?

One possible I solution might be to requery that form and return to the record which was active before the requery. Here comes another problem. The form has a subform in spreadsheet view (which is viewed by clicking + to the left of any record in the first form). If I expand an record to view the sub form, it is also expanded after the requery, but I also want the main form to scroll down so the subform is fully visible (or as visible as it was before the requery), or alternatively, scroll down so the top most record in the main form that is visible also is the top most visible record after the requery. If this is not possible, maybe there is a way to make the record which was the active record before the requery to be the top most visible record after the requery.

Could someone point me in the right direction? Give any suggestion?
Any help is appreciated.

 

[Pat Hartman]

Why do you need to requery the recordset? When you do that, Access reruns the form's RecordSource query and that is why you are repositioned to the first record of the RecordSource. You would only need to do this if some other process were adding records and you wanted to see them. Are you really just trying to save the current record? if so the correct method is:
DoCmd.RunCommand acCmdSaveRecord

 

[gizzu]

Thanks for the reply!

As far as I can see it, I actually have to rerun the form's recordsource. the recordsource is a query (that includes sum()'s and group by and thereby is not updateable) that is based upon a table that is updated with a docmd.runsql from the sub form when a field is double-clicked. it is after this docmd.runsql that I have to do a requery to show the updated sum in the active record of the main form.

This sql statements will add records to the table, but that will never result in any record additions to the query that is based upon the table. However, the values in some fields in the query will change. It is these values I want to update, and a form.recalc won't do it, because, as far as I know, a ReCalc() does not update the data from the recordsource.

In the main form there is a control, Faktureringsbart. The controlsource to this control is a sum() field in the query. I've tried Faktureringsbart.Requery with no result.

 

[Pat Hartman]

I think you've over complicated the whole process. Having a subform that controls the main form is not normal, nor is using SQL to update the form's recordsource forcing you to requery it.

In any event, you need to save the primary key of the record currently being viewed in the main form. Then after the .Requery, use that key to move the record pointer back to the record with that key. I don't ever do this so I'm not sure of the code. Look up GoToRecord, Move, and Seek.

 

[gizzu]

Thank you!

Having a subform that controls the main form is not normal, nor is using SQL to update the form's recordsource forcing you to requery it.

But how am I supposed to do, then? Both the sub form and the main forms's recordsource is queries that is sum()s grouped by another field. the SQL inserts new rows in the table, which will change the sum()s, but not the total count of records in either of the queries that the forms is dependent on. How can I update the values in the fields without requery, if the field in the query is not updateable? Is this possible?

 

[Pat Hartman]

I posted a picture of a subform on a mainform( the whole form picture is too large to upload). The mainform has three subforms. Each subform has calculated fields in its footer. Just use the Count(*) or Sum(SomeField) functions. They are updated as soon as the record itself is updated or inserted.

 

[gizzu]

OK, that makes sense.

But since both my main form and my sub form is in datasheet view, no controls in the header/footer will be viewed, will they?

Basically, when a record is inserted the subform, the query that is the recordsource of the main form has to be recalculated. And if possible without loosing focus on the current record.

 

[Pat Hartman]

It is not possible to have a mainform in datasheet view when it has subforms. If you have something you are calling a main/subform, it is most likely two separate mainforms, one of which opens the other and I don't understand the relationship between them. Perhaps if you posted a picture of the form/forms it would help.

 

[gizzu]

It is not possible to have a mainform in datasheet view when it has subforms. If you have something you are calling a main/subform, it is most likely two separate mainforms, one of which opens the other

If it is not possible to have sub forms on forms in datasheet view, I don't really know what I've done. Anyway, it's some kind of view with forms that looks like sub forms.

Perhaps if you posted a picture of the form/forms it would help.

I've posted a picture on the forms. In the picture, there are 7 forms, as following:

Form_th
Form_1
Form_2
Form_3
Form_4
Form_5
Form_6

Forms 1 to 6 is sub forms to the form named on the previously line, respectively.

Form_th is in Form view, Form_1 to Form_6 in datasheet view.
Forms 1 to 5 is based on different queries that in turn is based on the table "transactions". Form_6 is based on this table directly.

There is a locked text field (Button_mtq) in Form_5 that, when double-clicked, inserts a new record in the table. When this happens, a new record is to be displayed in Form_6, and this Form has to requery it's source. However, the new record in the table does not cause the number of records in forms 1 to 5 to change, but the values with a blue ring in the picture will be changed.

This is where my whole problem is. Do I have to requery those forms? If I do, I will loose focus on the current record in those forms.

I don't understand the relationship between them.

Forms 1 to 5 is based on different queries that query the "transactions" table. All of these queries is using different kinds of GROUP BY. The value of the field "Faktureringsbart" in Form_1 is the sum of the same field in the related records in Form_2. The same is for Form_2 and Form_3 respectively, down to Form_5 and Form_6.