Recordset update is skipping every other record

[Ken]

The following code results in a recordset where every other record of
tblOne has [Invoice Printed]=True. The recordset count is correct but
one record is skipped; exactly half the records are updated to True.
Why is this occuring and how can I fix it? Using A2K/Win XP.

Thank you.

Code:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * from tblOne;"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.Filter = "InvoicePrinted=False"

While Not rst.EOF
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend

[HJ]

I am not 100% sure, but I believe you need to use rst.Edit first:

While Not rst.EOF
rst.Edit
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend

HJ

"Ken" <mo*****@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

The following code results in a recordset where every other record of
tblOne has [Invoice Printed]=True. The recordset count is correct but
one record is skipped; exactly half the records are updated to True.
Why is this occuring and how can I fix it? Using A2K/Win XP.

Thank you.

Code:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * from tblOne;"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.Filter = "InvoicePrinted=False"

While Not rst.EOF
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend

[Douglas J. Steele]

Off the top of my head, I'm not sure why it's skipping every other record
(the only thing that comes to mind is that the Update is causing the pointer
in the recordset to move, although that doesn't make sense to me).

However, I think you'd be much better off using an Update statement rather
than going through the recordset as you are:

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim strSQL As String

strSQL = "UPDATE tblOne SET InvoicePrinted = True WHERE InvoicePrinted =
False"

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = strSQL
cmd.Execute

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Ken" <mo*****@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

The following code results in a recordset where every other record of
tblOne has [Invoice Printed]=True. The recordset count is correct but
one record is skipped; exactly half the records are updated to True.
Why is this occuring and how can I fix it? Using A2K/Win XP.

Thank you.

Code:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * from tblOne;"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.Filter = "InvoicePrinted=False"

While Not rst.EOF
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend

[Pieter Linden]

mo*****@hotmail.com (Ken) wrote in message news:<2b**************************@posting.google. com>...

The following code results in a recordset where every other record of
tblOne has [Invoice Printed]=True. The recordset count is correct but
one record is skipped; exactly half the records are updated to True.
Why is this occuring and how can I fix it? Using A2K/Win XP.

Thank you.

Code:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * from tblOne;"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.Filter = "InvoicePrinted=False"

While Not rst.EOF
rst!InvoicePrinted = True
rst.Update
rst.MoveNext
Wend

seems like an awful lot of work for something that you could do with either:
a canned update query.
a command object in ADO.