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