Results 1 to 4 of 4
  1. #1
    orcobal is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    1

    Updating a Table through a Form

    Dear all,



    I have a Table called Records with the following four columns:

    ID StartChainage EndChainage DistanceTraveled

    The DistanceTraveled is the difference between EndChainage and StartChainage.

    For each new record, the StartChainage should be equal to the EndChainage of the previous record. In order to input data to this Table, I have created a Form called Record1 where I can only add (by typing) values in the field called EndChainage, while in the field StartChainage I use the following expression:

    =IIf([ID]=1,0,DLookUp("[EndChainage]","Record","[ID]=Forms![Record1]![ID]-1"))

    By this expression, I actually say that for the first record in the Table Records (i.e. ID=1) the value in the StartChainage must be 0, else it should obtain the value of the EndChainage field of the previous record. This works fine and I have a Form with fields where I only input the value of the EndChainage and the Form sets automatically the value of the StartChainage for the next record and it also calculates the DistanceTraveled.

    The problem is that the calculated fields are NOT updating the relevant fields of the Table. In the Table the only updated fields are the EndChainage ones, i.e. the ones I only type manually the values!

    How can I make the Table to get automatically updated by the calculated fields of the Form?

    Thank you and best regards,
    orcobal

    PS. Maybe I could use calculated fields in the Table itself, but this is not what I really want.

  2. #2
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    StartChaninage control is not bound to the table. It is bound to:
    =IIf([ID]=1,0,DLookUp("[EndChainage]","Record","[ID]=Forms![Record1]![ID]-1")) so it calculates on the fly as a display in your form and doesn't affect the table.

    If must be bound to the table. Change it record source to the table's field.

    The auto calculation should be move to the AfterUpdate of the EndChainage control i.e.
    Me.StartChainage
    =IIf([ID]=1,0,DLookUp("[EndChainage]","Record","[ID]=Forms![Record1]![ID]-1"))

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    ID is autonumeric? Let's make an experiment. You entered records with ID = {1, 2, 3, 4, 5, 6}. Now you deleted entry ID=6 for some reason. Next entry ID will be anyway 7. So you have now entries with ID = {1,2,3,4,5,7}. What happens with your formula?

    You have to calculate StartChainage as a query like (WHERE [ID] is ID value for entry you are calculating StartChainage for, and I renamed table from Records to tRecords):
    Code:
    SELECT TOP 1 EndChainage FROM tRecords WHERE ID < [ID] ORDER BY ID DESC
    Even better is to calculate StartChainage as
    Code:
    SELECT TOP 1 EndChainage FROM tRecords WHERE EndChainage < [EndChainage] ORDER BY EndChainage DESC
    , so you can add entries in any order. But then you must have a button on form, where you can at any time force the recalculation of StartChainage for whole table, and for to be sure, an Open event of application/main form too to do same (what is generally a good idea whenever you have calculated values in tables).

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The problem is that the calculated fields are NOT updating the relevant fields of the Table.
    which is practically the main reason why everyone ought to be advising you to not store calculations in tables in 99.9% of cases. I can't help but wonder why the focus of the responses was more about solving the update rather than what I would have thought to be one of the 10 (or so) database design commandments.

    These types of calculations should only be done in forms or reports and not be stored.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 12-08-2017, 09:56 AM
  2. Replies: 2
    Last Post: 11-11-2016, 09:07 AM
  3. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  4. Updating main table from temp table AND form value
    By shabbaranks in forum Programming
    Replies: 8
    Last Post: 05-01-2013, 07:18 AM
  5. updating a table from a form.....
    By softspoken in forum Access
    Replies: 7
    Last Post: 04-21-2010, 09:04 AM

Tags for this Thread

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