How to use recorset.moveprevious [Archive]

PDA

View Full Version : How to use recorset.moveprevious

shirley_kee

09-22-2006, 09:57 PM

boblarson

09-22-2006, 10:56 PM

Don't post the same question in different areas on the forums. It makes people here not so happy.

shirley_kee

09-23-2006, 11:12 AM

ok... am so sorry guys...

cheers..

Lilli

05-01-2007, 04:06 AM

Don't post the same question in different areas on the forums. It makes people here not so happy.

:D :D :D

Can Understand Shirley's frustration!

This question has dogged me for so long and i have asked so many questions

related to moveprevious and movenext but got no simple & direct answers.

Is there a reason MS made it so mind-bending to pull out a value from an

adjacent field? There are so many complex things I've learnt to do but just

can't wrap my head around nxt & Prev records. If anyone can reference me

to a simple but elaborate online example of the usage of .MoveNext

and .MovePrevious, please post the link. To some of us, this seemingly easy

problem has become quantum mechanics. I notice too that it's been a

recurring theme in the VBA threads.

Lilli

05-01-2007, 04:34 AM

ok... am so sorry guys...

cheers..

Shirley, if it will help, here's a demonstration from Microsoft using DAO. I found parts of it difficult and esoteric to understand without use of an actual recordset in the demonstration - perhaps you're a lot smarter and will find better use of it!

PREVIOUS AND NEXT RECORD:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'************************************************* *************
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

'************************************************* ***********
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'************************************************* ************
Function NextRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset

On Error GoTo Err_NextRecVal

' The default value is zero.
NextRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the next record.
RS.MoveNext

' Return the result.
NextRecVal = RS(FieldNameToGet)

Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function

Good Luck.

DJkarl

05-03-2007, 01:16 PM

Does anybody know how to use recordset.moveprevious?
I'm using Access 2000. I want to use moveprevious function but it generates an error. Following are my codes and I've attached the error screen. Thanks a lot...

Private Sub cmdPrev_Click()

If rstProceeds.BOF <> True Then
rstProceeds.Moveprevious
End If
End Sub

Below are my connection string
Set rstProceeds = New ADODB.Recordset
Set cmdProceeds = New ADODB.Command

sSQL = "SELECT * " & _
"FROM [AUC] " & _
"WHERE((([AUC]].[STATUS]) = ""SOLD"" "

cmdProceeds.CommandText = sSQL

cmdProceeds.CommandType = adCmdText
cmdProceeds.ActiveConnection = CurrentProject.Connection

If sSQL <> "" Then
Set rstProceeds = cmdProceeds.Execute
If rstProceeds.EOF <> True Then
Call GetAFData
End If

End If

Try using a Connection object and opening a recordset as opposed to using a command object IE

Function test()
Dim adCOn As ADODB.Connection
Dim adRST As ADODB.Recordset

Set adRST = New ADODB.Recordset

sSQL = "SELECT * " & _
"FROM [YourTableHere] "

Set adCOn = CurrentProject.Connection

Call adRST.Open(sSQL, adCOn, adOpenStatic)

If adRST.EOF <> True Then
Debug.Print adRST(0).Value
adRST.MoveNext
Debug.Print adRST(0).Value
adRST.MovePrevious
Debug.Print adRST(0).Value
End If

Set adRST = Nothing
Set adCOn = Nothing

End Function

Lilli

05-12-2007, 05:19 AM

Try using a Connection object and opening a recordset as opposed to using a command object IE

Function test()
Dim adCOn As ADODB.Connection
Dim adRST As ADODB.Recordset

Set adRST = New ADODB.Recordset

sSQL = "SELECT * " & _
"FROM [YourTableHere] "

Set adCOn = CurrentProject.Connection

Call adRST.Open(sSQL, adCOn, adOpenStatic)

If adRST.EOF <> True Then
Debug.Print adRST(0).Value
adRST.MoveNext
Debug.Print adRST(0).Value
adRST.MovePrevious
Debug.Print adRST(0).Value
End If

