Hmmmm. Try:
Me.Parent.SubformA.Requery Why write a function when the expression in the text box works just fine? John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of tyki9799 Sent: Saturday, January 21, 2006 8:41 PM To: [email protected] Subject: [ms_access] Re: Continuous form problem John- I entered Me.Parent.Refresh on the After Update event in Subform B's module but what I got is a trembling subform A! I seems that it tries to update for ever it's records and the other calculated elements in it and Access gets stuck displaying the hour glass so I have to close it throught the Windows Task Manager! I fixed the Sub as you adviced creating a textbox with "=Sum([OfferUnitPrice]*[OfferDesignQuantity])" as it's data source. How would I get the same result through a query in a Standard Module as the one you suggested to me the other time? Thanks, Theodore --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Theodore- > > Try: > > Me.Parent.Refresh > > Put the Sum in the footer section of Subform A (in Continuous view). > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of tyki9799 > Sent: Friday, January 20, 2006 11:25 PM > To: [email protected] > Subject: [ms_access] Re: Continuous form problem > > John thanks for the advice. It worked out fine except for subform A > getting updated (I followed your instructions). > > I entered this code on the After Update event in Subform B's module: > > Forms!MainForm.SubformA.Refresh > > But then get the following error message: > > Run-time error '438': > Object doesn't support this property or method > > And one further question: > > In subform A's data source there is an OfferDesignQuantity field and > there's a Textbox bound to this field in subform A. > I also have another unbound textbox that does carries the > multiplication OfferUnitPrice*OfferDesignQuantity. So far so good > (except for updating everything as soon as subform B gets updated). > > I wish to have in the Main form a textbox that would show the total of > the Offer i.e. the Sum(OfferUnitPrice*OfferDesignQuantity). > How would I do this? > > Theodore > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Theodore- > > > > First, delete your Sub and put this in a Standard module (one you > can see in > > the list in the database window): > > > > Public Function OfferDesignUnitPrice(lngDesignID As Long, dblTaxRate As > > Double) As Currency > > > > Dim varX, varY > > > > varX = DSum("[ItemQuantity]*[ItemUnitPrice]*(1-[ItemDiscount])", > > "tblOfferDesignDetails", "[OfferDesignID]=" & lngDesignID) > > > > varY = CCur(Nz(varX * (1 + dblTaxRate))) > > > > OfferDesignUnitPrice = varY > > > > End Sub > > > > Now change your query to: > > > > SELECT tblOfferDesigns.*, tblOffers.TaxRate, > > OfferDesignUnitPrice([OfferDesignID], [TaxRate]) As OfferUnitPrice > > FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID = > > tblOfferDesigns.OfferID; > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] > On Behalf > > Of tyki9799 > > Sent: Thursday, January 19, 2006 8:08 PM > > To: [email protected] > > Subject: [ms_access] Re: Continuous form problem > > > > John, thanks for the response. > > > > The form's record source is the following: > > > > SELECT tblOfferDesigns.*, tblOffers.TaxRate > > FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID = > > tblOfferDesigns.OfferID; > > > > The unound textbox in question is txtOfferDesignUnitPrice. > > > > The sub that makes the calculation is this: > > > > > > Private Sub OfferDesignUnitPrice() > > > > Dim varX, varY > > > > varX = DSum("[ItemQuantity]*[ItemUnitPrice]*(1-[ItemDiscount])", > > "tblOfferDesignDetails", "[OfferDesignID]=" & Me.[OfferDesignID]) > > > > varY = CCur(Nz(varX * (1 + Me.TaxRate))) > > > > Me!txtOfferDesignUnitPrice = varY > > > > End Sub > > > > > > Then "OfferDesignUnitPrice" (i.e. the sub) is entered in the form's On > > Current event procedure. > > > > Thanks again, > > > > Theodore > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > Theodore- > > > > > > If you can put a function call in the Record Source query that > > includes in > > > the parameters at least one field in the recordset, you should be > > able to > > > fix the problem. What is the Control Source? What is the code that > > > calculates the value? Are you requerying or refreshing from any > > event in > > > the form? > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > > > -----Original Message----- > > > From: [email protected] [mailto:[EMAIL PROTECTED] > > On Behalf > > > Of tyki9799 > > > Sent: Wednesday, January 18, 2006 5:47 AM > > > To: [email protected] > > > Subject: [ms_access] Continuous form problem > > > > > > Hi to all! > > > > > > I have a continuous form with a calculated textbox. The calculation > > > comes by some code in the form’s module. The trouble is that > this > > > textbox shows the same result for all form’s records. The > > correct one > > > is the one in the current record. I know this is common problem with > > > continuous forms. What should I do so that the correct calculation > > > shows for every record? > > > > > > Thanks in advance > > > > > > Theodore > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > Yahoo! Groups Links > Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
