Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Unanswered: Read value from second last record - DLast() ?

    Does anybody know what the function is to read a value from the (second)last record?

    Is it DLast() , it seems like DLast returns a random value ?

    imagine i want to increase a value each record by 2
    so i am on record 100

    num=num(from record 99)+1

    for record 101

    num=num(from record 100)+1

    ......

    this is just an example to visualise my question.

  2. #2
    Join Date
    Apr 2004
    Posts
    130
    why not keep it simple and use 2 steps?
    1) goto record last
    2) goto record previous

    Winston

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The domain functions will return a "random" value if the underlying recordset (table) does not have some sort of index.

    For example, if I have a one field table that is just city names and they're not indexed, then Access will "assume" a sort - it's not necessarily the order they were added - more likely it'll be alphebetical. And, in different circumstances, the sort order may change.

    Take this "City" table and add a field such as an Autonumber (set as the index) and the order of the data will be exactly the same every time (unless a specific sort is applied to the data). this is not to say that I can't have the cities sorted alphabetically on my forms and reports, but it does make it so that record #34 is always the same city.

  4. #4
    Join Date
    Sep 2003
    Posts
    69
    Yes i tried with DLast() and with movePrevious and moveNext
    But i get systax erros everytime , i just don't knwo what to try anymore

    Dim rs as recordset
    Dim strSQL as string
    goto EOF ' Here i already get an error
    rs.MovePrevious
    strSQL = num1
    rs.MovePrevious
    num2 = strSQL

    This Doesn't work properly, with the got eof i already get an error, without it gets in a loop and after a while i got an error message.

    Please somebody help me with the syntax


    ************************************************** ***
    the purpose is:
    num2 from current record must be same as num1 from previuos record
    ************************************************** ***

  5. #5
    Join Date
    Sep 2003
    Posts
    69
    TCACE i have an index (autonumber) and (I think) I know what to do but the problem is put the code in the right syntax without errors.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    What version of Access are you using? If later then 97, then you need to explicitly reference DAO, otherwise, your Recordset objects will be ADO, which works differently. Based on your code sample, it looks like your trying to use DAO.

    Try this:
    Code:
    Dim rs as DAO.Recordset
    Dim strSQL As String
    Set rs = Me.Recordset.Clone ' this creates a copy of the form's recordset
    rs.MoveLast ' this moves to the last record
    rs.MovePrevious ' this moves to the previous record
    Me.Bookmark = rs.Bookmark ' this changes the location of the form to match the clone
    strSQL = Me.num1  ' this is a control on your form?
    rs.MovePrevious
    Me.Bookmark = rs.Bookmark
    Me.Num1 = strSQL
    Set rs = Nothing
    I'm not quite sure what you are trying to do - what I wrote takes the value of "num1" from the 2nd to last record and copies it to the 3rd to last record.

    In any case, this may put you back on the path.

    Good luck

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    btw, EOF is a flag, not an action or method. Access let's you move one record past the end of a record set. In this position, EOF is True and and Edit, Data Access or Move Forward methods will give you an error. The same is true for BOF at the beginning of a recordset.

    The most common use of EOF is when looping through records in a recordset:

    While Not rs.EOF
    MsgBox rs(1)
    rs.MoveNext
    Wend


    have fun

  8. #8
    Join Date
    Sep 2003
    Posts
    69
    Thank you for your help.

    With your code i got an error on :
    Set rs = Me.Recordset.Clone ' this creates a copy of the form's recordset

    (3037) can not open more tables or queries.
    (no other querues or tables are open)

    btw the second rs.MovePrevious shouldn't that be a rs.MoveNext ?



    the whole function is now:

    Private Sub Tekst340_Enter()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    ravdg = rav
    Set rs = Me.Recordset.Clone ' this creates a copy of the form's recordset
    rs.MoveLast ' this moves to the last record
    rs.MovePrevious ' this moves to the previous record
    Me.Bookmark = rs.Bookmark ' this changes the location of the form to match the clone
    strSQL = Me.ravdg ' this is a control on your form?
    rs.MovePrevious
    Me.Bookmark = rs.Bookmark
    Me.totra = strSQL
    Set rs = Nothing
    End Sub

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    What version of Access are you using?

    What I gave you is from Access 2000. For Access 97, try removing the period before the Clone
    Code:
    Set rs = Me.RecordsetClone
    And, to quote myself, what I wrote takes the value of "num1" from the 2nd to last record and copies it to the 3rd to last record. I used MovePrevious twice because you did.

    Anway, give this a try. You should have better luck using the DAO recordset to do what you want. Check out the object browser to see what else you can do with it.

    Have fun

  10. #10
    Join Date
    Sep 2003
    Posts
    69
    access 2002 SP2

    with Set rs = Me.RecordsetClone i don't get an error anymore but the program gets in a kind of loop and after 5 seconds i get an error message (see attachement)

    this eroor should point to : Me.Bookmark = rs.Bookmark ' this changes the location of the form to match the clone
    Attached Thumbnails Attached Thumbnails fout.JPG  

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Somebody with Access 2002 please ....

    I have not used Access 2002.

    The part of the code regarding DAO.Recordsets should work, but the part to drive the form is appearently different.

    Sorry!

    If you want to see the code to change the current record of a form in your version, open a form in design view with table or query as it's recordsource. Turn on the toolbox and make sure the "Wizard" is on (the "magic wand").

    Add a combo box to the form - the third option is "Find a value on my form based on the record I select" or something similar. Create the combo box, then go into the form's module and you'll see the code that accomplishes this.

    Good luck

Posting Permissions

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