Identefying the last record...

[Besoup]

Hey,
I am working with Access VBA and looping through a table. how would I get out of the loop on the last record?

Thanks

[RobDog888]

You can do a loop like this.

rs.MoveFirst
Do While rs.EOF = False
    'Do stuff
    rs.MoveNext
Loop

[Besoup]

ok I would have to dim rs to a recordset and set it to the table? Or do I have to do all that seeing the form was made from the table using the wizard?

[RobDog888]

Oops sorry I didnt realize that this was a table bound to your form.

Do While Me.CurrentRecord <= Me.MaxRecords
        'Do stuff
    Loop

Note: if you are not on the first records then you will not be iterating through the entire table.

HTH

[salvelinus]

If you leave the navigation buttons on the form, just click the >>| symbol down where it reads Record 1 of 650. That will take you to the last record.
But there's no guarantee that the data in a table will be sorted, so the last record may not be the one you think it would be. You could base your form on a query that has an ORDER BY clause to do that.
As an alternative to RobDog's method, if you just want to find the last record, you could do something like:

strSQL = "SELECT * FROM tblTable ORDER BY fldLName"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
   rs.MoveFirst
   rs.MoveLast
   MsgBox "Last record is: " & rs!fldFName & " " & rs!fldLname
Else
   MsgBox "No records found"
End If

[RobDog888]

Correct. But you could place sorting on the forms property. Then when you go to the last record using
the nav buttons it will be the last record according to the sorting. Conversly, if you had sorting on the
form, it would be different then the table so you wouldnt be on the last record. I think thats is also
what you said.

[Besoup]

Oops sorry I didnt realize that this was a table bound to your form.

Do While Me.CurrentRecord <= Me.MaxRecords
        'Do stuff
    Loop

Note: if you are not on the first records then you will not be iterating through the entire table.

HTH

Tried this and got the following error:

You have entereed an expression that has and invalid reference to the property MaxRecords.

[RobDog888]

Did you place the code in the Forms code module?

[Besoup]

Did you place the code in the Forms code module?

yeah it is returning a value when using me.CurrentRecord but me.MaxRecords is getting the error.

[RobDog888]

What version of Access are you running? Maybe its not supported in your version.
I am running 2003. You may need to do like salvelinus posted in order to get
the max number of records or like this to get the max records no matter what
the sorting is.

strSQL = "SELECT Count(*) As MaxRecs FROM tblTable;"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
   MsgBox "Max Records is: " & rs!MaxRecs
Else
   MsgBox "No records found"
End If

Yea, 8000 posts

[Besoup]

What version of Access are you running? Maybe its not supported in your version.
I am running 2003. You may need to do like salvelinus posted in order to get
the max number of records or like this to get the max records no matter what
the sorting is.

strSQL = "SELECT Count(*) As MaxRecs FROM tblTable;"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
   MsgBox "Max Records is: " & rs!MaxRecs
Else
   MsgBox "No records found"
End If

Yea, 8000 posts

I am using 2k3 also... strange maybe I will try updating will also try salvelinus' code.

grats on 8k