By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

Recordset update is skipping every other record

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.