update fields in main form without having to re-open the man form for re-query

[gstylianou]

Good evening to all,


I have a form that gets data from a query (qryCustomers) and has a built-in command button (cmdInvoices) which opens the form "frmInvoices". I want after the update of some fields in the second form (frmInvoices which is pop-up-form) the related fields in the main form to be updated directly without having to close and open again so it becomes requery.



It is understood that after the update in the main form and when the re-query finish, I want to produce the same record

Regards

[June7]

Requery/Refresh always sets focus on first record of form. So unless the form RecordSource has a parameter for filtering to a single record, will have to somehow return to the record. This will probably require saving ID to a global variable (declared in module header or by TempVars) and using that to reset focus to desired record.

[gstylianou]

First all, i would like to thank you for your reply.

But, as i'm not expert can you please write me a sample code in order to modify it in my case?

Thanks again

[June7]

Bing: access requery form and return to record

Review:
http://bytes.com/topic/access/answer...-return-record

Correction, looks like global declaration not really needed, just procedure declaration. Something like:

Dim intID As Integer

intID = Me.ID

DoCmd.OpenForm "formname", , , , , acDialog

Me. Requery

With me.RecordsetClone
.FindFirst "ID=" & intID
Me.Bookmark = .Bookmark
End With

[gstylianou]

Good Morning June7,

Unfortunately it doesn't work..

I wrote the following code in order 1st) to close the popup form which the calculated fields are embedded and 2) in order to return to the parent form and requery.

Private Sub Form_Close()


DoCmd.OpenForm "FrmCustomers" (this is the parent form)


Dim RecId As Long


RecId = Me.Code (this field is autonumber field which is embedded in child popup form)


Me.Requery


With Me.RecordsetClone
.FindLast "Code" & "=" & RecId (i want going to the last)
Me.Bookmark = .Bookmark
End With

DoCmd.Restore
End Sub

[June7]

Not quite following the code example I posted.

Need to declare and set the variable before opening the popup form. Don't want the record ID from popup form. Need record ID from the first form.

[gstylianou]

Hi June7,

Really appreciate your help. Unfortunately i cannot to that..its beyond my knowledge in vba. I will appreciate if you can write me a sample code in order to try..

Thanks in advanced

[June7]

I already wrote sample code in post 4.

[gstylianou]

June7,

I tried the following code......but......nothing

Dim intCode As Integer
intCode = Me.Code


DoCmd.OpenForm "frmCustomers", , , , , acDialog


Me.Requery


With Me.RecordsetClone
.FindLast "Code=" & intCode
Me.Bookmark = .Bookmark
End With

Any idea?

[June7]

That is my idea. I don't know your db so can't really be more specific.

What do you mean by 'nothing'? No error and no results? Refer to link at bottom of my post for debugging guidelines.

My sample code used FindFirst, you are using FindLast. I have never used FindLast but I suppose as long as only one record meets the condition, should work as well.

[gstylianou]

Good morning June7,

I tried this code on the "On Close" event of the child form and i'v just seen nothing to doing.. no filtering to the record. Please note that, the parent form taking values from query..... Maybe is that problem for filtering?

Regards

[June7]

The query is form RecordSource? That should be okay.

At this point I am just making wild guesses. If you want to provide db for analysis, follow instructions at bottom of my post.

[gstylianou]

Good morning June7,

The query is the form Record Source. Can you please a suggest anything?

Thanks in advanced

[June7]

Suggestions already given are all I have. Read post 12 again.

[gstylianou]

June7 the problem has been solved.

I just write the code Forms!frmCustomers.Requery on the "OnClose" event of the child form.

Anyway, thank you again