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.

 

[brianb]

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

 

[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