We help IT Professionals succeed at work.

Grouping in a query - Cant update record source

pdvsa
pdvsa asked
on
Experts, when you group in a query, you can not update the record source?  Is there some work around for this or can I remove the grouping in the record source and group on a form instead?  I dont see grouping functionality on forms like I do see in reports.  

thank you.
Comment
Watch Question

Top Expert 2014
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?
pdvsaProject finance

Author

Commented:

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.

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

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

Author

Commented:
Yes and Yes
Top Expert 2014
Then construct an UPDATE statement from the data in the group by query result.
Mark EdwardsChief Technology Officer
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.
Most Valuable Expert 2015
Distinguished Expert 2018

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
pdvsaProject finance

Author

Commented:

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
Top Expert 2014
Is tblProjects the group by query?
pdvsaProject finance

Author

Commented:

tblProjects is in the group by query

Most Valuable Expert 2015
Distinguished Expert 2018

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.

Top Expert 2014
If Me.Status.Value <> Me.Status.Value

How can this condition ever be true?!?
pdvsaProject finance

Author

Commented:

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. 


Top Expert 2014
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.
pdvsaProject finance

Author

Commented:

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

Most Valuable Expert 2015
Distinguished Expert 2018

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.

John TsioumprisSoftware & Systems Engineer
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.
pdvsaProject finance

Author

Commented:

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.    

Mark EdwardsChief Technology Officer
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.
John TsioumprisSoftware & Systems Engineer
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

Open 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

Open in new window

Most Valuable Expert 2014

There is a Split form in play.

The events DO NOT fire for controls in the split form -- that is the issue.

The AfterUpdate event is for the control in the Detail section--and that isn't being changed,   since the recordset is not updateable, and that control isn't even being used. And the controls in the split form aren't going to fire any events, since you can't attached events to them, and even if you could they wouldn't fire, since the recordset is not updateable.


You need a different architecture.

Please see your altered sample attached and have a look at the following article.

https://www.experts-exchange.com/articles/6692/Overcoming-unbound-continuous-forms-limitations-by-staging-data.html


Nick67EE_GroupByNotUpdatingNick.accdb

pdvsaProject finance

Author

Commented:

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

pdvsaProject finance

Author

Commented:

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.  

Most Valuable Expert 2014

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

pdvsaProject finance

Author

Commented:

Thank you Nick.  Appreciate the knowledge transfer.  :)