Get Selected Record In Activedatasheet

[brianb]

Get Selected Record In Activedatasheet [solved]

I am trying to get a value from an ActiveDatasheet. The following code returns the correct record number but the data is from record 1 (or another record in the middle of the table when using other dbOpen.. arguments. Any help appreciated.

Dim rst As Recordset
Dim recno As Long
Dim Proj As String
'------------------
    Set rst = CurrentDb.OpenRecordset("YP17", dbOpenDynaset)
    recno = Screen.ActiveDatasheet.CurrentRecord
    '---------------------------------------------
    '- version 1
    'rst.Move 0
    '---------------------------------------------
    '- version 2
    rst.MoveLast
    rst.MoveFirst
    rst.Move recno
    '---------------------------------------------
    Proj = rst.Fields("Project Number")
    MsgBox ("CurrentRecord = " & recno & vbCr & Proj)
    rst.Close
End Sub

 

[Rich]

Why not just use Me.CurrentRecord and Me!Proj ??

 

[brianb]

Sorry, I probably did not make it clear.
The ActiveDatasheet is the standard one we get by double clicking the table in the Tables list - not a special form. The code is run from a button in the toolbar.

We are importing thousands of records into a table from .csv files with a VBA macro, and getting date format problems in some cases. The source file name is put into a field. The ultimate aim will be to automatically open files in Notepad so we can see the original text.

 

[Pat Hartman]

Try selecting the record after you move to it.
DoCmd.RunCommand acCmdSelectRecord

 

[brianb]

Selects the correct record in the datasheet and message shows the correct number - but returns data from another record.

 

[Pat Hartman]

You are probably going to need to use a form rather than opening the table directly. You can open the form in datasheet view so it looks like a table but it will have all the form's events and you can manipulate the recordset by using the RecordSetClone.

 

[brianb]

Thanks for the suggestions but I would rather try this way to begin with.

Is there a way of referring to a record in a table by record number ?

 

[brianb]

For the record, Rich gave me the right answer in disguise :

    Dim FileName As String
    FileName = Screen.ActiveDatasheet!csv_file

 

[Rich]

For the record, Rich gave me the right answer in disguise :

    Dim FileName As String
    FileName = Screen.ActiveDatasheet!csv_file

'twas heavily disguised

 

[Pat Hartman]

Is there a way of referring to a record in a table by record number ?

Relational database tables do not have record numbers. You don't access the table directly so record order is meaningless. The only way to control record order is with an Order By clause in your query. The correct way to select a specific record is to use its primary key.