[RESOLVED] Recordset not updating

[Pasvorto]

I have a routine that populates a dataset (SQL SERVER 2000). Then, it goes back to delete duplicates.

The code that populates the database runs fine. The code that looks for sduplicates give me a [3251] Current record Does Not Support Updatign message. As mentioned, it does support updating, as the previous routines was able to add records.

So here if the code up the line where the rrror occurs (at the rs1.update command):

'===========================
Set rs1 = New ADODB.Recordset
'===========================
sql = "SELECT * FROM TMPCOMPNEEDED ORDER BY [UNPRTD],[LINE],[LOT],[ONHAND]"
rs1.Open sql, CnxnTempSQL, adOpenKeyset, adLockOptimistic, adCmdText
If Not rs1.BOF And Not rs1.EOF Then
    Do
        If UNPRTD = "" Then
            UNPRTD = rs1![UNPRTD]
            LINE = rs1![LINE]
            JOB = rs1![JOB]
            REMBAL = rs1![REMBAL]
            ONHAND = rs1![ONHAND]
            LOT = rs1![LOT]
        Else
            If Trim(rs1![UNPRTD]) = Trim(UNPRTD) And rs1![LINE] = LINE And rs1![LOT] = LOT And rs1![ONHAND] = ONHAND Then
                rs1![DROPTHIS] = "X"
                rs1.Update
            Else

[djwk]

I've never used ADODB for SQL but in all my applications to update an MS Access database it would require the following:

rs1.Edit
rs1![DROPTHIS] = "X"
rs1.Update

I'm probably gonna be wrong lol, but that's my input.

[Pasvorto]

It doesn't like that bit of code.

[GaryMazzone]

Try like this:

'===========================
Set rs1 = New ADODB.Recordset
'===========================
sql = "SELECT * FROM TMPCOMPNEEDED ORDER BY [UNPRTD],[LINE],[LOT],[ONHAND]"
rs1.Open sql, CnxnTempSQL, adOpenKeyset, adLockOptimistic, adCmdText
If Not rs1.BOF And Not rs1.EOF Then
    rs1.EDIT
    Do
        If UNPRTD = "" Then
            UNPRTD = rs1![UNPRTD]
            LINE = rs1![LINE]
            JOB = rs1![JOB]
            REMBAL = rs1![REMBAL]
            ONHAND = rs1![ONHAND]
            LOT = rs1![LOT]
        Else
            If Trim(rs1![UNPRTD]) = Trim(UNPRTD) And rs1![LINE] = LINE And rs1![LOT] = LOT And rs1![ONHAND] = ONHAND Then
                rs1![DROPTHIS] = "X"
                rs1.Update
            Else
 
Missing stuff here
    rs1.MoveNext
WEnd
rs1.Update

[Pasvorto]

It does like .edit at all, nowhere.

[Pasvorto]

When I get rid of this "ORDER BY [UNPRTD],[LINE],[LOT],[ONHAND]", I do not get the error message. Making headway.

[djwk]

Must be because it's SQL, actually I don't use .edit and .update to remove a record from MS Access databases, I've always just used .delete. Must be totally different when accessing SQL.

[Pasvorto]

I created an index on the recordset and it worked. Go figure.

[djwk]

Lol at least it's working, sorry I couldn't help.