how to track to record position?

[titanium]

I need to know the record position in order to enable the moveNext and movePrevious button. However, it seems that the recordset.absolutePosition does not give an up-to-date position of my recordset.

I add watch to the debugger and track the value of recordset.absolutePosition it seems that it always had an value of adPosUnknown (why is so??).

How do I track my recordset position?

TIA.

[Wesam]

Hi,

Determining a record position depends on the nature of your RecordSet; is it sorted ?? does it read records as they are stored in the database ?? or are there more other properties??
AbsolutePosition can be of great help if you know how to use it. I can't provide advises as you didn't tell how the records are collected in the RecordSet,

if you couldn't manage to solve the problem, then reply to this topic with information about the RecordSet.

Regards,

Wesam.

[titanium]

I still couldn't get the .absolutePosition to work, neither can .BOF and .EOF.

All database and recordset are unsorted.

I'm using MS Assess 2000 for my database.

heres how I set my recordset.

In Module
-----------------------------------
Public gFindString As String
Public Const gConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\project\database\Project.mdb;"

In my forms
-----------------------------------
Global Declaration
Public adoConnection As ADODB.Connection
Public adoStudent As ADODB.Recordset

-----------------------------------
Public Sub adoSetup()

Dim sConnection As String

Set adoConnection = New ADODB.Connection
Set adoStudent = New ADODB.Recordset

adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\project\database\Project.mdb;"
adoConnection.Open

sConnection = "SELECT * FROM Student"
Set adoStudent = adoConnection.Execute(sConnection)

End Sub

-----------------------------------
Private Sub Form_Activate()

Call adoSetup

End Sub
-----------------------------------

Private Sub cmdButton_Click(Index As Integer)

Static vMyBookMark As Variant

With adoClass
Select Case Index
Case 0
.MoveFirst
Call updateButtons 'updateButtons will enable or disable the respective navigate command buttons

Case 1
.MovePrevious
Call updateButtons

Case 2
.MoveNext
Call updateButtons

Case 3
.MoveLast
Call updateButtons

End Sub

Public Sub updateButtons(Optional blockEm As Variant)
Select Case editStatus
Case nowStatic
If (lTotalRecords > 2) Then
If (.BOF) Or (.AbsolutePosition = 1) Then
navigateButtons ("0011")

'I had four command buttion << , < , >, >>
'navigateButtons will set the enable or disable the
'respective command button.
'fisrt 0 or 1 represent the button <<
'0 is disable and 1 is enable

ElseIf (.EOF) Or (.AbsolutePosition = lTotalRecords) Then
navigateButtons ("1100")
Else
navigateButtons ("1111")
End If
ElseIf (lTotalRecords = 2) Then
If (.BOF) Or (.AbsolutePosition = 1) Then
navigateButtons ("0010")
ElseIf (.EOF) Or (.AbsolutePosition = lTotalRecords) Then
navigateButtons ("0100")
Else
navigateButtons ("0000")
End If
ElseIf (lTotalRecords = 1) Then
navigateButtons ("0000")
Else
navigateButtons ("0000")
End If
End Sub

-------------------------------

it seems that my .BOF, .EOF and .AbsolutePosition does not shows the updated status of my recordset and therefore I can't update my command buttons accordingly... and sometimes end up with error message.

The debugger shows .absoulutePosition = adPosUnknown when I click on << (which will .moveFirst).

Why is this so?? I need the .BOF, .EOF and .absoultePosition to work properly.

[Wesam]

I think your code should be something like the following:

-------

In Module
-----------------------------------
Public gFindString As String
Public Const gConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\project\database\Project.mdb;"

' Here you place the global variables

Public adoConnection As ADODB.Connection
Public adoStudent As ADODB.Recordset

-----------------------------------
Public Sub adoSetup()

Dim sConnection As String

Set adoConnection = New ADODB.Connection
Set adoStudent = New ADODB.Recordset

adoConnection.ConnectionString = gConnectionString adoConnection.Open

sConnection = "SELECT * FROM Student"
Set adoStudent = adoConnection.Execute(sConnection)

End Sub

-----------------------------------
Private Sub Form_Load() ' I think you better use this event

adoSetup

End Sub
-----------------------------------

Private Sub cmdButton_Click(Index As Integer)

Static vMyBookMark As Variant

'With adoClass ' This variable exist only here!!

'updateButtons will enable or disable the respective navigate command buttons

Select Case Index
Case 0
adoStudent.MoveFirst
Case 1
adoStudent.MovePrevious
Case 2
adoStudent.MoveNext
Case 3
adoStudent.MoveLast
End Select

updateButtons

End Sub

Public Sub updateButtons(Optional blockEm As Variant)

Select Case editStatus ' ???

Case nowStatic '???

If adoStudent.RecordCount > 2 Then

' AbsolutePositions is 0-based, be carefull!!

