Re: Update with Do Until rsTable.EOF allways leave one record "beh
- From: Ge <Ge@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 2 Aug 2008 09:30:00 -0700
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" wrote:
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
Ge wrote:
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200808/1
- References:
- Update with Do Until rsTable.EOF allways leave one record "behind"
- From: Ge
- Re: Update with Do Until rsTable.EOF allways leave one record "behind"
- From: Chris O'C via AccessMonster.com
- Update with Do Until rsTable.EOF allways leave one record "behind"
- Prev by Date: Re: Creating a User Object
- Next by Date: Re: Search a linked table
- Previous by thread: Re: Update with Do Until rsTable.EOF allways leave one record "beh
- Next by thread: Re: Update with Do Until rsTable.EOF allways leave one record "beh
- Index(es):
Relevant Pages
|
