AbsolutePosition

[vlitim]

I am tryimg to open a connecting and then display the record where id = mainID. I can't do it in the SQL because then the adoPrimaryRS.MoveLast etc doesn't work??????

help please??

Set adoPrimaryRS = New Recordset

adoPrimaryRS.Open "select id,description,date,days,hours,mins,staffNo,Department,periodNo from Maintenance", db, adOpenStatic, adLockOptimistic

adoPrimaryRS.AbsolutePosition = MainID

 

[mohansekar]

Hi,

You should set the Recordset.cursorlocation, cursortype before using. Only if the cursor type is Dynamic then only Movefirst etc., will work

bye

 

[vlitim]

The problem is that it does go to a record but not the correct one.

if MainID = 100 it goes to the 100th record, how do I make it go go the record where the id [field] = 100

cheers

 

[woyler]

You can filter the recordset:
adoPrimaryRS.Filter = "ID=" & MainID

 

[vlitim]

but then the next and back don't work because there is only one record that matches

 

[woyler]

After you filter, you can capture the cursor position, then clear the filter. You will then know what position the record you were looking for is, and have full navigation of the recordset

 

[vlitim]

Sounds like what I am after, though due to the fact that I know nothing.......................could you give me an example of this, as it would help me greatly!

Cheers mate

 

[woyler]

'OPen the recordset

Dim intAbsPos as Integer

adoPrimaryRS.Filter = "ID=" & MainID
intAbsPos = adoPrimaryRS.AbsolutePosition
adoPrimaryRS.Filter = adFilterNone
adoPrimaryRS.movenext

'The variable intAbsPos now holds the position of the record you were looking for and you have your full recorset back after you set the filter property to adFilterNone

 

[vlitim]

The problem is I always get intabsPos = 1 no matter what the MainID is????


rowIndex = FrmShowMaintenance.MSHFlexGrid1.RowSel
colIndex = FrmShowMaintenance.MSHFlexGrid1.ColSel
MainID = FrmShowMaintenance.MSHFlexGrid1.TextMatrix(rowIndex, colIndex)
Dim intAbsPos As Integer

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=timVB;uid=;pwd=;database=vli;"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select id,description,date,days,hours,mins,staffNo,Department,periodNo from Maintenance", db, adOpenStatic, adLockOptimistic

adoPrimaryRS.Filter = "ID=" & MainID
intAbsPos = adoPrimaryRS.AbsolutePosition
adoPrimaryRS.Filter = adFilterNone
adoPrimaryRS.MoveNext

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtfields
Set oText.DataSource = adoPrimaryRS
Next

mbDataChanged = False

 

[woyler]

Is the 'ID' field that we are filtering on a numeric data type, as well as the MainID variable? If so try:


rowIndex = FrmShowMaintenance.MSHFlexGrid1.RowSel
colIndex = FrmShowMaintenance.MSHFlexGrid1.ColSel
MainID = FrmShowMaintenance.MSHFlexGrid1.TextMatrix(rowIndex, colIndex)

Dim intAbsPos As Integer

Dim db As ADODB.Connection
dim adoPrimaryRS As ADODB.Recordset
Dim sSQL as String
Set db = createObject("ADODB.Connection&quot
Set adoPrimaryRS = CreateObject("ADODB.Recordset&quot

db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=timVB;uid=;pwd=;database=vli;"

sSQL = "SELECT id,description,date,days,hours,mins,
staffNo,Department,periodNo FROM Maintenance"

adoPrimaryRS.Open sSQL,db, adOpenKeyset, adLockOptimistic, adCmdText And adExecuteNoRecords

adoPrimaryRS.Filter = "ID=" & MainID
intAbsPos = adoPrimaryRS.AbsolutePosition
adoPrimaryRS.Filter = adFilterNone
adoPrimaryRS.MoveNext

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtfields
Set oText.DataSource = adoPrimaryRS
Next

 

[strongm]

You should be able to do a recordset.find. Something along the lines of:

adoPrimaryRS.Find "[ID] = 100"

 

[vlitim]

Cheers for all the help, I used the .find and it works perfectly!