Set adRST = Nothing
Set adCOn = Nothing

End Function

Dr Karl,

Could you please show an example of how you'd use this function - perhaps a small table. How do you reference it from a sub so that a previous value or next value appear in PrevValue/NxtValue controls on a form.

DJkarl

05-14-2007, 05:00 AM

Dr Karl,

Could you please show an example of how you'd use this function - perhaps a small table.

I've attached a small database with this code and a small test table.

How do you reference it from a sub so that a previous value or next value appear in PrevValue/NxtValue controls on a form.

If you are referring to the Next Record and Previous Record buttons on the bottom of the form then I wouldn't use this function to do that, I would bind the form to a query or table, then those buttons would take care of themselves in terms of functionality.

Lilli

05-15-2007, 10:40 AM

First off, thank you very much for the attachment DjKarl, a quick demonstration is worth a thousand explanations with programming, and so I appreciated it very much. Please bear with me, my post is a lot shorter than it looks!

If you are referring to the Next Record and Previous Record buttons on the bottom of the form then I wouldn't use this function to do that, I would bind the form to a query or table, then those buttons would take care of themselves in terms of functionality.

I was actually referring to the records themselves, next record and previous record and how I would go about populating two controls on a form (dteNxtDate and dtePrevDate) with their values:

So that, for example,

1) when the first record in the date field of the bound table is 12/01/2006,

2) the second is 13/01/2006,

3) and the third is 14/01/2006,

whenever the second record is current, I would like my form to show the dteCurDate as 13/01/2006, dteNxtDate as 14/01/2006 and the dtePrevDate control to show 12/01/2006.

Had I a recordset with a sequential ID column, my problem would have been a lot easier to get around by placing a Dlookup statement in each control's control source:

dtePrevDate=DLookUp("[TheDate]","tblDates","[ID]=Forms![frmDates]![ID]-1")

dteNxtDate=DLookUp("[TheDate]","tblDates","[ID]=Forms![frmDates]![ID]+1")

However, the order of the record ID is non-sequential, so that I have no option but to try and figure out how to get it done with ADO or DAO (movenext, moveprevious).

I am wanting to calculate the differences between these dates.

Perhaps the Standard module in your attachment addresses this problem in its entirety and I've missed your explanation. I hope that's not the case.

DJkarl

05-15-2007, 02:06 PM

First off, thank you very much for the attachment DjKarl, a quick demonstration is worth a thousand explanations with programming, and so I appreciated it very much. Please bear with me, my post is a lot shorter than it looks!

I was actually referring to the records themselves, next record and previous record and how I would go about populating two controls on a form (dteNxtDate and dtePrevDate) with their values:

So that, for example,

1) when the first record in the date field of the bound table is 12/01/2006,

2) the second is 13/01/2006,

3) and the third is 14/01/2006,

whenever the second record is current, I would like my form to show the dteCurDate as 13/01/2006, dteNxtDate as 14/01/2006 and the dtePrevDate control to show 12/01/2006.

Had I a recordset with a sequential ID column, my problem would have been a lot easier to get around by placing a Dlookup statement in each control's control source:

dtePrevDate=DLookUp("[TheDate]","tblDates","[ID]=Forms![frmDates]![ID]-1")

dteNxtDate=DLookUp("[TheDate]","tblDates","[ID]=Forms![frmDates]![ID]+1")

However, the order of the record ID is non-sequential, so that I have no option but to try and figure out how to get it done with ADO or DAO (movenext, moveprevious).

I am wanting to calculate the differences between these dates.

Perhaps the Standard module in your attachment addresses this problem in its entirety and I've missed your explanation. I hope that's not the case.

Now I'm not sure what exactly you are trying to accomplish, can you post the database your working in?

Rich

05-15-2007, 02:14 PM

The Prev rec val function as posted works ok where the sequential id is out of sequence but the dates are sequential