Re: Error message on assignment line before rs.update


"JeanH" <JeanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5923B7D6-2087-41E9-AFC2-B4A08FE92A20@xxxxxxxxxxxxxxxx




"Ralph" wrote:


"JeanH" <JeanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AA7FBCC4-07D8-4E96-9563-7708264421B2@xxxxxxxxxxxxxxxx
' I am getting an error when I try to update the value of Posted to 1.

Set rsTenders = New ADODB.Recordset
Set rsTenders.ActiveConnection = Cnxn
rsTenders.CursorLocation = adUseClient
rsTenders.CursorType = adOpenKeyset
rsTenders.LockType = adLockBatchOptimistic

rsTenders.Open queryTenders, Cnxn

If Not rsTenders.EOF Then

rsTenders.MoveFirst

While Not rsTenders.EOF

ID = rsTenders!ID
Posted = rsTenders!Posted
PostTenderID = rsTenders!PostTenderID
RECID = rsTenders!RECID
OrderID = rsTenders!OrderID
TenderType = rsTenders!TenderType
TenderAmount = rsTenders!TenderAmount
CCNum = rsTenders!CCNum
CCExp = rsTenders!CCExp

'Process goes here

'This command errors out. If I comment it out, the update statement
works.

'What am I doing wrong?
rsTenders!Posted = 1

rsTenders.Update

rsTenders.MoveNext

Wend

End if

Thanks for any help on this. All I want to do is read through a
table,
process each record and then update a field showing that it was
processed.

Jean

What's the actual error?
Are you enumerating the Cnxn.Errors Collection?
What are the data types for Posted? The field "Posted"?

-ralph

The data types are as follows:

ID as Integer
Posted as Integer
PostTenderID as Integer
RECID as Integer
OrderID as Integer
TenderType as string
TenderAmount as currency
CCNum as string
CCExp as string

The actual message is:

Run-time error '-2147217887 (80040e21)':
Multiple-step operation generated errors. Check each status value.

I am not enumerating the errors collection.

Thanks for the reply.

Jean


First. Always enumerate the Errors Collection. Subsequent messages may
provide more information.
https://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx

I assume queryTenders is a SQL Statement? (I should have asked earlier).

The .Update method works by ADO creating (behind the scenes) its best guess
as to what an Update statement would look like based on the Query. This
error commonly occurs if due to database/table constraints or query criteria
an unambigous update statement can not be constructed.

It can also occur if one of the fields you are updating doesn't contain the
proper datatype. But it doesn't look like this is a problem in your case as
Strings and Integers are hard to mismatch. Are you sure Posted is an
"Integer" in the Table?

As a quick check write your own Update Query based on the new values and see
if it works as expected. Remember you are only changing one field - but ADO
is going to be using the entire query to build its criteria for updating
THAT record.

If the above doesn't help, post your query, the DDL for the table/s
involved, and the specific database.

hth
-ralph


.

Relevant Pages

  • Re: Error message on assignment line before rs.update
    ... TenderType as string ... as to what an Update statement would look like based on the Query. ...
    (microsoft.public.vb.database.ado)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: loop through records and run a function before update a field...head strom
    ... instead of putting the code in either a report or a form I would put the code behind a query as a user defined function used to populate a calculated field. ... AgainstPeople() ... Dim rl1 As String ... Dim RiskLevel As String ...
    (comp.databases.ms-access)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)