RecordSet only read the first record

[uoghk]

I have 2 records in vDayTran.
When I debug below coding, it will go thought the loop 2 times then exit.
When first jump into the loop, i get the values from the vDayTran first record.
But in the second loop, i get the same values as the vDayTran first record, not the values from the second record.

Set gblDb = CurrentDb
Set gblRst = gblDb.OpenRecordset("SELECT * FROM vDayTran", dbOpenDynaset, dbSeeChanges)
Do Until gblRst.EOF
    DoCmd.RunSQL ("Insert into Tranlog VALUES ......")
    gblRst.MoveNext
Loop
gblRst.Close

Set gblRst = Nothing
gblDb.Close

Set gblDb = Nothing

[Welshgasman]

Put a debug.print in the loop before the docmd and post back the results here.
Also show us a pic of the table vDayTran

[moke123]

Since you instantiate a db object you might as well use it instead of runsql.
As GasMan stated use debug.print as shown to see what it resolves to.

Dim strSql as string

Set gblDb = CurrentDb
Set gblRst = gblDb.OpenRecordset("SELECT * FROM vDayTran", dbOpenDynaset, dbSeeChanges)

Do Until gblRst.EOF

    strSql = "Insert into Tranlog VALUES ......"
    debug.print strSql

    gblDb.Execute strSql, DbFailOnError

    gblRst.MoveNext

Loop

gblRst.Close
Set gblRst = Nothing
gblDb.Close
Set gblDb = Nothing

[Micron]

There's no field list for the INSERT INTO ??

[Welshgasman]

There's no field list for the INSERT INTO ??

I took that to be just rubbish text to indicate what was meant to happen, concentrating on the changing value, which we still do not know?

[orange]

uoghk,

What are you trying to achieve? What would be a successful run of your code?

[ssanfu]

This is how I would write the snippet of code posted by the OP:

    Dim sSQL As String

    Set gblDb = CurrentDb

    Set gblRst = gblDb.OpenRecordset("SELECT * FROM vDayTran", dbOpenDynaset, dbSeeChanges)
    If Not gblRst.BOF And Not gblRst.EOF Then
        gblRst.MoveLast
        Debug.Print "Number of records = " & gblRst.RecordCount
        gblRst.MobeFirst

        Do Until gblRst.EOF
            sSQL = "Insert into Tranlog VALUES ......"
            Debug.Print sSQL
            gblRst.Execute sSQL, dbFailOnError + dbSeeChanges
            '           DoCmd.RunSQL ("Insert into Tranlog VALUES ......")
            gblRst.MoveNext
        Loop
    End If

    gblRst.Close

    Set gblRst = Nothing
    '   gblDb.Close

    Set gblDb = Nothing

(I added the lines in blue)


" gblDb.Close" should not be there.

The rule is
If you open it, close it
If you create it, destroy it

You created the recordset, so you destroy it ( Set gblRst = Nothing)
You opened the recordset, so you close the recordset ( blRst.Close)


You created the application object, so you destroy the application object ( Set gblDb = Nothing)
BUT you did not open the application object, therefore you do NOT close it ( ' gblDb.Close)

[uoghk]

thanks all, specially ssanfu.
by using debug.print, i make mistake in the SQL statement.
also thanks to learn to use Execute instead of RunSQL inside db object.