How to use recorset.moveprevious

S

shirley_kee

New member
Local time
Today, 10:34
Joined
Sep 15, 2006
Messages
6
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
 

Attachments

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

cheers..
 
boblarson said:
Don't post the same question in different areas on the forums. It makes people here not so happy.
Click to expand...

: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.
 
shirley_kee said:
ok... am so sorry guys...

cheers..
Click to expand...

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.
 
shirley_kee said:
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
Click to expand...

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

Code: 
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
 
DJkarl said:
Try using a Connection object and opening a recordset as opposed to using a command object IE

Code: 
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
Click to expand...

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.
 
Lilli said:
Dr Karl,

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

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.
Click to expand...

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.
 

Attachments

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.
Click to expand...

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.
 
Last edited:
Lilli said:
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.
Click to expand...

Now I'm not sure what exactly you are trying to accomplish, can you post the database your working in?
 
The Prev rec val function as posted works ok where the sequential id is out of sequence but the dates are sequential
 
You must log in or register to reply here.

Similar threads

MajP
Using A Collection Class to Raise Common Event
2
Replies
33
Views
634
Pat Hartman
P
C
Invalid use of Me.
Replies
8
Views
328
cheekybuddha
cheekybuddha
zelarra821
Solved Protect VBA Project Programmatically with SendKeys
Replies
12
Views
846
zelarra821
zelarra821
T
Attache to email from attachment Field
Replies
2
Views
308
theinviter
T
R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
521
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom