Theodore- No. Storing a calculated value in a table is a very bad idea. You would have to write a ton of complex code to make sure the value stays in sync. For this one case, save the SQL as a query.
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 25, 2006 3:14 AM To: [email protected] Subject: [ms_access] Re: Continuous form problem John, I'm really-really sorry for your headache! I understand it's quite complicated and I strugle to tame the beast! I have a rule: I never store a form's query as a standalone one. I always store it with the form's data source. This way I avoid both deleting the querry or amend it by mistake. The other way I thought so I would finally get to a solution is to create an OfferGrandTotal field in tblOffers table. Then I would store the value of the Main form's txtGrandTotal calculated textbox to the afore-mentioned field when the form gets closed. That is, being in MainForm, tblOffers.OfferGrandTotal = Me!txtGrandTotal WHERE Me.OfferID = tblOffers.OfferID Any clue of a way to do so? I mention again that txtGrandTotal textbox is a calculated control, not bound to a field. I hope that this eases matters down. Thanks once more and sorry of your hurting head! Theodore --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Yes, my head hurts. > > Use the form's query in the DSum: > > DSum("[OfferUnitPrice]*[DesignQuantity]", "<name of query here>", "OfferID > = " & <offer ID you want>) > > 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: Monday, January 23, 2006 11:18 PM > To: [email protected] > Subject: [ms_access] Re: Continuous form problem > > John, the problem is that the value of OfferUnitPrice isn't stored in > tblOfferDesigns table. It would be ok if so. It's only calculated by > the use of the public function you suggested to me, i.e. > > 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 > > This function is then passed in the form's query, i.e. > > SELECT tblOfferDesigns.*, tblOffers.TaxRate, > OfferDesignUnitPrice([OfferDesignID], [TaxRate]) As OfferUnitPrice > FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID = > tblOfferDesigns.OfferID; > > where OfferUnitPrice is bound to a textbox. Another textbox carries > the function OfferUnitPrice*DesignQuantity and finally a textbox in > the form's footer does the Sum(OfferUnitPrice*DesignQuantity) for the > selected Offer. > > (DesignQuantity is a field in tblOfferDesigns) > > Kind of complicated I suppose! > > You see, there are three tables involved so I can't figure out a Dsum > function that will do the job. The first table is > tblOfferDesignDetails where are stored the individual items (their > quantities and price) that make up the various designs. The second > table is tblOfferDesigns that carries the various designs offered > (their quantities). You see every design is made of various items is > various quantities. Then every design may be required in various > quantities. An offer normally carries a number of various designs. The > third table is tblOffers which carries the TaxRate. > > So it's like that: > Sum{[Sum[ItemQuantity*ItemUnitPrice*(1-[ItemDiscount])from > tblOfferDesignDetails]] * [DesignQuantity from tblOfferDesigns]} > and then all this multiplied by [(1 + TaxRate) from tblOffers]. > > I tried to make it as clear as I would here!!! > > I think your getting a headache and that's my fault! > > I'm really grateful, Theodore > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Theodore- > > > > Probably no need for a custom function. Use DSum: > > > > DSum("[OfferUnitPrice]*[OfferDesignQuantity]", "<table name here>", > > "OfferID = " & <expression to get offer ID to filter>) > > > > To do the requery: > > > > Forms!OtherForm!OtherSubform.Form!ComboBox.Requery > > > > 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: Monday, January 23, 2006 7:49 AM > > To: [email protected] > > Subject: [ms_access] Re: Continuous form problem > > > > Cheers John. > > > > The requery statement worked alright. > > > > The function (code) is needed (although the textbox expression is > > fine) as I wish to to get this value in another form and I don't like > > to include in this form's record source the tables required to > > calculate this value. The only common field that is included in the > > form's data source is OfferID and this is needed as a filter in the > > function. > > > > And again:(!) How would I requery a combobox that lies in a subform > > from another form (not the subform's parent form)? > > > > Your help is really great! > > > > Theodore > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > 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 > > > > > > > > > > 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/
