Getting data from a previous row

[ryetee]

when a certain form is opened the open event runs this code

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM PaymentsMadeForALbaran where saleslink = " & Me.OpenArgs, dbOpenDynaset)
Set Me.Recordset = rs

This is to do with having transaction processing and having the ability to discard changes.

This all works fine.

I want the user to be able to add a new record. This works fine too, but I want to be able to get data from the previous row as well.

I was using a method that basically did a DLookUp on the previou record by assuming that the ID was current ID - 1. I quickly discovered this has inherent problem, i.e. it doesn't work!

So is there any way in getting data from the previous record in the record set for ue in the new record?

I tried adding in this
If Me.Recordset.BOF Then
Beep
MsgBox "You are at the beginning."
DoCmd.GoToRecord , , acFirst
Else
DoCmd.GoToRecord , , acLast
Me.netamount = Me.Recordset.netamount
Me.previousamount = Me.Recordset.paymentamount
end if

but it seems to pick up the data from he 1st record of the recordset.

I also changed
Set rs = db.OpenRecordset("SELECT * FROM PaymentsMadeForALbaran where saleslink = " & Me.OpenArgs, dbOpenDynaset)
to
Set rs = db.OpenRecordset("SELECT * FROM PaymentsMadeForALbaran where saleslink = " & Me.OpenArgs & " ORDER BY PAymentID", dbOpenDynaset)

but no change

 

[arnelgp]

you need to use a Recordset:

dim rs as dao.recordset
set rs=me.recordsetclone
set rs=rs.openrecordset
if not (rs.bof and rs.eof) then
    rs.movelast
    Me![textbox1]=rs![fieldname1]
    Me![textbox2]=rs![fieldname2]
    ...
    ...
End If
set rs=nothing

 

[isladogs]

See this link for two methods that definitely work including one using a self join to the previous record
http://www.mendipdatasystems.co.uk/getpreviousrecordvalue/4594484854

 

[The_Doc_Man]

Please note that without an ORDER BY, there IS no previous row because records have no inherent order as stored. Sounds counter-intuitive, but it is true.

In order to have a "previous record" you have to supply TWO things. You said you added the ORDER BY and that is good. You need that. But you then ALSO need to supply the recordset.MoveLast for the ordered recordset after you open it, OR you need to have the ORDER BY include a DESC (descending) keyword so that when you open the recordset, by default it goes to the first record.

And Arnel brings up a good point about testing for having zero records matching your WHERE criterion. In that case, you would need to supply some defaults.

 

[ryetee]

OK thanks everyone. I've used arnelgp's method withan orderby (but not desc which I undertsand I don't need).

I have a follow on question.
From the previous rows I don't want the user to be able to change anything other than add notes. I know I can set the form to allow only additions but then I can't get the user to add notes. Is this easy to do?

 

[arnelgp]

on each controls in your form add this to the GotFocus event:

=fnDisable()

and on its LostFocus:

=fnEnable()

create the 2 func in a module:

public function fnEnable()
screen.activecontrol.enabled=-1
end function

public function fnDisable()
dim c as control
c=screen.activecontrol
if me.newrecord=false then
if c.name="notes" then c.enabled=0
end if
end function

 

[Solo712]

you need to use a Recordset:

dim rs as dao.recordset
set rs=me.recordsetclone
set rs=rs.openrecordset
if not (rs.bof and rs.eof) then
    rs.movelast
    Me![textbox1]=rs![fieldname1]
    Me![textbox2]=rs![fieldname2]
    ...
    ...
End If
set rs=nothing

It should be pointed out that this routine will work only if the recordset is ordered by a field which appends the record as the last member of the recordset. I would do this instead:

dim rs as dao.recordset
set rs=me.recordsetclone
set rs=rs.openrecordset
if not (rs.BOF and rs.EOF) Then
rs.FindFirst "[OrderField] = " & DMax([OrderField],[MyTable]) ' add quotes if field is text 
    Me![textbox1]=rs![fieldname1]
    Me![textbox2]=rs![fieldname2]
    ...
    ...
End if
set rs=nothing

Happy New Year to all,
Jiri

 

[arnelgp]

whatever what you are seing in form as the last/preious record is the same when you use its Clone. using dmax is redundant.

 

[ryetee]

on each controls in your form add this to the GotFocus event:

=fnDisable()

and on its LostFocus:

=fnEnable()

create the 2 func in a module:

public function fnEnable()
screen.activecontrol.enabled=-1
end function

public function fnDisable()
dim c as control
c=screen.activecontrol
if me.newrecord=false then
if c.name="notes" then c.enabled=0
end if
end function

just looking at this and getting some errors
should =fn etc be call fn etc?

Also doesn't like me.newrecord or c = Screen.ActiveControl
Screen.ActiveControl appears to be the contents of the control
if i changeto c = Screen.ActiveControl.name i get the name of the control but i get an error - error 91 object variable or with block variable not set

OK I've changed c= to set c= and now it doesn't like .name

is this anything to do with the fact i'm using transaction processing?

 

[ryetee]

just looking at this and getting some errors
should =fn etc be call fn etc?

Also doesn't like me.newrecord or c = Screen.ActiveControl
Screen.ActiveControl appears to be the contents of the control
if i changeto c = Screen.ActiveControl.name i get the name of the control but i get an error - error 91 object variable or with block variable not set

OK I've changed c= to set c= and now it doesn't like .name

is this anything to do with the fact i'm using transaction processing?

I changed the code from a public to a private function to a private sub and made some subtle changes.

With breakpoints all over the place the flow through the code seems logical but c.name is coming out as a control on the previous form. The previous form opes the current one with acdialog


Private Sub fnEnable()
Screen.ActiveControl.Enabled = -1
End Sub

Private Sub fnDisable()
Dim c As Control
Dim d As String
'Exit Function

Set c = Screen.ActiveControl
d = Screen.ActiveControl.Name
If Me.NewRecord = False Then
If c.Name = "strnotes" Then c.Enabled = 0
End If

End Sub

 

[Royce]

DESC stands for "Decending", the default is Ascending. Descending is sort order 10, 9, 8, ... Ascending is sort order 1,2,3,4,... As to the actual implementation, a lot depends on the specific details. In other words, I know enough to know there is not enough information to give a definitive answer to your question. But there is enough information in all the posts that you probably can figure it out. I'd suggest do some testing in break mode and the immediate window until you are sure you understand what is going on.