Requery parent form when sub form controls are updated and STAY on current record

[Slush]

Hello,



I have a sub form where users can edit/change values displayed in the parent form. If the sub form control values are changed I would like the updated information to be displayed on the parent form. In the after update event of the sub form I entered me.parent.requery which worked, but then it jumps to the first record. What is the best way to display the new info in the parent form while staying on the same record?

Thanks in advance for your time.
Slush

[Micron]

As far as I can recall, doing a refresh instead should do what you want. I presume only one person at a time could be adding records to the underlying data, so there would be no need to reload the form, which deletes any bookmarks and moves to the start of the recordset.

[Slush]

Thanks for the suggestion. Refresh is not updating the parent form. I am trying to do this using the after update event on the sub form. Is there another way to get refresh to work or any other suggestions?

[Micron]

Doing a refresh on the subform won't refresh the main. If you refresh the main, and there is a parent/child relationship set between the sub and main, refreshing the main should take care of the sub as well, as far as I can recall. If not, try refreshing both. Sorry, I'm a bit rusty on this part.

If I've misunderstood and you're doing this anyway, then post your code that calls for the main and/or subform refresh and I'll see if anything looks incorrect to me.

[Slush]

I tried refreshing and it did not update the parent form when I clicked refresh manually or made a command button on the main form to refresh or used me.parent.refresh in the after update of the child form. But even if refresh worked, how would I implement a main form refresh based on changes made in the sub form? Requery updates the info, but moves away from the desired record.

[Perceptus]

Beyond the ups and downs of how this occurs. To maintain the current cursor on refresh, normally we store the current bookmark object, and then re-apply the bookmark after the refresh is done.

[Micron]

I wouldn't have used the parent syntax, rather the Forms!NameOfMainForm syntax. However, after re-reading your first post, I'm trying to visualize what data on a main form that you would want to "refresh" anyway because it doesn't seem to fit the model. The main form is supposed to hold the data for the "one" side of a table, and the sub holds the "many" side of the other table and there is supposed to be one or more fields that tie them together in a one-to-many relationship. If the main form displayed the sales rep and the sub displayed all the accounts he/she had, what kind of changes on the sub should be affecting the one side of this relationship? As mentioned in my last post, I don't have the benefit of seeing your code, so I think there's nothing else I can offer.

[davegri]

  DIM varPlaceholder as string    
    varPlaceholder = txtTMID
    DoCmd.Requery
    Me.Recordset.FindFirst "[tblDbtcMast].[TMID] = " & varPlaceholder

The above worked for me.

[Slush]

I placed the code suggested by davegri in the sub form after update event- is that correct?
I am getting a missing syntax error on the last line of the code and I don't know if I am understanding it correctly. The forms are built off of queries. In the last line of the code what goes into [tblDbtcMast]? Is this the parent table name, parent query name, or ? Is TMID the primary key from the parent table? What is varPlaceholder?

Thanks everyone for the help.

[davegri]

Private Sub Title_AfterUpdate()    
    Dim varPlaceHolder As String
    varPlaceHolder = Me.Parent.txtAuthorID
    Me.Parent.Requery
    Me.Parent.Recordset.FindFirst "[tblAuthors].[AuthorID] = " & varPlaceHolder
End Sub

Here's a complete working example. The code is in the afterUpdate event of a text box in the subform. tblAuthors is the recordsource for the main form. AuthorID is the PK of tblAuthors, which is in txtAuthorID on the main form.
1. save the main form PK in varPlaceHolder
2. do the requery
3. lookup the original record on the main form using the saved PK

[Slush]

Awesome! Works great. Thanks for explaining it so well.

[davegri]

Glad it worked out for you, Slush. Good luck with that project!
DaveGri