Unable to update the Recorset

Question

Hello everyone.
I also have an issue and I'm just getting started with Access 2000.
Let's imagine a company with 3 main agencies (a, b, c) and each of them has 4 secondary sales points (a1, a2... c3, c4). We have a field called effectifpdv which represents... the workforce of the sales point in the "sales points" table. We want to access the record a1 or a2, or a3, and display the total workforce for a, that is, the workforce of a1 + the workforce of a2...
I created a query for this with a grouping field (all the sales points that depend on agency a) and a sum field for the workforce. I have a zone in my "sales points" form whose source is the query I just described.
It is then impossible to update the other data from the "sales points" form. The message "unable to update the recordset" appears at the bottom of the Access window.

Question: why?
Question: how to do it?

Thanks to all the brains!

Puples · Accepted Answer

Although it's an old message, I found the solution for my part and since this post is ranked well, I just changed the type of recordset in my subform:

Dynamic Sheet (Global Update)

And it worked.

++ PUPLES

blux · Answer

Hi,

how did you create your form:

- ex-nihilo?
- using an assistant and thus based on a table/query?

If it's the second case, then there might be a problem...

A+ Blux

 "Stupid people dare everything. That's how we recognize them"

blux · Answer

I think that, since the form is linked to a source, all the fields present (unless they are independent) are ready to update the data... that is to say, the query field, the others are not linked to anything, which may explain the error message

A+ Blux

 "Fools dare to do anything. That's how you recognize them"

blux · Answer

You have a request-based form.In your form, there is one (or more) field(s) whose source is linked to your request.If you have other fields that are not part of the request on which the form is based, then you cannot update them...Is that clearer? Or maybe I didn't understand your problem! :-)See you, Blux "Idiots dare to do everything. It's even how we recognize them."

Richard (un autre!) · Answer

Bingo! I understood.

I tried to take the query of the total staff in modification, adding the fields of the table "Decisions" one by one: there, I have to choose an operation (I chose "first," even though it doesn't yield something coherent). I save everything and create an instant form that... I still can't modify!
Well, is there anything left to try?

blux · Answer

A "sum" query will never allow you to modify anything in ACCESS...

You need to base your form on a "view" query...

What do you actually want to do?

Display the sums?
Modify data?

See you soon, Blux

 "Stupid people dare to do anything. That's how we recognize them"

blux · Answer

So, a form with a dropdown list that allows you to choose the point of sale.

Dependent fields based on the previous choice that are "updatable"

An independent field that displays the result of the sum query which is the same regardless of the chosen point of sale (if I read correctly).

That's it!

See you, Blux

 "Fools dare to do anything. That's how you recognize them."

blux · Answer

Well, go ahead! ;-)))  See you, Blux  "Fools dare everything. It's even how we recognize them."

blux · Answer

If you want, you can send me your database (zipped) to the following address:

denis[dot]mail[listat]free[dot]fr

Of course, if there is no sensitive data, I'll see what I can do...

See you Blux

 "Stupid people dare to do anything. That's how we recognize them"

Richard (un autre!) · Answer

Here it is, it's done. I still took the time to lighten it up.  Thank you.  Richard

blux · Answer

For me, there are a few design issues in your database:

the mainagencies table has a field 'totalstaffagency'
the agencydefinition table does not have a field 'staff'

How do you manage to calculate the sum of the staff of the agencies dependent on a main agency (having the same codagprincipal) in this case?

It should be noted that Access is very good at automatically managing the links between tables and forms, but this comes at the cost of an initially well-structured design (I take nothing away from your merit, but I am warning you ;-))

I don't want the CA to close, my wife works at Greencam :-)

Cheers Blux

 "The fools, they dare everything. It's even how we recognize them."

Richard (un autre !) · Answer

Yes for both remarks.
Explanation:
The TotalEffAgence field in the EffectifAgence table is something I tried (dynamic update, but it also got stuck); I don't use it and we can remove it, along with the query.

If you open the query for modification, there are 3 tables involved: agencesprincipales, definitionagence, and effectif agence, which allow me to link the main agencies and the workforce of the sales points. The workforce of the sales point is calculated in the effectifagence form (by sum) and updated in the table via an update query.
I take the codeagprincipale from my definitionagence table as a criterion to search for all corresponding records, I sum the values of the totaleffpdv fields, and that's it.
And it’s this sum that I display in the Apositionner form.
If you're up for it, we can get in touch by phone?

blux · Answer

Yes, that's true, I eventually saw that field, but my post was already sent!

No matter how I turn it around, I can't make anything stand out, whether it's with your method or mine (sub-forms + VBA).

I think, but SGDG, that Access probably trips over the carpet with 1-1 relationships that have the same key, and therefore theoretically shouldn't be in separate tables... but they are, and that shouldn't physically pose a problem. There must be some option to prevent the UPDATE, loaded with the filter from the form 'To Position by NCA', but I don't know where... or maybe it deduces this impossibility from how the forms are linked...

I'm sorry, but I can't help you anymore...

However, I'm still open to an explanation if you find one :-)

See you, Blux

 "Idiots dare to do anything. That's how you recognize them"

Richard (un autre!) · Answer

OK.
Your remark about one-to-one relationships is correct, however, the information contained in each table really has nothing to do with that in other tables.
Except that I found something that works! (It sometimes heats up better on weekends!): we take this damn update query, turn it into an instant form "FormulaireCalculTotalEffectif" (with a refresh macro on "enabled"). We take the "Apositionner" form and insert a subform with the new form as the source and ... it works!
I suppose the subform has become "independent" and it no longer blocks the data.
I have no other explanation.
I would like to sincerely thank you for the ideas and the time you spent on it and I will quote JC Bellamy (www.jcbellamy.com).
"Knowledge increases when shared."

Richard.

cloe · Answer

But I have the same issue and it's still not working!!!!! Sniff
From a form, I choose an activity, a location, and a period
thanks to these choices!!! It displays all the answers in my subform!!!!!
However, in this subform, I cannot modify a field!!!
It shows me that updating the recordset is impossible!!!!
What should I do, please!!!!!!!!!!!

claire · Answer

I also see your discussion quite late, and a big thank you to Puples for his very relevant response: the "Dynamic Sheet (Global Update)" debug everything. Claire

Unable to update the Recorset

16 answers

Libreoffice writer and whatsapp buggy

0x000014c error code

Folder name i don’t understand

Texture issues in enshrouded

Bios issue

No sound on my pc

Driver unavailable

I just watched season 1 of a series on arte in french (vf)

No internet connection possible

Kenwood p66 turntable won't spin anymore