Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Created Form Fields back to Table (Access 2002)

    I understand you can't place a calculating field into a table (e.g. a field to indicate the number of days between Date Shipped and Date Received). However, on a Form, one can create this field with the DateDiff expression as the control source. How can the result found in this Form field be transferred back to a blank field in a table (or can it?). Thanks in advance for any info. Bob

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linking Created Form Fields back to Table (Access 2002)

    Why would you want to store the calculated field in a table when you can calculate it in a query or form or report anyway.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Created Form Fields back to Table (Access 2002)

    It's usually a bad idea to store calculated values because they don't correct themselves when you change the data that originally created the calculation. Plus, as Pat pointed out, there isn't any reason to store something you can calculate on the fly. There are situations where you may indeed want to store calculated data, particularly in accounting type applications, but for most applications it just leads to headaches.
    Charlotte

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linking Created Form Fields back to Table (Access 2002)

    As Patt says, you normally don't store calculated values. The problem with storing calculated values is that you need to make sure that if you change any of the inputs, the calculation is always updated. If instead you perform the calculation whenever you need it, you avoid tjhis problem.
    That being said, it can be done.

    One a form have fields for the two dates, and another for the daysbetween field.
    If you see the control source for this field to the datediff expression, it will calculate the days, but not store it. Instead, set the control source to the dayselapsed field in the table, and set its value in code.

    e.g.
    <pre>Private Sub date2_AfterUpdate()
    if not isNull(me![date1]) then
    Me!ctrldays = DateDiff("d", Me![date1], Me![date2], vbUseSystemDayOfWeek)
    end if
    End Sub

    Private Sub date1_AfterUpdate()
    If Not IsNull(Me![date2]) Then
    Me!ctrldays = DateDiff("d", Me![date1], Me![date2], vbUseSystemDayOfWeek)
    End If
    End Sub
    </pre>


    You set the value in the after update event of either of the input dates. Even though you would normally enter date1 then date2, you might go back and change date1, so you need the calculation attached to each date.

    I attach an example.
    Attached Files Attached Files
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Created Form Fields back to Table (Access 2002)

    John: Thanks for the info & the attachment; it helps out a great deal. I know the reasons for not doing something like this in a table (as mentioned also by the others). However, what if you're running a company and you're having trouble getting supplies. You order on Jan 2, 2003 and receive the order Feb 23, 2003. This info is "fixed" so I'm thinking it might be nice to save it in a field since the form holding the info could change or accidently be deleted etc etc. Then the data is lost. Just a couple of thoughts as to why one might want to do this. Thanks again for your trouble. Bob

Posting Permissions

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