Current Record in Datasheet view

[SimonPGreen]

Hi,

I have a form that displays all records in a table in a datasheet view. This is opened from another form bound to the same table so that a user can quickly select a record go back to the first form with the selected record current.

In a datasheet view is it possible to specify the record that is current i.e. the one with the arrow on the right hand side. At present the list form always opens with the first record current but I want to open with the selected record synchronised with the first form.

Appreciate any help.

Simon

 

[Remou]

You can use the Recordset of the datasheet, FindFirst and the Bookmark property.

 

[SimonPGreen]

Remou,

I think you misunderstand or I am missing something:

The first form has a record selected. In order to open the second datasheet form with the correct record selected the only way I can see is to use the DoCmd.GotoRecord with AcGoto and specify the offset. The problem I have is that in order to specify the offset I have to find some way of iterating through the recordset of the first form counting records until the correct one is found and then pass that value to the datasheet to use as the offset?

Can't seem to use the following to iterate through the recordset as I don't seem to be able to use strCriteria in the loop:

Dim rst as recordset
Set rst = me!Recordset
strCriteria = "PKContractref=" & Forms!Contracts!PKContractRef
Do until rst.eof
rst.findfirst strCriteria
counter = counter+1
loop

Any ideas?

Simon

 

[Remou]

I do not see why you need a loop. Perhaps:

Dim rst as recordset
Set rst = me.RecordsetClone
strCriteria = "PKContractref=" & Forms!Contracts!PKContractRef
rst.findfirst strCriteria
If Not rst.Nomatch Then
  Me.Bookmark=rst.Bookmark
Else
  'Not found
End If

 

[SimonPGreen]

Thanks Ramou.

Strange - I don't seem to have a findfirst or nomatch methods for a RecordsetClone object? I ma using Access 2003 and when intellisense drops down my options after typing rst. I can only see the find method.

Simon

 

[RoyVidar]

By the order of references (ADO is probably higher up in the list than DAO), it seems you've gotten an ADO recordset - I think the consensus is to recommend being explicit with recordset declarations;

[tt]Dim rst as DAO.recordset[/tt]

or bypass with

[tt]with me.RecordsetClone
.findfirst "PKContractref=" & Forms!Contracts!PKContractRef
If Not .Nomatch Then
Me.Bookmark = .Bookmark
else
' not found
end if
end with[/tt]

Roy-Vidar

 

[SimonPGreen]

Roy,

I have had a look through my references and only ADO is ticked.

I presume I have to tick Microsoft DAO 3.6 Object Library in order to have this available?

Simon

 

[SimonPGreen]

Guys,

I appreciate your persistance in this one - works a treat!

Thanks

Simon