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 <*> 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/
