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
wellesleydpwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes you can. However, you need to be more specific, so the best advice can be given.
0
wellesleydpwAuthor Commented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes - HOW do you want to edit it?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

wellesleydpwAuthor Commented:
I would like to change it back and forth from Yes to No and No to Yes.
Jeff
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's a boolean - yep.

Based on what criteria?
0
wellesleydpwAuthor Commented:
Clicking the check box.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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?
0
hnasrCommented:
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
0
wellesleydpwAuthor Commented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And is the problem that, when the user clicks in the check box, he gets the message "This Recordset is not updateable"?
0
wellesleydpwAuthor Commented:
Correct.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

0
wellesleydpwAuthor Commented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try just omitting it and seeing if it works.
0
wellesleydpwAuthor Commented:
I tried but it didn't work.
Jeff
0
hnasrCommented:
Can you recreate the issue using a sample database and upload?
 
Explain which field to update and under what criteria.
0
wellesleydpwAuthor Commented:
I've uploaded the file.  Thanks for your help.

Jeff
Database1.accdb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
wellesleydpwAuthor Commented:
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.

Screenshots of queries

Jeff
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Jeff,

 Well I played around with this and I'm left scratching my head a bit; I don't understand why it's not updateable.  Seems it should be and I've done this in the past, but it doesn't work.

So you have a couple of choices:

1. Do it with a DMax()

screen capture
2. Make a temp table with the dates, then join to that table for the form (already mentioned).

3. Have a transparent button over the check box and when clicked, update the check box field yourself and refresh the record (again, already mentioned).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
wellesleydpwAuthor Commented:
Thanks Jim,

DMax it is!

Jeff
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.