SolvedPrivate

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

Posted on 2014-11-07
25
19 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:wellesleydpw
  • 10
  • 7
  • 3
  • +2
25 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428138
Yes you can. However, you need to be more specific, so the best advice can be given.
0
 

Author Comment

by:wellesleydpw
ID: 40428292
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428295
Yes - HOW do you want to edit it?
0
 

Author Comment

by:wellesleydpw
ID: 40428349
I would like to change it back and forth from Yes to No and No to Yes.
Jeff
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428353
It's a boolean - yep.

Based on what criteria?
0
 

Author Comment

by:wellesleydpw
ID: 40428354
Clicking the check box.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428359
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40428662
Can you recreate the issue using a sample database and upload?
Explain which field to update and under what criteria.
0
 

Author Comment

by:wellesleydpw
ID: 40480734
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480758
And is the problem that, when the user clicks in the check box, he gets the message "This Recordset is not updateable"?
0
 

Author Comment

by:wellesleydpw
ID: 40482585
Correct.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482608
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:wellesleydpw
ID: 40507356
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40507369
Try just omitting it and seeing if it works.
0
 

Author Comment

by:wellesleydpw
ID: 40508354
I tried but it didn't work.
Jeff
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40508436
Can you recreate the issue using a sample database and upload?
 
Explain which field to update and under what criteria.
0
 

Author Comment

by:wellesleydpw
ID: 40533490
I've uploaded the file.  Thanks for your help.

Jeff
Database1.accdb
0
 
LVL 57
ID: 40551083
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
 
LVL 40
ID: 40551531
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
 

Author Comment

by:wellesleydpw
ID: 40605395
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40605570
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
 
LVL 40
ID: 40605789
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
 

Author Closing Comment

by:wellesleydpw
ID: 40605807
Thanks Jim,

DMax it is!

Jeff
0
 
LVL 57
ID: 40605845
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now