Using a function in a report footer

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

I am using a function (fnFileCount) that counts the number of files in
a specific folder and which have a specific file extension. I am using
this function to get a value in a text box in the report footer. It
works fine when I hard code the control source of the text box ie

= fnFilecount("T:\Administration\iT Files\Wallpapers\", "*.jpg")

However, I want to set this up so that the function will pick up the
values for the first part of the function (strFolderSpec) and the
second part (strFileExt) from values that are held in a table
(tblPathnames). I can define those variables in the open event of the
report e.g.

strFolderSpec = DLookup("fldCoverImagePathname", "tblPathnames")

but I don't know how to call this function in the control source of
the text box. Am I on the right track here or am I completely off
beam?

Thansk

Gordon
 
Gordon,

If fnFilecount is a public function in the code sheet of the report,
AND strFolderSpec and strFileExt are global to the code sheet,
then all you need as the control source in the text box is
=fnFilecount(), where the function returns the value to be
displayed in the report.

Bill










- Show quoted text -
Click to expand...

Bill,

Thanks for the reply. You're gonna have to help me. I'm at the outer
limits of my VBA knowledge here.
As suggested, I have moved the function to the code sheet of the
report viz

Public Function fnFilecount(FolderSpec As String, FileSpec As String)
As Integer
etc etc

In the "on open" event of the report I have put the follwoing code:

Dim FolderSpec As String, FileSpec As String
FileSpec = "*.jpg"
FolderSpec = DLookup("fldCoverImagePathname", "tblpathnames") & "\"

In the control source for the text box in the footer of the report I
have put:
=fnFilecount()

Result? A "#Name?" error on the report.

I have tested the on open code - it produces the correct FolderSpec:

H:\My Documents\Databases\EMM\03 CoverImages\

and the correct FileSpec

*.jpg


Help!

Gordon
 
Gordon,
All you need is:

Public Function fnFilecount() As Integer

There are no longer any arguments being passed
to fnFilecount.

Bill






Bill,

Thanks for the reply. You're gonna have to help me. I'm at the outer
limits of my VBA knowledge here.
As suggested, I have moved the function to the code sheet of the
report viz

Public Function fnFilecount(FolderSpec As String, FileSpec As String)
As Integer
etc etc

In the "on open" event of the report I have put the follwoing code:

Dim FolderSpec As String, FileSpec As String
FileSpec = "*.jpg"
FolderSpec = DLookup("fldCoverImagePathname", "tblpathnames") & "\"

In the control source for the text box in the footer of the report I
have put:
=fnFilecount()

Result?  A "#Name?" error on the report.

I have tested the on open code - it produces the correct FolderSpec:

H:\My Documents\Databases\EMM\03 CoverImages\

and the correct FileSpec

*.jpg

Help!

Gordon- Hide quoted text -

- Show quoted text -
Click to expand...

Hey Bill,

I finally got it but you made me work for it ! (Good thing <grn>.
Aided understanding.)

I didn't need anything in the on open event of the report. I just had
to define the 2 variables in the body of the function and retitle the
function as you suggested.

Many thanks

Gordon
 
Back
Top