Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43

    Query Last LastModified Field from Form and Subforms

    Hi All,



    Back again...

    I have a form with 2 subforms. The form is for tracking customer activities (info about the customer, customer orders and comments related to their inquiry).

    I have placed 3 text boxes called LastModified. 1 is on the main form and 1 on each subform. Each field is updated with a short VBA code to update Value = Now(). This code is in the BeforeUpdate event of the forms.

    As I have many inquiries, I have created a datasheet view to monitor their status. One status metric is the number of days elapsed since we last took action and updated either of the 3 forms.

    When I only had 1 LastModified field it was easy to calculate the number of days the inquiry had not been modified/changed. But now with 3 fields and 2 of them being on subforms, I dont know how to compare the 3 and calculate the number of day that have elapsed since any of the 3 were updated.

    I hope I'm clear...can someone help with VBA or recommend a better way to do this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't say where you want to do the calculation (days elapsed). If you want it on the main form you can add an unbound textbox and set its control source to something like this:

    =Date-Max2(Me.[LastUpdated],Max2(Me.Subform1.[LastUpdated],Me.Subform2.[LastUpdated]))

    This should give you the number of days since the last update.


    Add the two public functions below to a standard module:
    Code:
    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
      Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
      Max2 = IIf(a > b, a, b)
    End Function
    Paul, the example you gave does it for the same table, I think we are looking at three different ones.

    Cheers,
    Vlad

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The example I gave works for any values passed to it, and any number of them. Your function has to be called twice. I've never used LastUpdated, but presuming your syntax for it is correct:

    Maximum(Me.[LastUpdated],Me.Subform1.[LastUpdated],Me.Subform2.[LastUpdated])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry Paul, you're right of course, I read the title and looked at the function declarations where it was showing they accept an array, missed the example on the bottom. Sorry again for the misunderstanding.

    Cheers,
    Vlad

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I guess to clarify I missed the ParamArray parameter...

    Vlad

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No worries Vlad.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    I tried using this syntax but it did not work for me.

    For example, when I type Me. expression builder does not show any options. If I type the field name (from the main form) the field is available but form names/subform names are not automatically suggested. I believe I am doing something wrong.

    By the way, the text box field for which I am building this expression is on the main form.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you in the VBA editor?

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Hi Paul,

    No, I was entering this into the expression builder (sorry, I'm new to this).

    Do I add this to the (...) section of BeforeUpdate for my new textbox?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Start with the load event of the main form. We'll probably also want to call it from the after update events of all 3 forms.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    I tried entering the following and the VBA windows says my syntax is incorrect.

    Private Sub Form_Load()
    Max (Me.TotalLastModified,Me.OpportunityDetailsOrderDe tails!LastModified)
    End Sub

    ******
    TotalLastModified is the new text box I created to show the most recent LastModified date/time.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's probably simpler to make it the control source of a textbox as Vlad showed. Did you copy the function to a standard module? Then you have to use the function name, as in my example.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Gicu View Post
    You don't say where you want to do the calculation (days elapsed). If you want it on the main form you can add an unbound textbox and set its control source to something like this:

    =Date-Max2(Me.[LastUpdated],Max2(Me.Subform1.[LastUpdated],Me.Subform2.[LastUpdated]))

    This should give you the number of days since the last update.


    Add the two public functions below to a standard module:
    Code:
    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
      Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
      Max2 = IIf(a > b, a, b)
    End Function
    Paul, the example you gave does it for the same table, I think we are looking at three different ones.

    Cheers,
    Vlad
    Vlad, this does a comparison of 2 values. But I have 3 update fields that I must compare. How would I adjust your if statement to calculate 3 fields?

  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It actually compares three values (=Date-Max2(Me.[LastUpdated],Max2(Me.Subform1.[LastUpdated],Me.Subform2.[LastUpdated]))) The first one is from the main form, the second from the first subform and the third from the second subform. But Paul's function is better as you called only once. Add the two functions below to a standard module, compile and save.

    Code:
    
    
    Code:
    Function Minimum(ParamArray FieldArray() As Variant)
       ' Declare the two local variables.
       Dim I As Integer
       Dim currentVal As Variant
    
       ' Set the variable currentVal equal to the array of values.
       currentVal = FieldArray(0)
    
       ' Cycle through each value from the row to find the smallest.
       For I = 0 To UBound(FieldArray)
          If FieldArray(I) < currentVal  Then
             currentVal = FieldArray(I)
          End If
       Next I
    
       ' Return the minimum value found.
       Minimum = currentVal
    
    End Function
    
    Function Maximum(ParamArray FieldArray() As Variant)
       ' Declare the two local variables.
       Dim I As Integer
       Dim currentVal As Variant
    
       ' Set the variable currentVal equal to the array of values.
       currentVal = FieldArray(0)
    
       ' Cycle through each value from the row to find the largest.
    
       For I = 0 To UBound(FieldArray)
          If FieldArray(I) > currentVal Then
             currentVal = FieldArray(I)
          End If
       Next I
    
       ' Return the maximum value found.
       Maximum = currentVal
    
    End Function

    To use it just make your textbox control source = Maximum(
    Me.[LastUpdated],Me.Subform1.[LastUpdated],Me.Subform2.[LastUpdated])



    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 05-13-2015, 11:10 PM
  2. Multivalue field in Subforms
    By thergot in forum Forms
    Replies: 3
    Last Post: 02-17-2015, 01:08 PM
  3. Replies: 3
    Last Post: 06-20-2011, 09:30 AM
  4. FileSearch LastModified Type Mismatch
    By CevinMoses in forum Forms
    Replies: 3
    Last Post: 10-05-2010, 04:16 PM
  5. populate the same named field in two subforms??
    By jhjarvie in forum Programming
    Replies: 1
    Last Post: 09-25-2009, 04:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums