Update using a Recordset

[mike60smart]

Hi Everyone

My VB Skills are minimal and I am trying to use some Code to Update a Specific Field in a Table.



My Form structure is Main Form named "frmPurchaseDates"
with a Continuous Subform named "frmPurchasedItemsSubform"

When I run my code I get the following Error:-



The Code is as follows and any help is appreciated:-

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 Object
30    Set rs = frmPurchasedItemssubform.Form.RecordsetClone
40    With rs
50    Do While Not .EOF
      'Do Something
60      strSQL = "UPDATE [tblStock] SET InvQtyOH = " & Me.NewQOH & " WHERE StockID = " & Me.StockID & ";"
70      CurrentDb.Execute strSQL, dbFailOnError
80    .MoveNext
90    Loop
100   End With
110   Set rs = Nothing
120   MsgBox "All Stock Updated", vbInformation
      
130       On Error GoTo 0
140       Exit Sub


cmdUpdate_Click_Error:


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


End Sub

[June7]

If you have Option Explicit at top of header, then Debug > Compile should locate undeclared variable.

Disable the "On Error GoTo" line then run your code. What line does it stop on?

Best practice is to declare ALL variables at beginning of procedure.

Regarding saving stock balances, review http://allenbrowne.com/AppInventory.html

[mike60smart]

Hi June

It Highlights the name of the Form in Line 30

How would I declare the Form ?

[Gicu]

Mike, can you please review the updated code below:

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 = " & Me.NewQOH & " WHERE StockID = " & Me.StockID & ";"
70      CurrentDb.Execute strSQL, dbFailOnError
80    .MoveNext
90    Loop
100   End With
	  rs.Close
110   Set rs = Nothing
120   MsgBox "All Stock Updated", vbInformation
      
130       On Error GoTo 0
140       Exit Sub




cmdUpdate_Click_Error:


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


End Sub

Cheers,

[Bulzie]

Do you need to add Set db = CurrentDb and maybe change to "Dim rs as Recordset" instead of object

[Welshgasman]

Why refer to a form externally if the code is in that form?
As Vlad has pointed out, you completely missed Forms! at the start of the reference.

[June7]

Where is this code located? On main form named "frmPurchaseDates"? Then if subform container control is named "frmPurchasedItemssubform", your code should work. But good practice to qualify with object name or use Me.

Set rs = Me.frmPurchasedItemssubform.Form.RecordsetClone

I always name subform container control differently from the object it holds, like ctrPurchasedItems, so:

Set rs = Me.ctrPurchasedItems.Form.RecordsetClone

If code is behind subform then simply Me.RecordsetClone.

If this code is on neither of these forms, then need full path reference as shown by Vlad.

[Micron]

Looks to me like the form reference is to the subform on the main form and the code is on the main form. In that case, a simple Me. type of reference should work?
Late again.

[mike60smart]

Hi Vlad

Thanks for that the Code now runs but it is not doing as expected and does not move to the next record.

There are 2 records on the Continuous Subform with StockID's 58 & 59

It updates StockID 58 but not 59.

I put a Debug.Print strSQL and the Immediate Windows shows the following:-

UPDATE [tblStock] SET InvQtyOH = 121 WHERE StockID = 58;
UPDATE [tblStock] SET InvQtyOH = 121 WHERE StockID = 58;

It should update 58 to equal 121 and 59 with 10

Any pointers to make this work ?

[Gicu]

Show us a screen shot to see where the 121 and 10 are coming from. Your SQL statement is the problem, the Me.StockID will always refer to the current record of the form on which this code is run; you need to get the Me.StockID and Me.NewQOH in a different way if they come from different records (so might need to add a nested loop).
Cheers,

[mike60smart]

Hi Vlad

Thanks for that the Code now runs but it is not doing as expected and does not move to the next record.

There are 2 records on the Continuous Subform with StockID's 58 & 59

It updates StockID 58 but not 59.

I put a Debug.Print strSQL and the Immediate Windows shows the following:-

UPDATE [tblStock] SET InvQtyOH = 121 WHERE StockID = 58;
UPDATE [tblStock] SET InvQtyOH = 121 WHERE StockID = 58;

It should update 58 to equal 121 and 59 with 10

Any pointers to make this work ?

[Gicu]

Duplicate post Mike, please see above!

[mike60smart]

Hi Vlad

On the Continuous Form I have a Combobox for selecting "Stock" with the following Row Source:-

SELECT tblStock.StockID, tblStock.PartTypeID, tblStock.Code, tblStock.InvQtyOH FROM tblStock ORDER BY tblStock.Code;

There is a bound Control for "Qty"

I then have an Unbound Control named "StockID" with a Control Source of :-

=[cboStock].[Column](0)

I then have an Unbound Control named "NewQOH" with a Control Source of:-

=Nz([Qty],0)+Nz([InvQtyOH],0)

which display the correct values 121 & 10

[Welshgasman]

Mike, you do this every time.

I do not know how many times you need to be told.

You process a recordset yet use the form controls, so the DATA STAYS THE SAME !!!!

You did the same over at UA multiple times.

[Gicu]

I do recall some ...
So as you can see Mike, you need to replace Me.StockID with rs("StockID") and Me.NewQOH with Nz(rs("Qty"),0)+Nz(rs("InvQtyOH"),0) in the update SQL statement.

Cheers,