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