Retrieve Field Value from Previous Record in an Event. 1

[maetrix]

Hi,

I need help typing up an Event in VBA code which would retrieve the value from the same field in a previous record.

Right now I'm using this in the Control Source of a subform to look up the previous data.

=IIf(IsNull(([txtVol]-1)),0,DLookUp("regdollar","tblVolume","[VolumeID]= ( [txtVol] - 1 )&quot)

txtVol is the key for the table, (the field name is VolumeID) and is an autonumber. Unfortunatly, this won't work for me because [txtVol] is not a reliable autonumber.

The pseudo code I'm having troubles translating to VBA is the following:

"Return the value from [regdollar] in the table [tblvolume] FROM the previous record WHERE DeptID is the same."

How would this look and would this be put in the Before_Update Event?

Thnx

-Toby Kliem- sapere aude: Dare to be wise

 

[thornmastr]

I am assuming you want the volume Id from the immediate preceding record on your subform, and that the sub form container on your mainm form is child0. ido not know at what point you want to get the previous value, but when you are ready to get it, this will do it for you.

Dim rs1 as DAO.recordset
Dim BKM as variant
Dim vPRdolar as variant

BKM = me.bookmark
Set rs1 = me.child0.form.recordsetclone
Rs1.bookmark = BKM
Rs1.moveprevious
Vprdolar = rs1.regdollar
Rs1.close
Set rs1 = nothing

The value from the previous record is in the variant vprdolar.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[maetrix]

Thank you Robert,

I just found out about the RecordsetClone method tonight and I'm reading like mad to learn how it works. Let me see if I can clarify what I posted.

I call the subform using a filter and need to retrieve data from a record that is being blocked. In this case, would it be practical to use AbsolutePostion - 1 instead of MovePrevious?

Otherwise, that script worked superbly. (And infinitly much faster then the Dlookup!)

--Toby Kliem--
Novice Programmer
Expert Hack sapere aude: Dare to be wise

 

[thornmastr]

Toby,

If you look up absolute position in Access help, you will find that bookmarks is considered a more viable approach. The only time I ever use absolute position is if I need the record position in a recorset.

I've never used it to do any type of positioning per se.

Just my $0.02 cents worth.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[maetrix]

That makes sense. Then is there a way in VBA to temperarily disable the link criteria then re-enable it?

--Toby Kliem-- sapere aude: Dare to be wise

 

[thornmastr]

Why would you want to do that. Refer to the child properties (controls, values, etc) using the child control(the control which contains your sub form). For example if, if you have a control on your sub form called UserStreet; then to get its value from the main form you would do it as follows.

me.child0.form.userstreet.value

where child0 is the name of the subform container. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com