Recordset Problem

[Minkers]

I am having two problems with my recordset. Firstly, when I try to execute the code it tells me "Operation not allowed when this object is open" hilighting my Open command. Secondly, this is to create a new record in the table, but I don't know how to tell it to move to a new record. Will it do so automatically?

TIA!
Minkers

Dim rs As New ADODB.Recordset
Dim varECNID As Integer
Dim varItem As Variant, strSQL As String, i As Integer
i = 0
For Each varItem In Me.lstModels.ItemsSelected
varECNID = Me.ECNID
strSQL = "SELECT tblCost.ECNID, tblCost.Quantity, tblCost.Phase, tblCost.Workstation, tblCost.UnitCost, tblCost.PartID, tblCost.ModelID, tblCost.StandardOrOptionalID, tblCost.PartAddDel" _
& " FROM tblCost;"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs.Fields("ECNID").Value = Me.ECNID
rs.Fields("Quantity").Value = Me.Quantity
rs.Fields("Phase").Value = Me.Phase
rs.Fields("Workstation").Value = Me.Workstation
rs.Fields("UnitCost").Value = Me.UnitCost
rs.Fields("PartID").Value = Me.PartID
rs.Fields("ModelID").Value = varItem
rs.Fields("StandardOrOptionalID").Value = Me.StandardOrOptional
rs.Fields("PartAddDel").Value = Me.PartAddDel

rs.Update
i = i + 1
Next varItem

If i > 0 Then
MsgBox i & " Models were updated", , "Done"
Else
MsgBox "No models were chosen", , "No changes"
End If
rs.Close
Set rs = Nothing
End Sub

 

[LittleSmudge]

All is recoverable.

But theres a lot of unnecessary waffle in your code

and ONE MISSING LINE

Dim rs As New ADODB.Recordset
Dim varECNID As Integer
Dim varItem As Variant, strSQL As String, i As Integer
i = 0
strSQL = "SELECT ECNID, Quantity, Phase, Workstation, UnitCost, PartID, ModelID, StandardOrOptionalID, PartAddDel " _
& "FROM tblCost;"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'' You only OPEN the rst Once NOT each time round the loop
For Each varItem In Me.lstModels.ItemsSelected
    varECNID = Me.ECNID
    rs.AddNew   '' THIS IS YUOR MISSING LINE
    rs!ECNID = ECNID
    rs!Quantity = Quantity
    rs!Phase = Phase
    rs!Workstation = Workstation
    rs!UnitCost = UnitCost
    rs!PartID = PartID
    rs!ModelID = varItem
    rs!StandardOrOptionalID = StandardOrOptional
    rs!PartAddDel = PartAddDel

    rs.Update
    i = i + 1
Next varItem

If i > 0 Then
    MsgBox i & " Models were updated", , "Done"
Else
    MsgBox "No models were chosen", , "No changes"
End If
rs.Close
Set rs = Nothing
End Sub

Do you see what ( and Why ) I've done.

And do you now see the bits you'd missed ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

As an additional thought, I lay out my recordset openings as

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Activeconnection = CurrentProject.Connection

rs.Open "SELECT ECNID, Quantity, Phase, Workstation, " _
      & "UnitCost, PartID, ModelID, StandardOrOptionalID, " _
      & "PartAddDel " _
      & "FROM tblCost;"

Shifting the New out of the Dim line means that the "Set rs = Nothing" at the end of your snippet really does release the object's memory rather than just re-allocating it to a new object
Sticking the other parameters ahead of the rs.Open means that if you rs.Close and rs.Open on a new SQLString then you don't need to repeat all of the properties again.

( For your consideration - thats all. )


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.