Link to home
Start Free TrialLog in
Avatar of wellesleydpw
wellesleydpw

asked on

Access Query: Update "one" side of a one-to-many when many is a totals query

I would like to update the "one" side of a one-to-many when the many side is a totals query.  Is this possible?
Jeff
Avatar of Phillip Burton
Phillip Burton

Yes you can. However, you need to be more specific, so the best advice can be given.
Avatar of wellesleydpw

ASKER

Thanks Phillip,

On the one side, I have two fields:
1. [Seg] - a text field
2. [Locked] - a yes/no check box).  

One the many side, is a total query  with two fields:
1. [Seg3] - the join field and the field that the query is also grouped by
2. [MaxOfEditDate] - the total field which is a Max of the field [EditDate]

I would like to be able to edit the value of the field [Locked].

Does that help?
Jeff
Yes - HOW do you want to edit it?
I would like to change it back and forth from Yes to No and No to Yes.
Jeff
It's a boolean - yep.

Based on what criteria?
Clicking the check box.
Right - we are getting closer. So here is what I imagine (and what you have not said).

You have a form, which is based on some unknown query (which would be nice to know), but which shows fields from 2 tables:

1. One table, which has [Seg] and [Locked]. It is presumed that Seg is the primary key.
2. A second table, which has [Seg3] and [MaxOfEditDate]

It is grouped by Seg3, which is the foreign key of Seg.

You want the end user to be able to click on the [Locked] check box, and update all Locked values based on the value of Seg3.

Is this correct?
Avatar of Hamed Nasr
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
Thanks Phillip,
Sorry I was out sick for a week and then catching up.

You're close.  
1. One table, which has [Seg] and [Locked]. It is presumed that Seg is the primary key. - YES
2. A second table, which has [Seg3] and [MaxOfEditDate] - not a table but a total query that is grouped by [Seg3] and returns a max value of the field [EditDate] so the resulting field name in the query is [MaxOfEditDate]

I want the end user to be able to click on the [Locked] check box, which would only change the value on the [Locked] check box in the first table and have no effect on the second query.

Is that clearer?
Jeff
And is the problem that, when the user clicks in the check box, he gets the message "This Recordset is not updateable"?
Correct.
Finally. It's been a long, hard process, but after a month of work, I've finally got the question out of you, namely:

I'm getting the message "This Recordset is not updateable" when the user clicks in the check box on a form.

My tables are this ...
Here's the answer. Put this in the Locked_GotFocus VBA:

Private Sub Locked_GotFocus()
On Error Resume Next
Dim myRecord As Long
myRecord = Me.CurrentRecord
DoCmd.SetWarnings False
DoCmd.RunSQL "Update TableA Set Locked = " & IIf([Locked], "False", "True") & " Where Seg = " & Chr(34) & [Seg] & Chr(34)
DoCmd.SetWarnings True
DoCmd.RefreshRecord
DoCmd.Requery
DoCmd.GoToRecord acActiveDataObject, , acGoTo, myRecord
End Sub

Open in new window

Thanks Phillip,
I'm using access 2007 and from what I gather RefreshRecord was introduced in the 2010 version.  I get an error when I try to compile.  Is there an alternative command for 2007?
Jeff
Try just omitting it and seeing if it works.
I tried but it didn't work.
Jeff
Can you recreate the issue using a sample database and upload?
 
Explain which field to update and under what criteria.
I've uploaded the file.  Thanks for your help.

Jeff
Database1.accdb
Short answer to this is you can't do this directly.   In order for a query to be updateable, Access must be able to identify rows uniquely, but in a totals query (has a group by), it can't do that.  So you need to do it indirectly as Philip showed you, or split the query up a bit.

While you can do this:

Private Sub Locked_GotFocus()
On Error Resume Next
Dim myRecord As Long
myRecord = Me.CurrentRecord
DoCmd.SetWarnings False
DoCmd.RunSQL "Update TableA Set Locked = " & IIf([Locked], "False", "True") & " Where Seg = " & Chr(34) & [Seg] & Chr(34)
DoCmd.SetWarnings True
DoCmd.RefreshRecord
DoCmd.Requery
DoCmd.GoToRecord acActiveDataObject, , acGoTo, myRecord
End Sub

Open in new window


 There are a couple of things that you need to be aware of:

1. You don't want to update the field directly via the form since you would be doing it in code.  So make sure it's not enabled and locked.

2. If you want the user to still be able to check and un-check it, you put a transparent command button over the top of it, then use the OnClick event of that to actually update the field with code.

3. I would not use DoCmd.RunSQL to execute any SQL, but rather:

   CurrentDB().Execute  <sql statement> , dbFailOnError

 as it allows you to error trap.  With warnings off, you'll never know if your statement fails.

4. You don't need the requery.  If the record is already displayed, all you need is the Refresh to pickup the new data (namely, the check box).

5. When you do the update, you want to update the actual table that has the yes/no field.  If your creating that on the fly as part of the totals query, then you can't do that.  It has to be a real field in a table somewhere.   If you don't want to modify the main table, consider creating a temp table (totals query get's turned into a make table, and your form is based on the temp table).

I also mentioned splitting up the query.   One way to make the query updateable would be to do a query that does the totaling, then create another query which includes the base table and the query you just created for the totals.

 In this way, you could update the main table, and do it directly through the form.

 Read through that, see if it makes sense, and based on what you want to do, which way you'd like to approach it.  

Jim.
You might be able to do it by creating a new query that links your "one" table with the result of your total query.

Create a new query.

Add to it your table and your query (you can add a query in a query - check the tabs in the window in which you select your tables).

Link both together.

Bring down the fields that you need for display, but for the seg field, make sure that you bring down the field from the table (it should be updatable) and not the one from the query (it is not).

This is seen as a one to one relation, so the table is updatable.
Jim and James,
I must not have been clear because I have my query already links my "one" table with the result of the total query.   Here's a screenshot of my queries.

User generated image

Jeff
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
From the documentation : When you set the UniqueValues property to Yes, the results of the query aren't updatable.

The reason is one of simple logic... for Access. For a given row that is displayed, there might be 2 underlying records. Because changes in changes in a query a made one record at a time, the system would not know which row you want to change. Even if there care no hidden rows, the query is automatically marked as non updatable because of the possible side effects it could cause.

UniqueValues is usually used for display purpose. When you need to update, the individual rows should be visible.
Thanks Jim,

DMax it is!

Jeff
Just so you know, DMax() (or any domain function) inside a query is a big no-no from a performance standpoint.   The statements are un-optimizable by the query parser and will always yield poor performance as a result.

 Also, since all the domain functions represent a SQL statement, normally there is no reason to use one in a query since you can write the SQL directly.

 However is this case, as soon as I added in anything with a GROUP BY either as a sub query or a nested query, it's not updateable even though it should be from what I can see.  There's no obvious reason why the one side can't be updated as it is uniquely identified.

 But there must be something and I'm just not remembering this correctly because in looking back, I actually never did this.  I always used one of the workarounds.

And by the way, one other thing; you had a Department.* in one of the queries to select all the fields; bad habit to get into.

 Only use the fields you actually need in a query.  Don't select all by default.

Jim.