Update with Do Until rsTable.EOF allways leave one record "behind"

  • Thread starter Thread starter Ge
  • Start date Start date
G

Ge

Hi all.
In my lousy engelish, I will try to explain my problem.
In my form with a subform, I would like to update all record in the subform
with a value entered in a field (cboPakkevalg) in the main form. In the
subform I can select wich record to update with a checkbox(Behandles=True)
for each record. This value wil be reset to False during the update. (The
whole idea is to move several sub records from one main record to an another
main record by changing the walue in the "childfield" (main/sub)).

Anyway, I have this "On Click" sub, and the problem is; I can update all
selected records except the "last one". This occur if I have selected more
than one subrecord to move.

Eg; I will move 10 subrecords. I will have to click twice; First for the
record 1 - 9, and once more for the record 10

I see the message "EOF have been reached..." when the pointer are at the
..EOF record, but i seems that this record are not updated when the Me.Requery
are done. When 1 record are selected, I see the same message, but here the
record are uppdated....

Please look at my sub, and help me find what I am doing wrong... Thanks for
any help!!!!

*********

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err
Dim rsTabell As New ADODB.Recordset
Dim SQLStreng As String
SQLStreng = "SELECT Tabell.* FROM Kabel WHERE ((Tabell.Behandles) = True)"
rsTabell.Open SQLStreng, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
'rsTabell.MoveFirst
Do Until rsTabell.EOF
rsTabell![Pakke] = Me.cbopakkeValg.Value
rsTabell![Behandles] = False
rsTabell.Update
rsTabell.MoveNext
If rsTabell.EOF Then
MsgBox "EOF have been reached...", vbOKOnly, "OK"
End If
Loop 'While Not rsTabell.EOF

rsTabell.Close
Set rsTabell = Nothing

Me.Requery
Exit Sub


cmdOverfor_Err:
MsgBox "Error during update: " & Err.Description, vbCritical,
"System-feil"


End Sub
 
Not sure what the issue is, but be aware that it's almost always more
efficient to use an Update query than to loop through a recordset in VBA.
 
Yes, If I could get rid of the system message "2 of 2 record are updated"...,
or other system messages with docmd.runsql or docmd.openquerry, this would be
a nice solution.. I don't remeber how to supress those messages... :)

Regards,
Ge

Douglas J. Steele said:
Not sure what the issue is, but be aware that it's almost always more
efficient to use an Update query than to loop through a recordset in VBA.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ge said:
Hi all.
In my lousy engelish, I will try to explain my problem.
In my form with a subform, I would like to update all record in the
subform
with a value entered in a field (cboPakkevalg) in the main form. In the
subform I can select wich record to update with a checkbox(Behandles=True)
for each record. This value wil be reset to False during the update. (The
whole idea is to move several sub records from one main record to an
another
main record by changing the walue in the "childfield" (main/sub)).

Anyway, I have this "On Click" sub, and the problem is; I can update all
selected records except the "last one". This occur if I have selected more
than one subrecord to move.

Eg; I will move 10 subrecords. I will have to click twice; First for the
record 1 - 9, and once more for the record 10

I see the message "EOF have been reached..." when the pointer are at the
.EOF record, but i seems that this record are not updated when the
Me.Requery
are done. When 1 record are selected, I see the same message, but here the
record are uppdated....

Please look at my sub, and help me find what I am doing wrong... Thanks
for
any help!!!!

*********

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err
Dim rsTabell As New ADODB.Recordset
Dim SQLStreng As String
SQLStreng = "SELECT Tabell.* FROM Kabel WHERE ((Tabell.Behandles) =
True)"
rsTabell.Open SQLStreng, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
'rsTabell.MoveFirst
Do Until rsTabell.EOF
rsTabell![Pakke] = Me.cbopakkeValg.Value
rsTabell![Behandles] = False
rsTabell.Update
rsTabell.MoveNext
If rsTabell.EOF Then
MsgBox "EOF have been reached...", vbOKOnly, "OK"
End If
Loop 'While Not rsTabell.EOF

rsTabell.Close
Set rsTabell = Nothing

Me.Requery
Exit Sub


