Problem with moving thru recordset

[jjmclell]

I'm having an issue with a recordset object where Access seems to start in the middle of the recordset even when I use MoveFirst. The object's RecordCount property also returns 4 more records than are actually in the table (should be 321, returns 325) even when I first use the MoveLast method to ensure I'm accessing all of the records.

Sub test()


    Dim dbs As Object
    Dim mainTable As Recordset
    Dim testValue As String
        
    Set dbs = Application.CurrentDb
    Set mainTable = dbs.OpenRecordset("tbl_Main", dbOpenTable)
    
    mainTable.MoveFirst
    testValue = mainTable.Fields("partners").Value
    Debug.Print testValue
End Sub

The result from this code is the value for the 21st record in the table I'm trying to work with.

[ssanfu]

Strange that you are declaring dbs as an object.

What happens when you try this:

Sub test()

    Dim dbs As DAO.Database
    Dim mainTable As DAO.Recordset
    Dim testValue   'variant

    Set dbs = CurrentDb
    Set mainTable = dbs.OpenRecordset("tbl_Main", dbOpenTable)

    If mainTable.BOF And mainTable.EOF Then
        Debug.Print "No records"
    Else
        mainTable.MoveLast
        Debug.Print "Record count = " & mainTable.RecordCount

        mainTable.MoveFirst
        testValue = mainTable.Fields("partners")
        Debug.Print "partners = " & testValue
    End If
    
    'clean up
    mainTable.Close
    Set mainTable = Nothing
    Set dbs = Nothing
End Sub

[John_G]

You are creating a recordset from tbl_Main without specifying a sort order. In my experience, when you do that Access will use the PK order, so if "Partners" is not the PK, that is your problem.

Try using this:

Set mainTable = dbs.OpenRecordset("Select * from tbl_Main order by partners")


If you open the table from the Navigation pane, how many records does it have?

[orange]

Here is a good reference for Recordsets.

[rpeare]

If your recordset is more than a single record why is only the first record important, that seems a bit odd to me. I would be more inclined to make sure the SQL statement was pulling only the record needed to cut down on processing time, or alternately cycle through the recordset to pull the information you want from each record. I'd use ssnafu's code and add a loop in the ELSE portion of the code like:

dim sPartner as long ' this is assuming you are pulling the partner name as a string and not actually using the primary key
maintable.movelast
debug.print "record count = " & maintable.recordcount
maintable.movefirst

do while maintable.eof <> true
    spartner = maintable.fields("partner")
    debug.print "    Partner name = " & spartner
    maintable.movenext
loop

[jjmclell]

Strange that you are declaring dbs as an object.

What happens when you try this:

Sub test()

    Dim dbs As DAO.Database
    Dim mainTable As DAO.Recordset
    Dim testValue   'variant

    Set dbs = CurrentDb
    Set mainTable = dbs.OpenRecordset("tbl_Main", dbOpenTable)

    If mainTable.BOF And mainTable.EOF Then
        Debug.Print "No records"
    Else
        mainTable.MoveLast
        Debug.Print "Record count = " & mainTable.RecordCount

        mainTable.MoveFirst
        testValue = mainTable.Fields("partners")
        Debug.Print "partners = " & testValue
    End If
    
    'clean up
    mainTable.Close
    Set mainTable = Nothing
    Set dbs = Nothing
End Sub

I tried this code and was experiencing the same problems. I do believe I have figured out the aspect where the first record being returned is a random record in the table. I included this piece of code after setting the mainTable recordset:

mainTable.Index = "PrimaryKey"

This had the effect of actually returning the first record when using .MoveFirst. As to why 4 extra records were being returned when using .RecordCount I haven't figured out, but I copied the table and deleted the original and the correct number of records started being returned. It is a table that I regularly import to from Excel so maybe something strange happened in a past import.

[orange]

To open a recordset that is Ordered in some repeatable manner, use a query as the source of the recordset.

For example:

Dim sql1 as string
sql1 = "SELECT * from tbl_Main Order By YourImportantFieldHere;"

Then
....
Set dbs = CurrentDb
Set mainTable = dbs.OpenRecordset("sql1")

[ssanfu]

I was curious about the number of records returned - but it seems that there was possibly a little corruption.

OK... a table has NO inherent order. To have a sorted (ordered) list, you must use a query (as orange pointed out).
What field do you want to sort by?

Sub test_2()

    Dim dbs As DAO.Database
    Dim mainTable As DAO.Recordset
    Dim testValue   'variant
    Dim sSQL As String

    Set dbs = CurrentDb

    sSQL = "SELECT * from tbl_Main Order By ORDER_FIELD;" '< change "ORDER_FIELD" the the field name you want to sort (order) by
    Set mainTable = dbs.OpenRecordset(sSQL)

    If mainTable.BOF And mainTable.EOF Then
        Debug.Print "No records"
    Else
        mainTable.MoveLast
        Debug.Print "Record count = " & mainTable.RecordCount

        mainTable.MoveFirst
        testValue = mainTable.Fields("partners")
        Debug.Print "partners = " & testValue
    End If
    
    'clean up
    mainTable.Close
    Set mainTable = Nothing
    Set dbs = Nothing
End Sub

[jjmclell]

The SQL worked perfectly. Thanks to everyone for their help.