Function reused by several forms ?

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

I want to write a function that could be reused on several forms, each of
which is based upon a different table.
The function needs to find the number of records which have todays date in a
field, which has a different name
in each table. Then it builds up a string which contains the date and number
of records, which is then returned by
the function.
Ideally I should be able pass this function the name of the table, and the
name of the field, and it would return
the text string I require.
I'm still getting to terms with VBA and data objects. Will I need to add a
variant of the function into each form ?
or can I generalize the function so it would work in any of the forms as
long as I can pass the table & field that contains
the date being used by that form ? I'm puzzled how to refer to the table and
the relevant field in the table in my code
and pass it into the function ?

TIA
Tony
 
Place the function in a module to make it public. Then add parameters to
hold the target table name and target field name. Something to the effect of

Function countRecords(strTargetTable as String, strTargetField as String)

countRecords = DCount(strTargetField, strTargetTable, strTargetField =
Date())

End Function

That should be enough to get you started.

David H
DCOUNT(fieldName, tableName, whereStatement)
 
Hi Tony,

This is untested air code but should get you started. Note that the date
in the DCount expression must be formatted mm/dd/yyyy.

Public Function Foo(TableName As String, FieldName As String) As String
Dim lngCount As Long

lngCount = DCount(FieldName, TableName, FieldName & "=#" _
& Format(Date(), "mm/dd/yyyy") & "#"
Foo = "There are " & Format(lngCount,"0") " records in " _
& TableName & " dated " & Format(Date(), "dd/mm/yyyy") & "."
End Function
 
Hmmm.... dejavu(sp?)

John said:
Hi Tony,

This is untested air code but should get you started. Note that the date
in the DCount expression must be formatted mm/dd/yyyy.

Public Function Foo(TableName As String, FieldName As String) As String
Dim lngCount As Long

lngCount = DCount(FieldName, TableName, FieldName & "=#" _
& Format(Date(), "mm/dd/yyyy") & "#"
Foo = "There are " & Format(lngCount,"0") " records in " _
& TableName & " dated " & Format(Date(), "dd/mm/yyyy") & "."
End Function
Click to expand...
 
Thanks Guys. I didn't even know Access would have a built in function to do
this sort of thing !
Tony
 
I would recommend reading up on Modules and Public Functions and Public
Subs as they are EXTREMELY powerful.
 
Hi David,
Can you recommend anywhere to read up on these topics ? The Access help is
OK if you want to check up the details on something
you already know, but hopeless if you don't know what to look up in the
first place (if you know what I mean) ?
Thanks
Tony
 
Hmmm, a feature that fundamentally is a good ideal, but basically
useless. I've NEVER heard of anything like that from Microsoft.

First, try surfing the web under "Access Functions", "Access VBA",
"Access Modules", "VBA Functions", "VBA Modules".

The concepts behind public sub's and function's apply to all Office Apps
with VBA (Word, Excel, Access, Powerpoint, Outlook(?)) So its not really
neccessary to use one specifically, however Access might be the easiest.

Second, look for a book entitled 'ACCESS DEVELOPER's HANDBOOK' by Sybex.
At least I think that's the name of it. I bought the Access 2.0 Version
of the book (eons ago), and it helped IMMENSLY. Other than that, check
out a bookstore and browse the books that are there. Border's
(Southeastern USA) seems to have a really good selection. Anytime I
needed help with something, I'd hit the bookstore and use the books as
reference. I was able to read up enough to walk out of the store with a
a good starting point, without having to actually pay for the book.

In short, a SUB (subroutine) is code that just executes any number
actions (open the door, close the window, order pizza, etc.). A FUNCTION
is code that performs a calculation and returns the result AND/OR
executes a number of actions and returns a result (go out to the car and
tell me if my cell phone is there - yes or no). You are already familar
with FUNCTIONS in that DATE(), TIME(), NOW(), DLOOKUP() are all functions.

David H

FYI...Here's a public function that I wrote this AM...

It's designed to take the various values supplied and put them all
together into a single block for various forms & reports.

Function formatAccountName(strFirstName As Variant, strLastName As
Variant, strAddressLine1 As Variant, strAddressLine2 As Variant, strCity
As Variant, strState As Variant, strZipCode As Variant, strContactName
As Variant, strCompanyName As Variant)

Dim tmpAccountName As String
tmpAccountName = ""
tmpAccountName = tmpAccountName & strFirstName & " " & strLastName
& Chr$(10) & Chr$(13) & Chr$(10) & Chr$(13)
tmpAccountName = tmpAccountName & strAddressLine1 & Chr$(10) &
Chr$(13) & Chr$(10) & Chr$(13)
If IsNull(strAddressLine2) = False Then
tmpAccountName = tmpAccountName & strAddressLine2 & Chr$(10) &
Chr$(13) & Chr$(10) & Chr$(13)
End If
tmpAccountName = tmpAccountName & strCity & ", " & strState & " " &
strZipCode

formatAccountName = tmpAccountName

End Function
 
Just a comment on your function, David.

If you want the text to be on different lines, you need to use Chr$(13) &
Chr$(10) in that order. What you've got (Chr$(10) & Chr$(13)) will not work.
Another alternative is to use vbCrLf instead.
 
I was wondering why I had to duplicate them on each line. The fact that
I can never remember Chr(10), Chr(13), and Chr(9) and keep them separate
doesn't help. Shouldn't have had all those drugs in the 60's, wait I
wasn't born in the 60's....

David H
 
Back
Top