From: Douglas J. Steele on
=NameOfFunction(arg1, arg2, ...)

The equal sign needs to be there.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"magmike" <magmike7(a)yahoo.com> wrote in message
news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com...
On Mar 22, 5:12 am, "Douglas J. Steele"
<NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> Reports can have modules associated with them, or you can create the
> function in a stand-alone module.
>
> --
> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "magmike" <magmi...(a)yahoo.com> wrote in message
>
> news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com...
> On Mar 21, 6:10 am, "Douglas J. Steele"
>
>
>
>
>
> <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> > Why not create a function and use that function as the ControlSource,
> > rather
> > than trying to jam it all into a massive IIf statement?
>
> > --
> > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> > (no e-mails, please!)
>
> > "magmike" <magmi...(a)yahoo.com> wrote in message
>
> >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com...
>
> > > This IIf statement is in an unbound control on a report. When I paste
> > > it into the Control Source under the unbound control's properties - I
> > > get an error saying the expression is too complicated. I've double
> > > checked it, and it is crafted correctly. I'm guessing it is too long.
> > > Is there a maximum amount of IIf checks?
>
> > > Just a little background - There are 29 different possible fields from
> > > a query that I might want to populate this control with, depending on
> > > the value of another field. Unfortunately, I am stuck working with a
> > > badly built database and trying to make this work in spite of it. So,
> > > my control source answer is a 29 step IIf statement. Is there a work
> > > around on this?
>
> > > Thanks in advance for your help!
>
> > >magmike
>
> I didn't realize I could do a function in a report. How would I do
> that?

How would I reference the function in the Control Source?


From: magmike on
On Mar 22, 9:10 am, "Douglas J. Steele"
<NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> =NameOfFunction(arg1, arg2, ...)
>
> The equal sign needs to be there.
>
> --
> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "magmike" <magmi...(a)yahoo.com> wrote in message
>
> news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com...
> On Mar 22, 5:12 am, "Douglas J. Steele"
>
>
>
>
>
> <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> > Reports can have modules associated with them, or you can create the
> > function in a stand-alone module.
>
> > --
> > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> > (no e-mails, please!)
>
> > "magmike" <magmi...(a)yahoo.com> wrote in message
>
> >news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com....
> > On Mar 21, 6:10 am, "Douglas J. Steele"
>
> > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> > > Why not create a function and use that function as the ControlSource,
> > > rather
> > > than trying to jam it all into a massive IIf statement?
>
> > > --
> > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> > > (no e-mails, please!)
>
> > > "magmike" <magmi...(a)yahoo.com> wrote in message
>
> > >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com....
>
> > > > This IIf statement is in an unbound control on a report. When I paste
> > > > it into the Control Source under the unbound control's properties - I
> > > > get an error saying the expression is too complicated. I've double
> > > > checked it, and it is crafted correctly. I'm guessing it is too long.
> > > > Is there a maximum amount of IIf checks?
>
> > > > Just a little background - There are 29 different possible fields from
> > > > a query that I might want to populate this control with, depending on
> > > > the value of another field. Unfortunately, I am stuck working with a
> > > > badly built database and trying to make this work in spite of it. So,
> > > > my control source answer is a 29 step IIf statement. Is there a work
> > > > around on this?
>
> > > > Thanks in advance for your help!
>
> > > >magmike
>
> > I didn't realize I could do a function in a report. How would I do
> > that?
>
> How would I reference the function in the Control Source?- Hide quoted text -
>
> - Show quoted text -

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

I don't expect you to do this for me - but if you know of a good
resource that I can study up with - I'm rather new to the module and
to reports.
From: Douglas J. Steele on
Open the report in Design view.

Go to the Properties window and set the Has Module property to Yes (look on
the Other tab).

Go into the VB Editor (Alt-F11)

