If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a function in a report footer
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 |
#2
|
|||
|
|||
Using a function in a report footer
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 "Gordon" wrote in message ... 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 |
#3
|
|||
|
|||
Using a function in a report footer
On 19 Feb, 23:33, "Bill" wrote:
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 "Gordon" wrote in message ... 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- Hide quoted text - - Show quoted text - 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 |
#4
|
|||
|
|||
Using a function in a report footer
Gordon,
All you need is: Public Function fnFilecount() As Integer There are no longer any arguments being passed to fnFilecount. Bill "Gordon" wrote in message ... On 19 Feb, 23:33, "Bill" wrote: 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 "Gordon" wrote in message ... 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- Hide quoted text - - Show quoted text - 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 |
#5
|
|||
|
|||
Using a function in a report footer
On 20 Feb, 13:36, "Bill" wrote:
Gordon, All you need is: Public Function fnFilecount() As Integer There are no longer any arguments being passed to fnFilecount. Bill "Gordon" wrote in message ... On 19 Feb, 23:33, "Bill" wrote: 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 "Gordon" wrote in message .... 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- Hide quoted text - - Show quoted text - 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 - 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 |
Thread Tools | |
Display Modes | |
|
|