Re: Sum

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Start a new thread in the forms news group for form questions.

You can add code to the On Format event of the report's detail section like
Me.txtManager.Visible = Not IsNull(Forms!frmNoName!txtSupervisor)
You might need to remove the "Not" above depending on your logic. Of course
you will need to change the control and form names.

--
Duane Hookom
MS Access MVP


"neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:132FD323-4B43-4BBB-B661-F60489451DD1@xxxxxxxxxxxxxxxx
>I still haven't written the code yet. My macro is working now, except a
> couple of problems:
> 1. In the form, I have an end date text box and a start date text box. I
> have short date input mask for both. I also have >start date validation
> rule
> for end date text box but yet it allows entries in end date which are
> prior
> to end date?
> 2. there are 4 boxes in my form, manager name, supervisor name, start date
> and end date. When I supply the supervisor alone, I would like the report
> to
> print only supervisor info but it gives manager info also. I know you have
> given some code to hide detail but I would like to hide manager info only
> when the report has the supervisor parameter passed to it only.
>
>
>
> "Duane Hookom" wrote:
>
>> Learn how to add code to an event in Access. For instance find the On
>> Format
>> event of the report detail section. Click the builder button ([...]) and
>> select "Code Builder". You can then enter code between the Access
>> provided
>> lines like:
>>
>> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>> Cancel = (Forms!frmRptSlct!chkHideDetail = True)
>> End Sub
>>
>> This code would hide the detail section if the check box was checked.
>>
>>
>> --
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:EA5581DE-806D-4626-8B7B-16A4E70579B8@xxxxxxxxxxxxxxxx
>> >I dont know VB as such I didn't understand this code from you. I am now
>> > trying to accomplish what I was doing by macros only and I wonder if I
>> > can
>> > do
>> > that.
>> > This is what i have done so far:
>> > Created a macro1: Open Form called Form1.
>> > Form1 is where I input all my parameters.
>> > On the 'On Click' property of 'OK' button of 'Form1', I have a Macro2
>> > which
>> > is OpenReport. This report's record source is a parameter query. The
>> > parameters of this query are gotten from Form1. For example, one of the
>> > criteria in this query which provides the Supervisor Name is
>> > "[Forms]![Form1]![cboSupervisor].
>> > So to run the report, I run the Macro1 which opens the form Form1.
>> > After
>> > inputting all the values in the form, I click the OK button which
>> > should
>> > open
>> > the report that I want. So far it is not working. Will this approach
>> > work?
>> >
>> > "Duane Hookom" wrote:
>> >
>> >> Assuming you have a form with a check box control
>> >> Forms!frmRptSlct!chkHideDetail.
>> >> In your report, you want to hide the detail section if the box is
>> >> checked.
>> >> Use code in the On Format event of the detail section of the report
>> >> like:
>> >> Cancel = (Forms!frmRptSlct!chkHideDetail = True)
>> >>
>> >> --
>> >> Duane Hookom
>> >> MS Access MVP
>> >>
>> >>
>> >> "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:B0C8D6AE-4776-4AAD-AB9F-E9FDE3978DDB@xxxxxxxxxxxxxxxx
>> >> >I went through the article. It explains how to associate parameter
>> >> >queries
>> >> > with forms but doesn't go into explaining associating passing
>> >> > parameters
>> >> > to
>> >> > reports with forms. Does that website have such an article? Which
>> >> > other
>> >> > articles in this website would apply to me? Or can you refer me to
>> >> > another
>> >> > website
>> >> >
>> >> > "Duane Hookom" wrote:
>> >> >
>> >> >> There are a number of them. Look at the one "Customizing parameter
>> >> >> queries"
>> >> >>
>> >> >> --
>> >> >> Duane Hookom
>> >> >> MS Access MVP
>> >> >> --
>> >> >>
>> >> >> "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:6216CB91-F30F-4642-8D65-D8E8A1785EC4@xxxxxxxxxxxxxxxx
>> >> >> >I went into that website, it looks very good and uses a
>> >> >> >refreshingly
>> >> >> >friendly
>> >> >> > language instead of the textbok language that 'help' uses. Can
>> >> >> > you
>> >> >> > specify
>> >> >> > the relevant page for my question. Thanks.
>> >> >> >
>> >> >> > "Duane Hookom" wrote:
>> >> >> >
>> >> >> >> There are some great examples at Martin Green's site
>> >> >> >> http://www.fontstuff.com/access/index.htm
>> >> >> >>
>> >> >> >> --
>> >> >> >> Duane Hookom
>> >> >> >> MS Access MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> >> news:1CC9EE9F-EDEE-49D9-80F1-C3FB9D7C571F@xxxxxxxxxxxxxxxx
>> >> >> >> > How do I do "references to control on forms"
>> >> >> >> >
>> >> >> >> > "Duane Hookom" wrote:
>> >> >> >> >
>> >> >> >> >> I would stop using parameter prompt queries and begin using
>> >> >> >> >> references
>> >> >> >> >> to
>> >> >> >> >> controls on forms. This method would allow you to easily hide
>> >> >> >> >> report
>> >> >> >> >> sections based on values in controls on your form.
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Duane Hookom
>> >> >> >> >> MS Access MVP
>> >> >> >> >> --
>> >> >> >> >>
>> >> >> >> >> "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> >> >> news:C68FD9B1-728F-4460-90B4-79CA73235CC3@xxxxxxxxxxxxxxxx
>> >> >> >> >> >I attempted this but it didn't work. I created another query
>> >> >> >> >> >which
>> >> >> >> >> >would
>> >> >> >> >> >sum
>> >> >> >> >> > up revenue only by manager and joined this to my original
>> >> >> >> >> > parameter
>> >> >> >> >> > query
>> >> >> >> >> > which is my report's record source. It still gives the same
>> >> >> >> >> > results
>> >> >> >> >> > when
>> >> >> >> >> > run
>> >> >> >> >> > off against a supervisor. I dont even need the manager's
>> >> >> >> >> > total
>> >> >> >> >> > to
>> >> >> >> >> > show
>> >> >> >> >> > up
>> >> >> >> >> > when I supply only the supervisor criteria. Is there a way
>> >> >> >> >> > that I
>> >> >> >> >> > could
>> >> >> >> >> > suppress manager's info when I choose to pass on the
>> >> >> >> >> > supervisor
>> >> >> >> >> > critirea
>> >> >> >> >> > only
>> >> >> >> >> > but the manager info to show up when I pass the manager
>> >> >> >> >> > criteria
>> >> >> >> >> > in
>> >> >> >> >> > the
>> >> >> >> >> > same
>> >> >> >> >> > report
>> >> >> >> >> >
>> >> >> >> >> > "Duane Hookom" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> If you pass a criteria for only a single supervisor and
>> >> >> >> >> >> only
>> >> >> >> >> >> that
>> >> >> >> >> >> supervisor
>> >> >> >> >> >> is in the report's record source, you won't have records
>> >> >> >> >> >> to
>> >> >> >> >> >> build
>> >> >> >> >> >> all
>> >> >> >> >> >> of
>> >> >> >> >> >> the
>> >> >> >> >> >> manager's records.
>> >> >> >> >> >>
>> >> >> >> >> >> You can create a totals query the sums your records by
>> >> >> >> >> >> manager.
>> >> >> >> >> >> Add
>> >> >> >> >> >> this
>> >> >> >> >> >> totals query to your report's record source and join the
>> >> >> >> >> >> manager
>> >> >> >> >> >> fields.
>> >> >> >> >> >> This would provide a complete total for each manager.
>> >> >> >> >> >>
>> >> >> >> >> >> --
>> >> >> >> >> >> Duane Hookom
>> >> >> >> >> >> MS Access MVP
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
>> >> >> >> >> >> wrote
>> >> >> >> >> >> in
>> >> >> >> >> >> message
>> >> >> >> >> >> news:%237QSpQZjFHA.3784@xxxxxxxxxxxxxxxxxxxxxxx
>> >> >> >> >> >> > It's tough to diagnose what might not be working the way
>> >> >> >> >> >> > you
>> >> >> >> >> >> > want
>> >> >> >> >> >> > it
>> >> >> >> >> >> > to
>> >> >> >> >> >> > without a look at what you're using. Please post the
>> >> >> >> >> >> > SQL
>> >> >> >> >> >> > for
>> >> >> >> >> >> > the
>> >> >> >> >> >> > query.
>> >> >> >> >> >> >
>> >> >> >> >> >> > Jeff Boyce
>> >> >> >> >> >> > <Access MVP>
>> >> >> >> >> >> >
>> >> >> >> >> >> > "neeraj" <neeraj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> >> >> >> >> > message
>> >> >> >> >> >> > news:7414E994-B575-4A3B-AC61-CCDBC2F6DC9B@xxxxxxxxxxxxxxxx
>> >> >> >> >> >> >> I have a report which is based on a parameter query.
>> >> >> >> >> >> >> Its a
>> >> >> >> >> >> >> sales
>> >> >> >> >> >> >> report
>> >> >> >> >> >> >> to
>> >> >> >> >> >> >> sum up sales for sales reps, supervisors and managers.
>> >> >> >> >> >> >> It
>> >> >> >> >> >> >> is
>> >> >> >> >> >> >> grouped
>> >> >> >> >> >> >> on
>> >> >> >> >> >> >> managers, then supervisors. The parameters are
>> >> >> >> >> >> >> supervisor
>> >> >> >> >> >> >> name
>> >> >> >> >> >> >> or
>> >> >> >> >> >> >> manager
>> >> >> >> >> >> >> name. When I provide the value for the manager
>> >> >> >> >> >> >> parameter,
>> >> >> >> >> >> >> its
>> >> >> >> >> >> >> gives
>> >> >> >> >> >> >> the
>> >> >> >> >> >> >> correct totals for individual sales reps, supervisors
>> >> >> >> >> >> >> and
>> >> >> >> >> >> >> finally
>> >> >> >> >> >> >> that
>> >> >> >> >> >> >> manager. But when I pass on the value of the supervisor
>> >> >> >> >> >> >> parameter,
>> >> >> >> >> >> >> it
>> >> >> >> >> >> > gives
>> >> >> >> >> >> >> correct total for supervisor but for the manager, it
>> >> >> >> >> >> >> gives
>> >> >> >> >> >> >> the
>> >> >> >> >> >> >> same
>> >> >> >> >> >> >> figure
>> >> >> >> >> >> > as
>> >> >> >> >> >> >> the supervisor whereas it should be much higher as
>> >> >> >> >> >> >> there
>> >> >> >> >> >> >> are
>> >> >> >> >> >> >> several
>> >> >> >> >> >> >> supervisors under a manager
>> >> >> >> >> >> >
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.

Quantcast