Do While loop question

[jbehrne]

Hey everybody,

I have question about do while loops. I am currently running a loop to check for values in a table using a DAO recordset against another DAO recordset (See code below). However, I am receiving some problematic returns. On some of the matches a count of 1 is returned. Others are returning a count of zero. However if a count of zero is returned the Invset still returns values... If one value is returned the Do While Loop should process data and return a correct count of 1. If a count of more than 1 is returned then the correct number of matches is returned. However, if no values are found then the default value of count should return a zero - but why am I still getting Invset field values in the message box? Any ideas or am I just going crazy...

Thanks,

jbehrne



'recset is a DAO recordset passed to the function
'Invset is a DAO recordset
'sqlString is a string
'count is an integer

count = 0

sqlString = "SELECT * FROM InfoTable WHere InfoTable.Amt = " & recset![Amount] & " InfoTable.InvoiceNumber = '" & recset![Inv] & "'"

Set Invset = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges, dbOptimistic)

If Invset.EOF = True Then
Invset.Close
Exit function
Else
Invset.MoveFirst
Do While Invset.EOF = False
count = count + 1
Invset.MoveNext
Loop
End If

MsgBox "Returned " & count & " matching records from InfoTable." & vbcrlf & "Amount: " & Invset![Amount] & " and Invoice Number: " & Invset![Inv]

Invset.close



If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Frink]

Hallo,

You have an operator (AND?) missing from sqlString, before InfoTable.InvoiceNumber.

- Frink

 

[RickSpr]

Frink's observation is true, but I don't think that's causing your problem.

You probably have a race condition. When you first open a recordset, it hasn't been fully populated. Access hands back the first record (if there are any) and then asynchronously continues loading the remaining records while your code runs. (This is done so that you can display information to the user without a long delay for a large recordset.)

Until the recordset is fully loaded, EOF will become True as soon as you've exhausted however many records have been loaded so far. (Also, the RecordCount property will keep changing until the load has been completed.) On a local database, it's usually not a problem because the load happens very quickly, but with a network back end database or especially a database server, the loading can be slower, and your code may run too fast for it to keep up.

To ensure that all the records have been loaded before you start testing for EOF, simply call the MoveLast method, then call MoveFirst to position back to the start. Note that if your server response is slow and your recordset is large, this WILL cause a noticeable delay.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Frink]

Hallo,

Rick, I've never known the race condition you mention. Is that the way MS Access behaves? Or is it just because of the options? I'm just wondering how much code I have written could be affected by this.

- Frink

 

[SonOfEmidec1100]

As you only need the count of records why not just select the count of rows, and change the msg ?

sqlString = "SELECT count(*) AS MyCount FROM InfoTable WHere InfoTable.Amt = " &  recset![Amount] & " AND InfoTable.InvoiceNumber = '" & recset![Inv] & "'"

Set Invset = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges, dbOptimistic)

MsgBox "Returned " & InvSet!MyCount & " matching records from InfoTable." & vbcrlf & "Amount: " & recset![Amount] & " and Invoice Number: " & recset![Inv]

Invset.close

 

[PHV]

Or simply a DCount function call.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[citychap26]

Hi...

If you simply want to just count the number of records that match your criteria I would simply execute the following:

Sub tblCount()

Dim qry As QueryDef
Dim db As Database
Dim sSQL As String
Dim rst As Recordset

Set db = CurrentDb()
sSQL = "select count(*) from tblMain"
Set qry = db.CreateQueryDef("", sSQL)
Set rst = qry.OpenRecordset
MsgBox rst.Fields(0)

End Sub

Or something like that ....

Cheers

SK

 

[wizarddrummer]

Hi,
here are some code fragments that might help you

Private Sub UpdateZipCodes()
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
    
    strSQL = "SELECT tblZipCodes.ZipCode, tblZipCodes.State, tblZipCodes.City, tblZipCodes.County FROM tblZipCodes;"


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    On Error Resume Next
    With rst
        .MoveLast 'populate the record set
        'you cannot get an accurate record count if you don't do this
        If Err = 3021 Or .RecordCount = 0 Then
            'no records found
            MsgBox "no records found"
            dbs.Close
            rst.Close
            Set dbs = Nothing
            Set rst = Nothing
            Exit Sub
        End If
        .MoveFirst
        On Error GoTo Err_UpdateZipCodes
        Do While Not .EOF
            .Edit
            !City = LowerThenCapFirstOfEveryWord(!City)
            .Update
            .MoveNext
        Loop
    End With