Ensure the Project Explorer window is open (Using Ctrl-R to display it if it
isn't)

You should now find an entry for Report_<NameOfYourReport> under Microsoft
Office Access Class Objects in the explorer. Double-click on it to get into
the module.

Add your function there.



--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"magmike" <magmike7(a)yahoo.com> wrote in message
news:76e52f67-7699-425d-8fc2-8cf60f720da2(a)l25g2000yqd.googlegroups.com...
On Mar 22, 9:10 am, "Douglas J. Steele"
<NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> =NameOfFunction(arg1, arg2, ...)
>
> The equal sign needs to be there.
>
> --
> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "magmike" <magmi...(a)yahoo.com> wrote in message
>
> news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com...
> On Mar 22, 5:12 am, "Douglas J. Steele"
>
>
>
>
>
> <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> > Reports can have modules associated with them, or you can create the
> > function in a stand-alone module.
>
> > --
> > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> > (no e-mails, please!)
>
> > "magmike" <magmi...(a)yahoo.com> wrote in message
>
> >news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com...
> > On Mar 21, 6:10 am, "Douglas J. Steele"
>
> > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> > > Why not create a function and use that function as the ControlSource,
> > > rather
> > > than trying to jam it all into a massive IIf statement?
>
> > > --
> > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> > > (no e-mails, please!)
>
> > > "magmike" <magmi...(a)yahoo.com> wrote in message
>
> > >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com...
>
> > > > This IIf statement is in an unbound control on a report. When I
> > > > paste
> > > > it into the Control Source under the unbound control's properties -
> > > > I
> > > > get an error saying the expression is too complicated. I've double
> > > > checked it, and it is crafted correctly. I'm guessing it is too
> > > > long.
> > > > Is there a maximum amount of IIf checks?
>
> > > > Just a little background - There are 29 different possible fields
> > > > from
> > > > a query that I might want to populate this control with, depending
> > > > on
> > > > the value of another field. Unfortunately, I am stuck working with a
> > > > badly built database and trying to make this work in spite of it.
> > > > So,
> > > > my control source answer is a 29 step IIf statement. Is there a work
> > > > around on this?
>
> > > > Thanks in advance for your help!
>
> > > >magmike
>
> > I didn't realize I could do a function in a report. How would I do
> > that?
>
> How would I reference the function in the Control Source?- Hide quoted
> text -
>
> - Show quoted text -

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

I don't expect you to do this for me - but if you know of a good
resource that I can study up with - I'm rather new to the module and
to reports.


From: John W. Vinson on
On Mon, 22 Mar 2010 09:34:46 -0700 (PDT), magmike <magmike7(a)yahoo.com> wrote:

>I hope this doesn't come off as stupid - but I am just a hack that
>does what I need to get by, but, how would I link the module to the
>report? And would a function be the same as an event like I do on a
>form?

You can create a function either in the Form's Module, or in a new Module (on
the modules tab in the database window). The function can have any name you
like (well, not the same as the name of any stored Module, no blanks or
special characters in the name, etc.).

So let's say you create a function

Public Function MyNeatFunction(OneArg As String, AnotherArg As Long) As Long
<do a bunch of stuff with the arguments or with form references>
MyNeatFunction = <some expression>
End Function

Then, you can use the function in two ways:

1. If it's in a Module rather than in the Form's Module, you can put

NewLabel: MyNeatFunction([textfield], 31)

2. On the Form which has the function in its module, you can set the Control
Source of a textbox to

=MyNeatFunction("Active", [NameOfANumberControl])

The name of the module is irrelevant, and isn't needed in the use of the
function.
--

John W. Vinson [MVP]
From: Douglas J. Steele on
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:j48fq5p05md48off3cfl4d7bdtltup71dg(a)4ax.com...
>
> The name of the module is irrelevant

Not quite, John. The name of the module must be unique: it cannot be the
same as the name of any function (or sub) contained in the project.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)



First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Access sub form help neededSG 1400
Next: .dat file