Update using a Recordset

[mike60smart]

Hi Vlad



Changed the Code to the following but now get the attached error.

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error
      Dim strSQL As String
20    If Me.Dirty Then Me.Dirty = False
      Dim rs As DAO.Recordset 'Object
30    Set rs = Forms!frmPurchaseDates!frmPurchasedItemssubform.Form.RecordsetClone 'Vlad assumes the subform control has the same name as its source object; your original expression would work as the form is not actually open as a stand alone form
40    With rs
50    Do While Not .EOF
      'Do Something
60      strSQL = "UPDATE [tblStock] SET InvQtyOH = " & Nz(rs("Qty"), 0) + Nz(rs("InvQtyOH"), 0) & " WHERE StockID = " & rs("StockID") & ";"
70    Debug.Print strSQL
80      CurrentDb.Execute strSQL, dbFailOnError
90    .MoveNext
100   Loop
110   End With
120   rs.Close
130   Set rs = Nothing
140   MsgBox "All Stock Updated", vbInformation


150       On Error GoTo 0
160       Exit Sub


cmdUpdate_Click_Error:


170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."


End Sub

[mike60smart]

Hi Vlad
I have attached a stripped down version so that you can see what I am attempting to do.

Any help appreciated

[Welshgasman]

Why are you surrounding the fields with brackets?
Have you even looked as to how you reference recordset fields?
There are several ways. You appear to be trying to.mix two of the methods?

https://www.access-programmers.co.uk...ariable.89838/

[mike60smart]

Hi Welshgasman

Are you referring to [tblStock] ??

[Gicu]

Here you are Mike, you needed a dLookup as the InvQtyOH was in another table, hence the error.

Cheers,

[Welshgasman]

Hi Welshgasman

Are you referring to [tblStock] ??

No, your quantity fields in the recordset.

[mike60smart]

Hi Vlad

You are a star as always.

There is no way I would have been able to achieve this without the help of the forum.

Many thanks again

[Welshgasman]

I owe you an apology Mike.

I have seen formats of

rs.Fields("Qty") and
rs!Qty

but never rs("Qty"), which is why I thought you were mixing the two, knowing your lack of VBA.

Seems that also works as well?

So apologies for that at least. The reminder of recordsetclones and form controls still stands however.

[Gicu]

Not really Mike, I might just have a bit more experience with this stuff ��, glad to hear it works now.
Cheers,

[Micron]

but never rs("Qty")

Don't know where I read a comment on that recently but it was news to me when I came across it. Apparently it is like .Value in that Value is seldom required because it is the default member of many controls/references. IIRC Fields is the default for recordsets so rs("myField") is the same as rs.Fields("myField"). While I don't usually use .Value, I would probably use .Fields because it seems to be not as well known.

[Welshgasman]

Don't know where I read a comment on that recently but it was news to me when I came across it. Apparently it is like .Value in that Value is seldom required because it is the default member of many controls/references. IIRC Fields is the default for recordsets so rs("myField") is the same as rs.Fields("myField"). While I don't usually use .Value, I would probably use .Fields because it seems to be not as well known.

When I started with Access I searched on how to reference fields in the recordset, the .Fields() syntax was the first I came upon, and so I used that.
Then I discovered the bang syntax, which is at least less to type?
Now I have a third, though that is still more to type than the bang syntax, if only by one character.

If you use Tempvars, you have to use the value for the field/control, otherwise Access thinks you are trying to assign an object, which a Tempvar cannot hold.
Catches me out most times, having got used to NOT using .Value.

[Minty]

I know I'm late to the party here, but unless I'm missing something this could be much more easily done with a simple update query?

No need for any recordset machinations, which we all know Mike struggles with?

[mike60smart]

Hi Minty
I did try an Update query but with no luck

How would you structure the query?

[Welshgasman]

And I thought I had a bad memory?

https://www.utteraccess.com/topics/2065471?post=unread

[mike60smart]

Hi Welshgasman
I knew I had used this previously but could not find the file I had saved with the code.

Many thanks and maybe I will remember now.