Exit_UpdateZipCodes
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub
Err_UpdateZipCodes
    MsgBox Err.Description
    Resume Exit_UpdateZipCodes
    
End Sub

Private Sub UpdateButtonsFromTable(inBegDate As Date, inEndDate As Date)
Dim qdf As QueryDef
Dim dbs As Database
Dim rst As Recordset

    'Set database variable to current database.
    Set dbs = CurrentDb
    'Open QueryDef object.
    Set qdf = dbs.QueryDefs("BlockedDatesParamQry")
    ' Set parameters for query
    qdf.Parameters("begDate") = inBegDate
    qdf.Parameters("endDate") = inEndDate
    Set rst = qdf.OpenRecordset()
    On Error Resume Next
    With rst
        .MoveLast
        If Err = 3021 Or .RecordCount = 0 Then
            'there are no records
        Else
            .MoveFirst
            Do While Not .EOF
                Me(!Location).Value = True
                Me(!Location).ForeColor = vbDarkRed
                .MoveNext
            Loop
        End If
    End With
    qdf.Close
    rst.Close
    dbs.Close
    Set qdf = Nothing
    Set dbs = Nothing

End Sub

Ever wonder why when you do a large query in MS access it sometimes take a second or so for the number of records to show up. well they're doing the same thing; Populating the record set.

You can do the same thing with listboxes and dropdown boxes. When you do that all of the selections are available and you don't have any problems scrolling

regards,
wizzie

 

[RickSpr]

Frink,

Actually I'm not certain about EOF becoming True prematurely. Well, actually, I think that was wrong. I was pretty tired when I wrote that and wasn't thinking clearly.

But the RecordCount property definitely doesn't settle down until the asynchronous loading is finished. If you need an accurate recordcount, you definitely have to MoveLast first.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jbehrne]

Hey Guys,

Thanks for all the responses... It has given me some food for thought. And I did apparently leave a 'AND' out of the sql query. However, before I try any code modifcation I have a quick question...

RickSpr - I read what you wrote about the race condition. Is this true even if the recordset returned is only 2 or 3 records max? I am passing the db frontend records from SQL Server 7.5. However, I have never had a problem like this before using SQL Server as the backend db. At any rate I value your input. Thanks,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[RickSpr]

JBehrne,

My original post was inaccurate about EOF being true prematurely. I corrected the error in my second post.

The code you illustrated isn't sensitive to the race condition I was talking about, because it doesn't depend on the RecordCount property. As far as I know, it's only the RecordCount property that can be inaccurate while the query is still executing. When you test the EOF property, as your code does, I think you're safe.

If you're asking whether RecordCount is unreliable unless you do a MoveLast (or read until EOF is true), the answer is an unqualified "Yes". This is documented in the help file topic for the RecordCount property.

Consider the InfoTable query you illustrated. Note that you're restricting on the invoice number and amount, selecting all columns, and don't have an ORDER BY clause. If the invoice number isn't indexed (I assume the amount isn't indexed, either), SQL Server will have to do a table scan (SLOW!) to find the rows. With no ORDER BY clause, it doesn't have to collect all the result rows in order to sort them before sending any back, so it might send records back as they're found. So you might have a slow query that sends records back with relatively long delays between them. The fact that you're retrieving all columns contributes to this because it means each record probably fills several buffers. When the second record is found, it's enough to push the end of the first record back to the client. When that first record is received by the client, your code resumes executing--but part of the second record may still not have been received, and in any case SQL Server may still be scanning for a third record. RecordCount only reflects the number of rows which have been received so far (1) at this time.

But, like I said, you're not dependent on RecordCount so I was wrong in my first post. Your code should get an accurate record count. I would still limit it to selecting just the columns you need, however, and if possible I'd ensure that SQL Server is indexing the invoice number.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[SiberBob]

[blue]wizarddrummer:[/blue]

I have tried using part of your code from above (adapted for my needs):

    With RS
        Do While Not .EOF
            .MoveFirst
            .Edit
            !Rotate = True
            .Update
            .MoveNext
        Loop

but I get "method or data member not found" with the

.Edit

line highlighted. Is there a specific reference needed for this?

Thanks!

[blue]RickSpr:[/blue] Good food for thought - Thanks!

Bob

 

[PHV]

Edit is a method of a [highlight]DAO[/highlight].Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244