Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37

    How to obtain previous record field value in split form in access 2007

    Hi Everyone,

    I have a split form datasheet view with fields say field 1,field 2 (Date datatype).
    no field 1 field 2
    1 date 1 date 2
    2 date 3 date 4





    Suppose i make change to row 2's, field 1 date 3, i wish to check before update of this row's values that if the date 3 is < the previous row's field 1 , date 1.


    If it is then change the current row's field 1 date 3 value to date 1 value.
    I have tried using Me.CurrentRecord, DoCmd.SearchForRecord, DoCmd.GoToRecord but not achieving any result.

    Code:
    MsgBox "The Date you entered is incorrect should be greater than previous row's date", vbExclamation, "Date Error !"
    CurrentRecordValue = Me.CurrentRecord
    DoCmd.SearchForRecord acDataForm, , acPrevious
    AssignDate = Me.DateVariable.Value
    DoCmd.GoToRecord , , acGoTo, CurrentRecordValue
    Me.DateVariable.Value = AssignDate
    All help would be much appreciated.
    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,806
    Getting value from another record of the same dataset can be tricky. If you know you want the value from the record with the ID immediately previous to the current record, something like:

    DLookup("field1", "tablename", "no=" & Me.no - 1)

    However, that will work only if the ID field [no] has no gaps in sequence. If [no] is an autonumber field, gaps are certainly a concern. Try:

    DLookup("field1", "tablename", "no=" & DMax("no", "tablename", "no<" & Me.no))
    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
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    Hi June 7,
    Sorry for responding so late.
    My form has no field named [no] it was for your row reference, sorry for that.
    I have tried DoCmd.RunCommand acCmdRecordsGoToPrevious, Set Me.CurrentRecord = Me.CurrentRecord - 1, Tried with Me.Recordset.Move but nothing seems to work.
    i am uploading the db please help.
    Thanks
    Attached Files Attached Files

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    A few question, the answers to which, perhaps, might be helpful to others :
    1) How do you define the previous record ?
    2) Is it the record with a date just before ( not necessarily just one day previous ) the current date under consideration ?
    3) Is there a possibility of 2 records having the same date ?
    4) Do you have any primary key in the table ?

    Thanks

    Thanks

  5. #5
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    Hi recyan,
    Alright, there are two fields together acting as primary keys which define the record in the db pid,activityid.
    For any pid there can be 'n' number of activities so activityid is kept as autonumber.
    I do not like autonumber or Dmax etc. usage because any record deleted from in between records causes disturbance in sequencing.
    If the StartDate of current record is null then goto previous record select its startdate and enter it into CurrentRecord startdate.
    2 or more activities can have the same start date.
    Any more queries, feel free to ask.
    Thanks for responding.

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by sah View Post
    If the StartDate of current record is null then goto previous record
    How do we determine which is the previous record ?
    ________________________________________

    From what I have understood,
    We have Projects - PID.
    Each Project - PID has multiple Activities - AID.
    __________________________________________

    When we are editing an Activity - say AID1 belonging to Project - say PID5,
    if we find that there is no StartDate i.e. it is null for Activity - AID1 ,
    then,
    we want to find the previous StartDate for another Activity in the same Project - PID5
    ( this StartDate being the Last or Max StartDate within that Project - PID5 ),
    to which this Activity - AID1 belongs
    &
    assign the date as StartDate to Activity - AID1.
    __________________________________________

    Is that the way it is ?
    Hope I have not confused things.Thanks

  7. #7
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    Perfect!!!
    For AID 1 i would put a date which is say default for all projects first activities Jan 1, 2000
    So excepting the first activity all the rest activities can have a previous record to look at, for startdate.
    suppose there are 10 activities and the user edited activity 5 start date and assigned it null, then this start date should be activity 4 startdate and Not MAX startdate. Max Startdate would be giving me activity 10 startdate in that way if i am not wrong. this should be irrespective of form filters. say form has filtered act 3,act 5,act 8 and act 5 is edited to startdate having null then act 4 startdate is to be filled in act 5 and not act 3.
    we want to find the 'previous StartDate' for another Activity in the same Project - PID5

    I dont want to find previous startdate of any other activity i want previous activity's startdate in current activity.
    jumbling of words

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by sah View Post
    suppose there are 10 activities and the user edited activity 5 start date and assigned it null, then this start date should be activity 4 startdate
    How do we determine ( or let the system know ) that activity 4 comes before activity 5 ?
    Will it be the max date, prior to the date, which we are editing for activity 5?
    This assumes that all the activities will definitely have dates assigned to them except for a new record (activity) that is being added.

    Thanks

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,806
    Consider:
    Code:
    If IsNull(Me.StartDate) Or Me.StartDate = "" Then
        MsgBox "The StartDate you entered is incorrect", vbExclamation, "Date Error !"
    ElseIf Me.StartDate > Me.EndDate Then
        MsgBox "Start Date should be less than End Date", vbExclamation, "Date Issue!"
        Me.StartDate.SetFocus
    Else
        AssignDate = Nz(DLookup("StartDate", "ProjectSchedule", "Activity='" & DMax("Activity", "ProjectSchedule", "Activity<'" & Me.Activity & "'") & "'"), Date)
        If Me.StartDate < AssignDate Then Me.StartDate = AssignDate
    End If
    EDIT: I posted before reading previous posts (I was testing and composing). The sample db only has one table so was not aware of the relationship to projects. My suggestion should be adaptable for the additional PID criteria. It worked for the data structure provided.
    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.

  10. #10
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    Hi recyan,how would you suggest it? Should i let autonumber field activityno come into picture? Autonumbers confuse me totally.
    Sorry June 7 saw your post just now, would try it definitely.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,806
    Autonumber might be better. Problem with your Activity number is that it will be subject to alpha sort. Example, 'act 10' will sort before 'act 7'. Would need placeholder zeros, like: act 00007, act 00010
    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.

  12. #12
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    sah
    June 7 your post and code are Fantabulous!!!!
    Too Close now but if i edit any startdate and press delete key so that entire field is empty then it does nothing so i am playing a bit with the code and found some modification working.How would all of you suggest this db attached alongwith.
    Forgive me for bothering too much but i want it to look simply perfect enough without human errors creeping in - automating every step is what i want.
    BIG, BIG THANKS :-D

    Please reply i would only then mark this solved.
    Attached Files Attached Files

  13. #13
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just one thing - about AutoNumbers
    - what if some one added Activity 8 before Activity 7 ?
    or
    - added by error Actvity 8 as Actvity 7 & Activity 7 as Activity 8, then realized the error & edited it to Activity 8,..
    or
    ....

    Again, from what I remember having read somewhere, sometimes, Autonumbers should not be expected to be in a perfect sequence always.

    If, ProjectID, ActvityID & StartDate are the fundamentals, then perhaps, everything should move around them.

    Thanks

  14. #14
    sah is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    37
    Recyan Nice questions.
    I'll try to look into them after sometime,might be after four hours. Leaving for home.
    Bye.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,806
    I tested the behavior you describe. I deleted a startdate and when I moved to another record the BeforeUpdate ran and since the startdate field was empty it was updated to date from previous record. That's how you set the code.
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2012, 11:56 AM
  2. Replies: 1
    Last Post: 02-20-2012, 01:02 PM
  3. obtain record from report
    By teebumble in forum Reports
    Replies: 3
    Last Post: 07-22-2011, 04:25 PM
  4. Populate field from field on previous record
    By randolphoralph in forum Forms
    Replies: 7
    Last Post: 03-04-2011, 11:28 AM
  5. Obtain value in record
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 01-20-2011, 08:52 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
  •  
Tech Forums: Microsoft Office Forums