ADO Recordset error

[Robert]

I have gotten off track with the procedure below and
need help finding where I've gone wrong.

Here is what I am trying to do...

I have a form opened which is named Sales. There are
two controls on that form which I am using.

Item and SaleQty

I want to open a recordset of the table tblItem.

There are two controls of tblItem I am going to
use, they are

Item and InStock

Then find the record Item of tblItem which is equal
to Forms!Sales!item.

Once this record is found, then I want to update
the InStock control where

InStock = InStock - SaleQty

This is the code...

Private Sub SaleQty_AfterUpdate()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Source = "tblItem"
rst.Open Options:=adCmdTable
With rst
.Find [Item]
If .EOF Then
MsgBox "No Item was found!"
Else
.Fields("InStock") = "[InStock] - Forms!Sales!
SaleQty"
.Update
End If
End With
rst.Close
Set rst = Nothing
End Sub

 

[Tim Ferguson]

Then find the record Item of tblItem which is equal
to Forms!Sales!item.

Once this record is found, then I want to update
the InStock control where

InStock = InStock - SaleQty

Well, you don't actually say what is wrong with the code you posted, but I
would do this whole thing in two lines:

' you will need to check the data types and make sure
' that there are quotes in all the right places
strSQL = "UPDATE tblItem " & vbCrLf & _
"SET InStock = InStock - " & Me!SaleQty.Value & vbCrLf & _
"WHERE Item = """ & Forsm!Sales![Item].Value & """;"

' I rarely use the ADO methods, so you'll have to check
' up on the parameters for this, but you should see
' roughly what is meant to happen.
CurrentProject.ActiveConnection.Execute strSQL, _
adoCommandText, adoDoesNotReturnRecords


Hope that helps


Tim F

 

[joe]

Try setting the source to be rst.Source = "select * tblItem"

-- Joe