Solved

refresh main form from subform without losing place in subform

Posted on 2013-06-18
6
746 Views
Last Modified: 2013-06-18
I need to update some data in the main form from a subform and simply added
Forms![frmInvoices].Refresh.

The problem is it takes me away from the subform/field. Is there a way I can frefresh the main form without the cursur going back to the beginning of the form?
0
Comment
Question by:Shawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 39258006
You could use a variable to store the primary key of the current record in the subform, then after refreshing the main form, reposition the subform back to that record.

However, it is probably not necessary to requery the main form at all.  If you are simply updating data in a field of the parent record, then do it on the form, instead of executing code to update the table.

For example, instead of this:

CurrentDb.Execute "Update tblInvoices set DatePaid=Date() where InvoiceNum=" & Me.InvoiceNum
Me.Parent.Refresh


... you can simply do this:

Me.Parent!DatePaid = Date()

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
0
 
LVL 1

Author Comment

by:Shawn
ID: 39258033
I tried that but it didn't sum the amount. It copied the total to 0.

Forms![frmInvoices]![TotalPrice] = Me.SumTotalPrice

It seems when I step through it the total gets there....there seems to be a delay when calculating the new total.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39258060
I assume SumTotalPrice is a textbox in the subform footer with a ControlSource something like this:
=Sum([TotalPrice])

Which event is your code attached to?  It's possible that you need to save the modified subform record and/or requery SumTotalPrice before the new value will be available.

Try this:
Me.Dirty = False ' save current record
SumTotalPrice.Requery ' refresh the total
Me.Parent!TotalPrice = SumTotalPrice ' update parent record

Open in new window

You may find you need line 1 OR line 2, but not both.

-- Graham
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 1

Author Comment

by:Shawn
ID: 39258096
neither seemed to work
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39258299
Which event is your code attached to, and what exactly are you trying to do?
0
 
LVL 1

Author Comment

by:Shawn
ID: 39258375
I found a way to keep my initial code and just set focus on the next field. works nicely. similar to your above mentioned suggestions. thank you.

so I used
Forms![frmInvoices].Recalc

then
Me.Comments.SetFocus
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question