Thow me a bone... dao recordset recorcount 2

[drkhelmt]

This one is bugging me.... I have this code:

    dim dbs as dao.recordset
    dim rst as dao.recordset
    so = InputBox("Enter the sale number.", "Sale Number")
    sqlst = "SELECT workAssign.workCoID " _
        & "FROM workType INNER JOIN (workCo INNER JOIN ((sale INNER JOIN saleItem ON sale.saleID = saleItem.saleID) INNER JOIN workAssign ON saleItem.siID = workAssign.siID) ON workCo.workCoID = workAssign.workCoID) ON workType.workTypeID = workCo.coTypeID " _
        & "WHERE (((sale.saleNo)=" & so & ") AND ((workType.workTypeID)=1));"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sqlst)
    MsgBox rst.RecordCount

This returns 1. When I wash that through a regular query to be:

SELECT workAssign.workCoID
FROM workType INNER JOIN (workCo INNER JOIN (sale INNER JOIN (saleItem INNER JOIN workAssign ON saleItem.siID = workAssign.siID) ON sale.saleID = saleItem.saleID) ON workCo.workCoID = workAssign.workCoID) ON workType.workTypeID = workCo.coTypeID
WHERE (((sale.saleNo)=204177) AND ((workType.workTypeID)=1));

This returns 3 records. Why would the first count 1 and the second return 3? The documentation on RecordCount seems to say that it would count the records, not count the distinct records?

In the end, I need to count the distinct records, so I guess everything is working the way I want, but I don't like it when I can't say why it is doing what I want.

Any help is appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[earthandfire]

Try:

rst.movelast
msgbox rst.recordcount
rst.movefirst

Hope this helps.

 

[Eupher]

Hi, drkhelmt,

I think you have a typo in your first variable declaration, I think you meant:

Dim dbs As DAO.[red]Database[/red]

But I'm assuming that was just a typo in your post, not in your actual code.

In a DAO recordset, RecordCount is not known until the recordset is fully populated. Until then, RecordCount will either be 0 (no records) or 1 (1 or more records). The recordset can be populated in several ways:

1) Automatically, through the use of certain keywords in the SQL that require the entire recordset to be evaluated, such as DISTINCT or ORDER BY (although this is not 100% reliable in my tests - I think the ORDER BY field's primary key status has a bearing)

2) Use the MoveLast then MoveFirst methods to traverse and populate the recordset before using RecordCount - most reliable.

Ken S.

 

[TheAceMan1]

How are ya drkhelmt . . . . .

Try this (corrections in [purple]purple[/purple]):

[blue]    Dim dbs As dao.[purple][b]Database[/b][/purple], rst As dao.Recordset
    [purple][b]Dim sqlst As String, so As String[/b][/purple]
    
    so = InputBox("Enter the sale number.", "Sale Number")
    sqlst = "SELECT workAssign.workCoID " & _
            "FROM workType " & _
            "INNER JOIN (workCo INNER JOIN ((sale " & _
            "INNER JOIN saleItem ON sale.saleID = saleItem.saleID) " & _
            "INNER JOIN workAssign " & _
            "ON saleItem.siID = workAssign.siID) " & _
            "ON workCo.workCoID = workAssign.workCoID) " & _
            "ON workType.workTypeID = workCo.coTypeID " & _
            "WHERE (((sale.saleNo)=" & [purple][b]Val([/b][/purple]so[purple][b])[/b][/purple] & ") AND " & _
                  "((workType.workTypeID)=1));"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sqlst)
    
    [purple][b]rst.MoveLast[/b][/purple]
    MsgBox rst.RecordCount[/blue]

See Ya! . . . . . .