Solved Run an OnClick Event from Popup Form

[mike60smart]

Hi Everyone
I have a Popup Continuous form named "frm91NotPurchased"

On the Form there is an Unbound Control named "txtAllocated" which uses the following as its Control Source:-

=Nz([ReqQty],0)+Nz([Allocation],0)

I then have an On Click Event where I am trying to update the Control named "Allocation" in a table named "Stocklist" for
each of the records shown on the Form.

When running the following Code I get the following error:-



The Code is as follows and when run it highlights frm91NotPurchased in Line 20 .
Any help appreciated

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error

      Dim rs As Object
20    Set rs = Me.frm91NotPurchased.Form.RecordsetClone
30    With rs
40    Do While Not .EOF
      'Do Something
50      CurrentDb.Execute "UPDATE StockList SET Allocation = Me.txtAllocation" _
        & " WHERE StockNumber = '" & Me!StockNumber & "'", dbFailOnError
60    .MoveNext
70    Loop
80    End With
90    Set rs = Nothing
100   MsgBox "All Stocklist Items Updated", vbInformation

110   DoCmd.Close ""

          
120       On Error GoTo 0
130       Exit Sub

cmdUpdate_Click_Error:

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

End Sub

 

[Gasman]

So the form is frm91NotPurchased and you are saying that form has a control called frm91NotPurchased???, In fact a subform control?
You also are AGAIN mixing recordset fields and form controls.?

 

[Minty]

Surely that syntax isn't correct. You said this code is on the form called frm91NotPurchased
Why would you then refer to the form in that statement, shoudln't it just be

Me.RecordsetClone

 

[moke123]

Why use an unbound control? Put the calculation in the record source query.

 

[Minty]

Do you have option explicit turned on - I can't believe that syntax doesn't give an immediate error?

 

[mike60smart]

Surely that syntax isn't correct. You said this code is on the form called frm91NotPurchased
Why would you then refer to the form in that statement, shoudln't it just be

Me.RecordsetClone

Hi Minty
Many thanks for this. I changed the line to Me.Recordsetclone and the
Code now runs and does what it should but it only does it for 1 Record at a time.

I got the example code from a Google search.

What would I need to change to enable all records viewed on the Form get updated in the table?

 

[Minty]

Well, I'm afraid as per @Pat Hartman's response there is a whole heap of stuff in there that makes no sense.
The recordset doesn't actually achieve anything as far as I can tell.

It might be better to demonstrate with some data - what you have to start with, and what the expected end result is, as at the moment is all very disjointed, and confused, and might be a rabbit hole of a solution that isn't appropriate to the actual problem.

 

[Gasman]

Hi Minty
Many thanks for this. I changed the line to Me.Recordsetclone and the
Code now runs and does what it should but it only does it for 1 Record at a time.

I got the example code from a Google search.

What would I need to change to enable all records viewed on the Form get updated in the table?

Mike,
This is at least the third time you have done the recordsetclone and form controls mix up, here and in other forums.
It is not updating one record at a time, it is updating the SAME record as many times as there are records in the recordset.

 

[mike60smart]

Mike,
This is at least the third time you have done the recordsetclone and form controls mix up, here and in other forums.
It is not updating one record at a time, it is updating the SAME record as many times as there are records in the recordset.

Hi Gasman
Well the user has now come back and said they only wan to update 1 record at a time.
The following does what he needs:-

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error

          
      Dim strSQL As String

20    strSQL = "UPDATE Stocklist SET Allocation = " & Me.Update _
      & " WHERE StockNumber = '" & Me.StockNumber & "';"
30      Debug.Print strSQL
40      CurrentDb.Execute strSQL, dbFailOnError


50    MsgBox "The selected item in Stocklist has been Updated", vbInformation

        
60        On Error GoTo 0
70        Exit Sub

cmdUpdate_Click_Error:

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

End Sub

Thanks again for looking

 

[Gasman]

Well without multiple controls for each stock, I would not thik there was much else it could do?
Even if you had got the clone and form controls correct, you would have been setting the same amount for every stock?

 

[mike60smart]

Hi Pat
Yes the user has been told that his database is all wrong and he has plans to create a new one.