Results 1 to 7 of 7
  1. #1
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61

    Reading value in [MyField] on a form from current and immediately preceeding record

    Seeking recommendation for code behind a button on a form. Objective: read values from long date field [TD] in the form's current record as well as the immediately preceeding record and subtracts the smaller value from the larger value (in the current record). I wish to concatenate a text string and append it to [Notes] field on current record saying something like, "The previous READING/DOSAGE record was made 4-hrs 37-min earlier."



    Additional note - current record on form might not be committed/saved at time of button click. Don't mind saving it prior to and after all the above actions. The intent is that the text appended to the [Notes] be saved permanently in the record.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Saving calculated data is usually unnecessary and poor design, calculate when needed. Pulling value from another record done with subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord or with domain aggregate function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You'd have to elaborate as there really isn't a concept of database record order that you can rely on, outside of a query that's ordered by a reliable field. What could provide a reliable sort order field would be one that is date/time but is formatted to include the time component. You'll need that to make an hours/minutes calculation. Also, what other field ties one time to another? Plus, do you really want to store this calculation or will you always want to compare the last dosage time stamp to Now? If the comparison is only really to Now, a form ought to be providing the calculation based on the last dosage timestamp for a patient. Without knowing the nature of the db's purpose, I can't say it's a good or bad idea. Maybe all you're after is the duration between successive events for one particular person. If you mix that duration with text, you won't easily be able to make comparisons or calculations based on the duration.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Yes, of course, excuse me. [SugarID] is the keyfield in the table serving as the datasource for the form. It is of AutoNumber Type.
    Addendum - user has no access to table sort order

  5. #5
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    You'd have to elaborate as there really isn't a concept of database record order that you can rely on, outside of a query that's ordered by a reliable field. ========> Form's recordsource is a table with an AutoNumber keyfield.


    What could provide a reliable sort order field would be one that is date/time but is formatted to include the time component.


    You'll need that to make an hours/minutes calculation.


    Also, what other field ties one time to another? ========> Uncertain.


    Plus, do you really want to store this calculation or will you always want to compare the last dosage time stamp to Now? ========> Yes. Wanna store the calculation in an occasional record. NEVER want to compare the last dosage time stamp to Now(). It is VERY likely user's manual entries to the long date field [TD] will be values that are hours - perhaps days earlier than Now(). User may take glucometer away on a weekend fishing trip and not get around to entering 12-15 datapoints into database until Monday night.


    If the comparison is only really to Now, a form ought to be providing the calculation based on the last dosage timestamp for a patient. ========> No. Comparison is NOT to Now(). The values subtracted will ALWAYS be the hard coded TDStamps in the LongDate field of the current record on a form and the immediately preceeding record. When browsing records in form, flipping thru them instead of the WSJ, user comes upon one he wishes to append that information to in the [Notes] field. Single button click is desirable (as opposed to manually calculating timelapse between successive records 'n typing it in manually).


    Without knowing the nature of the db's purpose, I can't say it's a good or bad idea. ========> It has its moments, rest assured.


    Maybe all you're after is the duration between successive events for one particular person. ========> Yes - successive events for one particular person.


    If you mix that duration with text, you won't easily be able to make comparisons or calculations based on the duration. ========> Correct. Do not want to do that in this context. Just to make [Notes] field have this for SOME records (not all).

  6. #6
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Let's pretend it's a pop-up message instead of a poorly designed additional text string in a memo field.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then from what I can gather in all of that, I agree with June7's answer.
    P.S. when you want to quote, please use quote tags to make it easier to distinguish your comments from the other person's, as in
    Let's pretend it's a pop-up message instead of a poorly designed additional text string in a memo field.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-10-2017, 10:57 AM
  2. Replies: 4
    Last Post: 06-11-2015, 12:48 PM
  3. Replies: 1
    Last Post: 05-18-2015, 01:27 AM
  4. Replies: 3
    Last Post: 02-06-2015, 01:18 PM
  5. Immediately update table with form?
    By Rosier75 in forum Forms
    Replies: 6
    Last Post: 06-08-2012, 09:11 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