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
Jeff
Yes you can. However, you need to be more specific, so the best advice can be given.
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
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?
ASKER
I would like to change it back and forth from Yes to No and No to Yes.
Jeff
Jeff
It's a boolean - yep.
Based on what criteria?
Based on what criteria?
ASKER
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?
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?
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
Explain which field to update and under what criteria.
ASKER
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
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"?
ASKER
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.Here's the answer. Put this in the Locked_GotFocus VBA:
My tables are 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
ASKER
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
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.
ASKER
I tried but it didn't work.
Jeff
Jeff
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
Explain which field to update and under what criteria.
ASKER
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:
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.
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
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.
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.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks Jim,
DMax it is!
Jeff
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.
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.