cmdOverfor_Err:
MsgBox "Error during update: " & Err.Description, vbCritical,
"System-feil"


End Sub
Click to expand...
Click to expand...
 
Ge said:
Yes, If I could get rid of the system message "2 of 2 record are
updated"..., or other system messages with docmd.runsql or
docmd.openquerry, this would be a nice solution.. I don't remeber how
to supress those messages... :)
Click to expand...

Don't use either of those. Use...

CurrentDB.Execute "QueryName", dbFailOnError
 
1. The messages can be turned off using .SetWarnings as in...
DoCmd.SetWarnings False
DoCmd.RunSQL
DoCmd.SetWarnings True
-Note how I immediate turn the warnings back on.

2. You can also use the the .Execute method of a Database object as in
Set db = dbEngine(0)(0)
db.Execute strSQL, dbFailOnError

-Note that I didn't use CurrentDB() and specified dbFailOnError.
Both are
necessary if you want to check the number of records affected via
db.RecordsAffected.
-This also allows you to encapsulate the action in a Transaction which
will allow you to undo changes if determine that its neccessary.

3. If you're moving child records from one parent to another, you'll
need to
figure out how to lock out users from doing the same thing at the
same
time. In a multi-user environment, if John selects 4 records to
move to
a new parent and Mary simultaneously selects 2 others to move to a
different parent and clicks transfer, all 6 will be moved to the
new parent.
Mary may or may not pickup that 4 records were moved over by mistake
or she may think that there's a bug. John will think theres a bug.
At any
rate, both will give you call. Depending on how things are setup,
you may
or may not be able to figure out where the records when and where
they
belong.

4. Unfortunately, I can not give you a precise dissertation as to how
the two
treat .EOF. but I can tell you that I always use WHILE NOT .EOF and all the
records are touched. I can tell you that DO UNTIL runs code and then asks the
question. WHILE asks the question first and then runs the code.

Chris O'C via AccessMonster.com said:
Why are you updating each record one by one? You should use an update query.
Your query isn't right. It names two different tables but has no join. Your
update query is going to be something like the following, but you need to fix
the query with the right table names.

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err

Dim SQLStreng As String
SQLStreng = "UPDATE Kabel " _
& "SET Pakke = " & Me.cbopakkeValg.Value _
& ", Behandles = False " _
& "WHERE Behandles = True"
CurrentDb.Execute SQLStreng, dbFailOnError
Me.Requery

Exit Sub

cmdOverfor_Err:
MsgBox Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Chris
Microsoft MVP

Hi all.
In my lousy engelish, I will try to explain my problem.
In my form with a subform, I would like to update all record in the subform
with a value entered in a field (cboPakkevalg) in the main form. In the
subform I can select wich record to update with a checkbox(Behandles=True)
for each record. This value wil be reset to False during the update. (The
whole idea is to move several sub records from one main record to an another
main record by changing the walue in the "childfield" (main/sub)).

Anyway, I have this "On Click" sub, and the problem is; I can update all
selected records except the "last one". This occur if I have selected more
than one subrecord to move.

Eg; I will move 10 subrecords. I will have to click twice; First for the
record 1 - 9, and once more for the record 10

I see the message "EOF have been reached..." when the pointer are at the
.EOF record, but i seems that this record are not updated when the Me.Requery
are done. When 1 record are selected, I see the same message, but here the
record are uppdated....

Please look at my sub, and help me find what I am doing wrong... Thanks for
any help!!!!

*********

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err
Dim rsTabell As New ADODB.Recordset
Dim SQLStreng As String
SQLStreng = "SELECT Tabell.* FROM Kabel WHERE ((Tabell.Behandles) = True)"
rsTabell.Open SQLStreng, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
'rsTabell.MoveFirst
Do Until rsTabell.EOF
rsTabell![Pakke] = Me.cbopakkeValg.Value
rsTabell![Behandles] = False
rsTabell.Update
rsTabell.MoveNext
If rsTabell.EOF Then
MsgBox "EOF have been reached...", vbOKOnly, "OK"
End If
Loop 'While Not rsTabell.EOF

rsTabell.Close
Set rsTabell = Nothing

Me.Requery
Exit Sub

