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