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?

 

[Pat Hartman]

Lots of problems. Where to start.
1. You opened a recordset where you are looping through each record you want to update but you are running an update query that if it worked, would be updating every row with the value you have calculated on the current row.
2. The syntax of the Update query is incorrect because you have the "Me." reference inside the quotes. "Me." is only valid in the class module of a form or report. In a query you need to use the full reference - Forms!yourform!yourcontrol. In this case, since you are building the SQL, you would concatenate the Me. referenced field OUTSIDE of the quotes.
CurrentDb.Execute "UPDATE StockList SET Allocation = " & Me.txtAllocation _
3. Consistency is your friend. Use Me. rather than Me! to get compile time errors rather than runtime errors.
4. And what the others said.

Not sure what your objective is here. Are you intending to update all the rows with the same value or did you think that the where clause would get you a new record each time? Keep in mind that storing calculated values is poor practice at best. Inventory is best managed by using a transaction table. That way you have an audit trail for each in/out event.

 

[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.

 

[Pat Hartman]

Code now runs and does what it should but it only does it for 1 Record at a time.

And I told you why. The recordset doesn't fill the form. You are referring to the form to update each record and it will update it with the same value which I'm pretty sure will be the next wrong thing you discover.

If you are going to do this and I think you should NOT be storing calculated values, get rid of the recordset and just run an update query. If you want to update all the rows with the same value, then reference the form control. Otherwise, include the calculation in the query and you'll see how easy it is to do and maybe think twice about actually storing the calculated value.

 

[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?

 

[Pat Hartman]

Well the user has now come back and said they only wan to update 1 record at a time.

Why would you be giving the user the option to dictate how data is stored? They may be the subject matter expert but you are the database design expert. This method of updating inventory is very difficult to manage and impossible to audit should there be an error and so is never recommended by experts.

 

[mike60smart]

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