cmdOverfor_Err:
MsgBox "Error during update: " & Err.Description, vbCritical,
"System-feil"


End Sub
Click to expand...
Click to expand...
 
Hi again.
First; The text and names wich where used - there where som typofault from
my side. Sorry.

So, when I corrected the text according to my system I put the text:
SQLStreng = "UPDATE Kabel " _
& "SET Trpakke = " & Me.cboTrekkepakkeValg.Value _
& ", Behandles = False " _
& "WHERE Behandles = True"

When running the SQLString with the CurrentDB.Execute, I've got the
errormessage
3075, Syntax error (missing operator) in query expression '01.FIFI.02'

The text '01.FFI.02' is the correct value of the cboTrekkePakkeValg -
without the apostrophes '. I may have 'staring me blind', but I can't find
any misspelling.

Then, I created an Update Query in Querry designer. When I run this manually
from the "query list", It runs perefctly. But when I run the query 2nd time,
it skips one of the records. I created two update query's - one for update
the value in the kabel.trpakke field based on kabel.behandles = True, and one
wich updated the kabel.behandles to False, where the value where True.
They both work perfectly once, but I had to close the form and reopen it
before I used the query(s) againg. If not, the query(s) left out one of the
records.

OK, just for fun, I pute the two update querys in a command like:
CurrentDb.Execute "uqr_OverforTrekkepk", dbFailOnError
CurrentDb.Execute "uqr_OverforTrekkepk_Resett", dbFailOnError

This gave me the errormessage:
3061, Too few parameters, Expected 1 for the first query. The second query
run fine the first time, the second time it left out a record, until I
"restarted" the form.

The SQL-View if the two querys are:
UPDATE Kabel SET Kabel.Trpakke = Forms!Kabel_register!cboTrekkepakkeValg
WHERE (((Kabel.Behandles)=True));

UPDATE Kabel SET Kabel.Behandles = False
WHERE (((Kabel.Behandles)=True));

I copied the Sql-view for the first query to the SQLStreng, and it gave med
the error message 3061 again. The I changed the text Forms!Kabel_register....
to the & Me.cboTrekkepakkevalg surrounded with "", and that gave med the
3075 error message againg.

So now, what's next? I am realy stuck here, and I can't undestand what I am
doing wrong, or what makeing this problem...

Regards,
Ge.

Chris O'C via AccessMonster.com said:
Why are you updating each record one by one? You should use an update query.
Your query isn't right. It names two different tables but has no join. Your
update query is going to be something like the following, but you need to fix
the query with the right table names.

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err

Dim SQLStreng As String
SQLStreng = "UPDATE Kabel " _
& "SET Pakke = " & Me.cbopakkeValg.Value _
& ", Behandles = False " _
& "WHERE Behandles = True"
CurrentDb.Execute SQLStreng, dbFailOnError
Me.Requery

Exit Sub

cmdOverfor_Err:
MsgBox Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Chris
Microsoft MVP

Hi all.
In my lousy engelish, I will try to explain my problem.
In my form with a subform, I would like to update all record in the subform
with a value entered in a field (cboPakkevalg) in the main form. In the
subform I can select wich record to update with a checkbox(Behandles=True)
for each record. This value wil be reset to False during the update. (The
whole idea is to move several sub records from one main record to an another
main record by changing the walue in the "childfield" (main/sub)).

Anyway, I have this "On Click" sub, and the problem is; I can update all
selected records except the "last one". This occur if I have selected more
than one subrecord to move.

Eg; I will move 10 subrecords. I will have to click twice; First for the
record 1 - 9, and once more for the record 10

I see the message "EOF have been reached..." when the pointer are at the
.EOF record, but i seems that this record are not updated when the Me.Requery
are done. When 1 record are selected, I see the same message, but here the
record are uppdated....

Please look at my sub, and help me find what I am doing wrong... Thanks for
any help!!!!

*********

Private Sub cmd1_Click()
On Error GoTo cmdOverfor_Err
Dim rsTabell As New ADODB.Recordset
Dim SQLStreng As String
SQLStreng = "SELECT Tabell.* FROM Kabel WHERE ((Tabell.Behandles) = True)"
rsTabell.Open SQLStreng, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
'rsTabell.MoveFirst
Do Until rsTabell.EOF
rsTabell![Pakke] = Me.cbopakkeValg.Value
rsTabell![Behandles] = False
rsTabell.Update
rsTabell.MoveNext
If rsTabell.EOF Then
MsgBox "EOF have been reached...", vbOKOnly, "OK"
End If
Loop 'While Not rsTabell.EOF

rsTabell.Close
Set rsTabell = Nothing

Me.Requery
Exit Sub

cmdOverfor_Err:
MsgBox "Error during update: " & Err.Description, vbCritical,
"System-feil"


End Sub
Click to expand...
Click to expand...
 
You need text delimiters in your SQL string when you incorporate the
value. Right now the SQL engine is trying to figure out if 01.FFI.02 is
a field name, a reference to an object of some type, etc. Of course, it
can't so -ERROR ERROR ERROR.

Try

SQLStreng = "UPDATE Kabel " _
& "SET Trpakke = '" & Me.cboTrekkepakkeValg.Value & "'"_
& ", Behandles = False " _
& "WHERE Behandles = True"

Alternative is to use quote marks as in

SQLStreng = "UPDATE Kabel " _
& "SET Trpakke = """ & Me.cboTrekkepakkeValg.Value & """"_
& ", Behandles = False " _
& "WHERE Behandles = True"

It takes two consecutive quotes in a row when building a string to end
up with one quote in the constructed string.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hi there.

Your alternative 1 works great - I mean I don't get any error messages.

But as I wrote before, there are one more problem;
The Update don't find all records - It's alwas left one record behind.

I have created a new simple form. This form have a standard query as a
recordsource. On the form I have a unbound combobox (cboTrekkepakkevalg),
wich i can select text values (eg. 01.FIFI.02).
In the record/detail section I have a bounded checkboks (Behandles) wich I
clik on for select / deselct wich record who shal be updated.

I have a button who have the OnClick SUB wich includes the Update SQL.

If I click on the checkbox in 4 rows, only three are updated. When I click
the "Update button" again, the last row/record are also updated.

Why this is happening is way beond my imagination, specially because when I
click again, the last record are updated.

In this movierelease days, maybe this is something for Agent Mulder &
Scully??? :-)

Anyway, for now I use a dirty workaround; I am running the currentDB.execute
twice with a Me.Requery between them, and this works for now....

Regards, Ge
 
Ge said:
Hi there.

Your alternative 1 works great - I mean I don't get any error
messages.

But as I wrote before, there are one more problem;
The Update don't find all records - It's alwas left one record
behind.

I have created a new simple form. This form have a standard query as a
recordsource. On the form I have a unbound combobox
(cboTrekkepakkevalg), wich i can select text values (eg. 01.FIFI.02).
In the record/detail section I have a bounded checkboks (Behandles)
wich I clik on for select / deselct wich record who shal be updated.

I have a button who have the OnClick SUB wich includes the Update SQL.
Click to expand...

The last record you set the CheckBox on is not saved when you run the query.
Include a line of code that saves the record first...

Me.Dirty = False
 
Rick - Does .EOF physically occur after the last record or does it physically
occur on the last record? I'm curious if .EOF indicates that we've move past
the last record or if it indicates that we're on the last record?

David H
 
Ooooohhh my........
If You knew how many hours I have spent in the last days...

The Me.Dirty = False in the beginning of the procedure OnClick solved it!
(Maybe I schould put it in the On Click in the checkboks?)
Thanks al LOT! And also thanks to all other who have participated in this
subject - I have learned som tricks...

Btw:, I have to say, I have several books regarding access, (developing,
complete refernce, bible, vba programming...) and so on, and all are telling
me to use recordset looping, and not a word about Your solution
curentDB.Execute, me.dirty and so on... May be there are some other books I
have missed :)

Have a nice weekend, all!

Regards, Ge.
 
dch3 said:
Rick - Does .EOF physically occur after the last record or does it
physically occur on the last record? I'm curious if .EOF indicates
that we've move past the last record or if it indicates that we're on
the last record?
Click to expand...

EOF is after the last record.
 
Back
Top