If adoStudent.AbsolutePosition=0 Then
navigateButtons ("0011")
ElseIf AbsolutePosition = adoStudent.RecordCount-1 Then
navigateButtons ("1100")
Else
navigateButtons ("1111")
End If

ElseIf adoStudent.RecordCount = 2 Then

If adStudent.AbsolutePosition = 0 Then
navigateButtons ("0010")
ElseIf adoStudent.AbsolutePosition = _
adoStudent.RecordCount-1 Then
navigateButtons ("0100")
Else
navigateButtons ("0000")
End If

ElseIf adoStudent.RecordCount = 1 Then
navigateButtons ("0000")
Else
navigateButtons ("0000")
End If

End Sub

-------------------------------

Well, if it did not work this way, then it would be very strange .. try placing a (adoStudent.MoveFirst) every time you need to read information from adoStudent recordset.
I did not understand the way how your application works:
- First the (Activate) or (Load) event should call adoSetup.
- Well, then it seems that you have a control array of type
Button control. These buttons seem to be : <<,<,>,>>.
- I think every time the user presses a button, the button
index is passed to cmdButton_Click(), AND HERE YOU MOVE
THE CURRENT RECORD POINTER.
- If so, then what is updateButtons() for?? is it to
enable/disable button according to the state of the
Current Record ?? if so, you could've done so in the same
routine cmdButton_Click ???

Hope it works this way, please let me know about the results

Regards,

Wesam

[Clunietp]

What is your CursorType and Locktype for your recordset? (these should be properties of your data control, if you use one....)

[Elias]

In response to:

I need to know the record position in order to enable the moveNext and movePrevious button. However, it seems that the recordset.absolutePosition does not give an up-to-date position of my recordset.

I add watch to the debugger and track the value of recordset.absolutePosition it seems that it always had an value of adPosUnknown (why is so??).

How do I track my recordset position?

TIA.

Elias says:

It seems to me that your recordcount is not set correctly. The only way I know to ensure your recordcount property is set correctly is to use the .movelast method. From then on, your absoluteposition, recordcount, etc. should be accurate.

-Elias

[titanium]

thanks everybody,

I had managed to solve my problems by making my own global counter.

dim lPosition as long
lPosition = 1 'initialize to 1 when recordset is opened

....

.movefirst
lPosition = 1

.moveprevious
lposition = lposition -1

.movenext
lposition = lposition + 1

.movelast
lposition = .recordcount

it works fine now.

I had also to find out from
http://msdn.microsoft.com/library/techart/daotoadoupdate_topic5.htm#daotoadoupdate_topic5b

.absolutePosition cannot work may due to the following reason

1) ADO Recordset objects opened with the Execute method are always forward-only, read-only recordsets. If you need to be able to scroll or update data within the Recordset you must use the Recordset object Open method.

2) If the CursorLocation is not specified or is set to adUseServer, the AbsolutePosition property will return adUnknown (-1) because the Microsoft Jet Provider does not support retrieving this information.

----------------------------------------------------
to Wesam
sorry I didn't comment my code well...

' AbsolutePositions is 0-based, be carefull!!

With DAO the AbsolutePosition property is zero-based; the first record in the Recordset has an AbsolutePosition of 0. With ADO the AbsolutePosition property is one-based; the first record in the Recordset has an AbsolutePosition of 1.


I did not understand the way how your application works:
- First the (Activate) or (Load) event should call adoSetup.

my Form_Activate will call adoSetup to make the neccessary connection.


- Well, then it seems that you have a control array of type
Button control. These buttons seem to be : <<,<,>,>>.
yes, they are control array with <<,<,>,>>
- I think every time the user presses a button, the button
index is passed to cmdButton_Click(), AND HERE YOU MOVE
THE CURRENT RECORD POINTER.

you guess it right, thats how my code are suppose to work :)


- If so, then what is updateButtons() for?? is it to
enable/disable button according to the state of the
Current Record ?? if so, you could've done so in the same
routine cmdButton_Click ???
updateButtons() is indeed used to enable/disable button according to the state of the Current Record by sending a string of '0' and '1' to navigateButtons. Each '0' and '1' will enable the cmdButtons accordingly. As this is used by every cmd_button, instead of writing the code 4 times for <<,<,>,>>, I had put it in a procedure to save coding times.
----------------------------------------------------

To Clunietp,

What is your CursorType and Locktype for your recordset? (these should be properties of your data control, if you use one....)

I didn't use any CursorType, this might be the reason for .absolutePosition to fail.

----------------------------------------------------
Thank you guys. I really appreciate your help.

[Clunietp]

Yeah, I just looked at your code again, and you were not specifying a lock/cursor type. When you use the connection.execute method, it returns a read-only, forward only recordset. This would be why you didn't get a recordcount back, because you at least need a static recordset in order to get the recordcount.