Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / January 2008

Tip: Looking for answers? Try searching our database.

f7s4r4v

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
rebecky - 22 Jan 2008 20:56 GMT
This is probably a real stupid question, but I have this code I wrote and run
it on the afterupdate event of a form(the user types in a date or checks a
box and some fields of the form are populated with information from another
form and a new  record is copied down with a different date for each day of
the week for as many weeks as specified in the code -there are then empty
attendance hour fields for the purpose of tracking daily attendance).  I want
to call this code from another form (afterupdate) to run the event procedure
on a different form.......I have no idea how to "call" anything and am just
trying to learn about writing functions and variables and all that.  Can you
tell me how to make this code a function and then how to "call" it?

Thank you
Ken Sheridan - 25 Jan 2008 18:26 GMT
You can create a function in a standard module and declare it as Public so it
can be called from anywhere in the database.   If the code currently in the
form's module contains a reference to the current form using Me (strictly
speaking this is a reference to the current instance of the class, but think
of it as reference to the form) then you'll need to change this in the public
function.  Similarly any references to a control on the form simply by the
control's name will need changing so that the function knows which control on
what form is referred to.

You can reference the form in a number of ways.  One would be to include a
full reference to the form in the function; another would be to pass a
refernce to the form into the function as an argument.

I think the best way to illustrate this would be with a simple example.  
Lets say you have an event procedure in a form's module which adds the values
in two controls together and assigns the result to another control:

Private Sub Form_AfterUpdate()

  Dim dblTotal as Double

  dblTotal =txtFirstNumber + txtSecondNumber

  Me.txtTotal = dblTotal

End Sub

Note that I've used me when referencing the txtTotal control but not when
referencing the txtFirstNumber and  txtSecondNumber controls.  This is purely
to show how Me can be included or omitted in code in a form's module when
referencing controls; it could equally well have been included or omitted
when referencing any of them.

To do the same thing in a function in a standard module, including a full
reference to the form in the code:

Public Function AddNumbers()

  Dim dblTotal as Double

  dblTotal =Forms("MyForm").txtFirstNumber + Forms("MyForm").txtSecondNumber

  Forms("MyForm").txtTotal = dblTotal

End Sub

This would be called from any code in the database simply by entering the
following line:

   AddNumbers

To do it by passing a reference to the form into the function as an argument:

Public Function AddNumbers(frm As Form)

  Dim dblTotal as Double

  dblTotal =frm.txtFirstNumber + frm.txtSecondNumber

  frm.txtTotal = dblTotal

End Sub

In this case the reference to the form would be passed to the function when
its called like so:

   AddNumbers Forms("MyForm")

If the function is called from within MyForm's module you can use Me to
refer to the form as the argument:

   AddNumbers Me

We can take this further and pass the references to all three controls into
the function:

Public Function AddNumbers(ctrl1 As Control, _
                                              ctrl2 As Control, _
                                              ctrl3 As Control)

  Dim dblTotal as Double

  dblTotal =ctrl1 + ctrl2

  ctrl3 = dblTotal

End Sub

The underscore character _ is used here as a continuation character,
enabling us to write a single line over several lines for better readability.
In this case the references to the three controls be passed to the function
when its called like so:

   AddNumbers Forms("MyForm").txFirstNumber, _
                                    Forms("MyForm").txtSecondNumber, _
                                    Forms("MyForm").txtTotal

In the above cases the form MyForm has to be open when the form is called of
course.

So far we've only used the function to carry out some action, adding two
numbers together and assigning the result to a control).  For this a sub
procedure could have been used in exactly the same way.  A function, however,
can return a value, so we could have used it to return the sum of the two
numbers:

Public Function AddNumbers(ctrl1 As Control, _
                                              ctrl2 As Control) As Double

  Dim dblTotal as Double

  dblTotal =ctrl1 + ctrl2

  AddNumbers = dblTotal

End Sub

The return value could then be assigned to the third control in some other
code with:

   Forms("MyForm").txtTotal =   _
       AddNumbers Forms("MyForm").txtSecondNumber, _
                             Forms("MyForm").txtTotal

Also values passed into functions can be changed by the function.  If we add
the following function to a module:

Public Function ChangeNumbers(intFirst As Integer, intSecond As Integer)

   intFirst = intFirst * 2
   intSecond = intSecond * 3

End Function

And call it like so:

   Dim FirstNumber As Integer, SecondNumber As Integer
   
   FirstNumber = 2
   SecondNumber = 3
   
   ChangeNumbers FirstNumber, SecondNumber
   
   MsgBox "First:" & FirstNumber & _
      vbNewLine & "Second:" & SecondNumber

We'll find that the message box reads:

First:4
Second:9

The function has changed the values of the variables, multiplying them by 2
and 3 respectively.  This is because by default the arguments passed into the
function are ByRef, i.e. a reference to the original variables, so these are
changed by the function.  We might or might not want this to happen, however;
if not the arguments should be passed ByVal (by the values of the variables,
not references to them), so if the function is changed to:

Public Function ChangeNumbers(ByVal intFirst As Integer, ByVal intSecond As
Integer)

   intFirst = intFirst * 2
   intSecond = intSecond * 3

End Function

the message box will now read:

First:2
Second:3

i.e. the original values remain unchanged.

I hope the above helps you understand how functions work, but if there's
anything you need clarifying let me know.

Ken Sheridan
Stafford, England

> This is probably a real stupid question, but I have this code I wrote and run
> it on the afterupdate event of a form(the user types in a date or checks a
[quoted text clipped - 8 lines]
>
> Thank you
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.