Populating Table Fields via Recordset Loop 1

[grinnz]

I'm just again getting back into Access after nearly six years and unfortunately starting from square one.


Problem: The recordset loop only populates the first record of the table although the code loops properly. Using the immediate window it seems that the recordset record is not stepping.

Option Compare Database
Option Explicit

Private Sub btnFill_Click()

Dim dbs As Database, rst As DAO.Recordset

'Open Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySongsDB")

'Fill Records
With rst
    .MoveFirst
    Do While Not .EOF
        With Me
            .sngSeconds = CalculationHere
            .txtArtist = CalculationHere
            .txtAlbum = CalculationHere
            .txtSong = CalculationHere
        End With
        .MoveNext
    Loop
End With

'Release memory & system resources from object variables
rst.Close
Set dbs = Nothing

End Sub

The query is simply the entire database and the form used also includes as visible all of the necessary table fields to which it is bound.


Your help is certainly appreciated and will help me to again start my Access adventure. Damn, what fun starting over!


~ grinnz ~

 

[Steve3110]

looking at the code, it is only ever going to populate the form with one record. It might run through all the records but each time it is going to replace the data on the form.

What exactly are you trying to do?

 

[Golom]

This block of code

With Me
   .sngSeconds = CalculationHere
   .txtArtist = CalculationHere
   .txtAlbum = CalculationHere
   .txtSong = CalculationHere
End With

updates controls on the form ... not records in the recordset. You would need something like

With rst
    .MoveFirst
    Do While Not .EOF
        .Edit
        !Seconds = CalculationHere
        !Artist = CalculationHere
        !Album = CalculationHere
        !Song = CalculationHere
        .Update
        .MoveNext
    Loop
End With

where Seconds, Artist, Album and Song are fields in the recordset "rst".

 

[grinnz]

A brief word of thanks to both of you for your replies. Although I'm sure that I will return in the future, my current problem has been solved.


CheerZ!

~ grinnz ~

Listen to me online every Tue/Thu 6-9pm Eastern

http://www.temptationradio.net