Grouping in a query - Cant update record source

You can not update results of a Group By query.

What are you trying to update?  One row?  All rows of the query result set?

Hi Mark,  thank you for the response. 

<What are you trying to update?  One row?  All rows of the query result set?

=>one row only by way of a combo box.

Are you using Min() or Max() in the Select clause?

Is the row you want to update uniquely identified in the Group By query?

Then construct an UPDATE statement from the data in the group by query result.

As already pointed out, you cannot update a "Group BY" query (also known as a "Summation" query) because it is, well... a read-only "summary" of the data.
As aikimark recommended, you'll need to use "UPDATE" queries to make updates on the tables directly (that makeup your summation query) and then .Requery the form so that the changes "bubble up" to the form for display.
You can also try form .Refresh and see if it works for you.

You can do something like this - here in the Northwind example database, the form:

Product Sales by Total Revenue Chart

Northwind.zip

Private Sub Product_Name_KeyPress(KeyAscii As Integer)

    Dim NewName As String
   
    Select Case KeyAscii
        Case vbKeyReturn, vbKeyTab, vbKeyEscape
            ' Ignore.
        Case Else
            NewName = InputBox("Enter the new product name:", "Product Name", Me("Product Name").Value)
            Debug.Print NewName
            If NewName <> "" And NewName <> Me("Product Name").Value Then
                CurrentDb.Execute "Update Products Set [Product Name] = '" & NewName & "' Where [Product Name] = '" & Me("Product Name").Value & "'"
                Me.Requery
            End If
    End Select
   
End Sub

Select allOpen in new window

OK thank you for the responses.  Really appreciate it.  

I have crafted an update statement (below) but when I execute it (on AfteUrupdate event of cboStatus2), I still am not able to update the tblProjects recordset (it gives me a msg bottom left and says "this Recordset is not updateable".

Note that in the below code, [Trade_No] is a string.  I am not sure why I still can not update tblProjects.

Might be easier to see the database.  I have attached the pared down db and the form appears auto on load of db. 

Thank you in advance for the help.  

EE_GroupByNotUpdating.accdb

Private Sub cboStatus2_AfterUpdate()

    'Update [status] afterupdate is a workaround because form's qry is a summation "group" qry on tblProjects, which doesn't allow for updates
    
If Me.Status.Value <> Me.Status.Value Then 'might need Nz test too
        CurrentDb.Execute "UPDATE tblProjects SET status=" & Me.cboStatus2.Column(0) & " WHERE Trade_No='" & Me.Trade_No & "'"
    End If
   
End Sub

Select allOpen in new window

Is tblProjects the group by query?

tblProjects is in the group by query

It doesn't make any difference whether the control is a textbox or a combobox; if it is bound to the form's non-updatable recordsource, you can't update neither the control nor the form.

Use a technique similar to what I posted in my demo.

If Me.Status.Value <> Me.Status.Value

How can this condition ever be true?!?

Hi Mark,

<If Me.Status.Value <> Me.Status.Value

<How can this condition ever be true?!?

I think it should be = and not <>...oops

Hi Gustav,

I looked but you know me...dumb finance guy.  I thought this question would be a simple quick solution but it's turning out to be a little too complicated for me. 

I think it should be = and not <>...oops

Then how could the condition ever be false?!?  As such, the presence of the If...Then statement seems to serve no purpose.  Just execute the update SQL - every time.

<if...Then statement seems to serve no purpose.

Yes, thinking about it you are right.  I only added it because if for some reason I decided not to change the value of the cbo then the update query wouldnt run.  

it's turning out to be a little too complicated for me.

I don't think so. Study the function behind again. It is very simple - running an update query.

Any other solution will be equal or worse.

tblProjects  i assume is the "final" group by query...
If this is the case just go a step back and update in the base tables..
tblProjects will requery and show the new values.

Hi John

< If this is the case just go a step back and update in the base tables..

Not sure if  I follow but tblProjects is the record source of the form (base table) and has the group by (summation) qry and and the field I am updating is tblProjects.Status so I dont think I can step back anymore.   There are 2 tables in the form: tblProjects and tblStatus. I am using your Ustatus work around in the form and that is based on tblStatus and not sure if the same Ustatus workaround could be applied to this as well.    

This should be so simple, but we seem mired down in confusion and a strong desire to make things complicated because, well..... the answer just CAN'T be that simple....

We seem to have TWO references to "Status".  The Status field on the tblProjects, and the table tblStatus.  A single form CAN'T have multiple recordsources, so tblProjects must be the source table on the form's recordsource summation query, and tblStatus is, well, guessing it's a subform recordsource or control rowsource?

Just run an UPDATE query on the tblProjects table if that is the one you need to make updates to and make sure the changes "bubble up" to the form so you can see them in the form.

So you use tblProjects  as base of the Form's Recordsource while performing a GROUP BY....
So in essence you have as RecordSource of the form a query (SELECT f1,f2....fn FROM tblProjects  GROUP BY fieldx)
So you need to work directly with tblProjects as a table...

Like UPDATE tblProjects  SET Status = SomeValue Where SomeCriteria

Select allOpen in new window

If this doesn't work while you are on the Form...then

Me.RecordSource =""
docmd.RunSQL("UPDATE tblProjects  SET Status = SomeValue Where SomeCriteria)
Me.RecordSource =SELECT f1,f2....fn FROM tblProjects  GROUP BY fieldx

Select allOpen in new window

John/Nick: will be able to take a look in a few hours....thank you 

John, Nick than you very much for the help!  That was some awesome coding Nick and an interesting article although way above me.  I like the way a split form looks and wasn't aware of the Afterupdate event not firing limitation in this type of form.  

pdvsa

You had a couple of issues.

Your dataset was not Updateable, so you would have been unable to change the value in ANY control on that form.

And if you can't change the value, you aren't going to get an AfterUpdate event.

I  don't know if you can attach events to the controls of a Split form, since Access generates those based on the data in the form--and you don't see them in Design view

I think that the Form_Current event is the only event available to you -- but I could be wrong.

Any type of from where I can't interpose code between a user and willy-nilly changing of data is NOT my thing.

I'm sorry the article didn't help you as much as I wanted it too.

The idea is straight-forward: when you have un-updateable data, copy it to a temporary location that IS updateable, let the user have at it, and then code the users changes back to the original.

That's what I did with your sample.

Your grouped by query gets copies to a 'temp' table, your form is ties to that copied data, and then as you make changes, the AfterUpdate event of a control on a continuous form makes the changes back to your original data.

It's a useful technique to overcome a couple of occasion limitations: uneditable data and unbound controls on continuous forms.

Nick67

Thank you Nick.  Appreciate the knowledge transfer.  :)