Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Function Referencing a Form (2000/03)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hello - I wish to use the MS Age Function in a slightly different way to that which MS intended - (Refer http://support.microsoft.com/kb/290190)

    Specifically I do not wish to calculate the age from date of birth to now but wish to use the function to use a value stored on an input form (Me.txtTestDate) instead of Now

    Existing function in part: <pre>varAge = DateDiff("yyyy", varBirthDate, <font color=blue>Now</font color=blue> )</pre>

    The blue value is what I want to be taken from the input form. I have simply tried replacing Now with Me.txtTestDate but it doesn't know about the form. I have had very little experience with functions.

    Can this be done please?

    Leigh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    You can use Me only in the module behind the form itself. Assuming that your function is in a standard module, you'd use

    Forms!NameOfForm!txtTestDate

    where NameOfForm is the name of the form. If this name contains spaces or punctuation, enclose it in square brackets: [Name Of Form].

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Thanks for your quick reply, Hans. What do you mean by a standard module?

    I tried changing the reference as suggested but it runs to that line and then states that "..can't find the field txtTestDate"

    This is the reference I used 'varTestAge = DateDiff("yyyy", varTestDate, Forms!Students!txtTestAge)' when it came up with the message.

    The form name is 'Students' the field name is 'txtTestDate'. Have I got the reference correct?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    A standard module is the kind of module you create by clicking the New button in the Modules section of the Database window, or by selecting Insert | Module in the Visual Basic Editor.

    Referring to Forms!Students!txtTestDate requires that

    a) the form named Students is open.
    [img]/forums/images/smilies/cool.gif[/img] the form contains a control (text box) whose name is txtTestDate.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Well it is not a Class module so I assume that it is a standard one.

    <pre>Function TestAge(varTestDate As Variant) As Integer

    Dim varTestAge As Variant

    If IsNull(varTestDate) Then TestAge = 0: Exit Function

    ' varTestAge = DateDiff("yyyy", varTestDate, Now)

    varTestAge = DateDiff("yyyy", varTestDate, Forms!Students!txtTestAge)
    If Date < DateSerial(Year(Forms!Students!txtTestAge), Month(varTestDate), _
    Day(varTestDate)) Then
    varTestAge = varTestAge - 1
    End If

    TestAge = CInt(varTestAge)

    End Function
    </pre>



    And the form is open and the field is named txtTestDate

    Sould I post the db?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Oops - if the text box is named txtTestDate you should use Forms!Students!txtTestDate instead of Forms!Students!txtTestAge

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function Referencing a Form (2000/03)

    I might just take more notice of the written word...

    Thanks Hans, we are getting closer but now the age difference which is what I needed for calcs is way out. I am getting a difference of the order of 90+ years.

    I thought it might be as simple as taking a bigger value from a small value but it wasn't. The code that calls the TestAge/TestAgeMonths functions is shown below.

    <pre>=IIf(IsNull([txtDOB])," ",TestAge(([txtTestDate]-[txtDOB])) & "." & TestAgeMonths(([txtTestDate]-[txtDOB])))</pre>


    In effect I am trying to get the age at which the testing was done (previously I used system date) but the code is not working correctly.

    Any suggestions?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    You should feed a date to TestAge (and presumably to TestAgeMonths), not the difference of two dates.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Try this version of the function. It takes two arguments instead of one: the date of birth and the test date:
    <code>
    Function TestAge(varDOB As Variant, varTestDate As Variant) As Integer
    Dim intTestAge As Integer

    If IsNull(varDOB) Or IsNull(varTestDate) Then
    Exit Function
    End If

    intTestAge = DateDiff("yyyy", varDOB, varTestDate)
    If varTestDate < DateSerial(Year(varTestDate), Month(varDOB), Day(varDOB)) Then
    intTestAge = intTestAge - 1
    End If
    TestAge = intTestAge
    End Function
    </code>
    You'd use it like this:
    <code>
    =TestAge([txtDOB],[txtTestDate])</code>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Here is the corresponding function for months:
    <code>
    Function TestAgeMonths(varDOB As Variant, varTestDate As Variant) As Integer
    Dim intTestAge As Integer

    If IsNull(varDOB) Or IsNull(varTestDate) Then
    Exit Function
    End If

    intTestAge = DateDiff("m", varDOB, varTestDate)
    If Day(varTestDate) < Day(varDOB) Then
    intTestAge = intTestAge - 1
    End If
    TestAgeMonths = intTestAge Mod 12
    End Function
    </code>
    Use like this:
    <code>
    =TestAgeMonths([txtDOB],[txtTestDate])</code>

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function Referencing a Form (2000/03)

    Thanks Hans, again your guidance and code has worked a treat. I do appreciate your assistance.

    Have a Heineken on me (PayPal OK?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •