Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repeat previous record in current field (Access 97)

    Hi

    I have a subform where quite often the value of a field is the same as the previous value in that field. I know ctrl+' repeats the value, but to save a keystoke I have added the following code in the field's enter procedure to duplicate it

    If IsNull(Me.BillableHours) Then
    SendKeys "^'"
    End If

    However, this seems a very unelegant way of doing things. I would appreciate any advice (ie code) which is more refined (and robust).

    Thanks
    WTH

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat previous record in current field (Access 97)

    I don't know if you would call it more elegant but I would write a function that spits out the value of the field in the last record.

    ie

    Funtion lastValue() as String 'or whatever variable type the field is

    dim rstTable as DAO.Recordset

    'open a recordset based on the table the form is based on
    set rstTable = OpenRecordset "TableName", DbOpenSnapshot

    If (rstTable.RecordCount <> 0) Then 'If the table isn't empty
    rstTable.MoveLast 'move to the last entry
    lastValue = rstTable![FieldName] 'and assign its value to the lastValue variable
    Else
    lastValue = "" 'if the table is empty make lastValue variable = nothing
    End If

    'clean up
    Set RstTable = Nothing

    End Function

    Then in your data entry form, make the default value of the Text box representing your field =lastValue()

    Ryan

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

    Re: Repeat previous record in current field (Access 97)

    There are all kinds of ways to do this. One of the simplest is to use the tag property of each control. In the AfterUpdate event of the subform (not the individual controls), write the saved values of each control to the tag property of that control. Then when you move to a new record, you can test for a null and get the previous value from the tag if you need it. However, any scheme like this will fail if the user skips over several records without making changes because then the tag properties would not be updated. The tag scheme can get pretty fancy, but it depends on exactly what you want to do.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeat previous record in current field (Access 97)

    As an addition to the replies already given here, have a look at <!post=this thread, 138575>this thread<!/post>. In the replies to the original question, you'll find hints and attached samples.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat previous record in current field (Access 97)

    Hi

    Thanks for all the advice. I'll give them ago.

    Regards
    WTH

Posting Permissions

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