query / form not editable

Posted on 2013-07-01
Medium Priority
Last Modified: 2013-07-11
guys, how come my query and my form (both with the name CodeList) is not editable? i can't edit the fields.

how can i design the query / form such that it is editable? thanks!!
Question by:developingprogrammer
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 39289809
You can only update 1 table at a time... updating through a multi-tabled view takes care to ensure you're not updating multiple tables.... in many cases of access 2003 (and older), when querying multiple tables, at the bottom of datasheet view, it would read something about the query not being updatable.

There are obviously many reasons you may have these symptoms, but try isolating your update to 1 table or the other and see if this may be it.
LVL 48

Expert Comment

by:Dale Fye
ID: 39289856
I cannot open the database at this time (security issues), can you post the SQL for the query?

Here is a link that describes most of the reasons that a query might not be updateable.
LVL 61

Assisted Solution

mbizup earned 1000 total points
ID: 39289887
A grouping query will never be updatable.  The reason for this is in part that each visible 'record' is actually a collection of records.

You can work around this by changing your codelist query to simply select from the original table (the ORDER BY is fine as-is):

SELECT perfCode.CodeID, perfCode.WorkflowSequence, [SecondsAwarded]/60 AS MinutesAwarded, Format([LastProcessReview],"dd mmm yyyy") AS LastProcessReviewFormatted
FROM perfCode
ORDER BY DConcat("CategoryAcronym","Category_Acronym_AcronymDefinition","[FKCodeID] = " & [CodeID],"-","=",False,"[CategoryNumber] Asc");

Open in new window

And change the control sources of your Code and CodeDefinition textboxes to:

 =DConcat("CategoryAcronym","Category_Acronym_AcronymDefinition","[FKCodeID] = " & [CodeID],"-","=",False,"[CategoryNumber] Asc")

Open in new window

=DConcat("AcronymDefinition","Category_Acronym_AcronymDefinition","[FKCodeID] = " & [CodeID],"-","=",False,"[CategoryNumber] Asc")

Open in new window

(Those two textboxes will not/ can not be updateable)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 500 total points
ID: 39292767
Here's a paper I wrote that covers the basics of Dealing with Non-Updateable Microsoft Access Queries and the Use of Temporary Tables.

It's part of our Microsoft Access Query Help Center where you'll find additional resources on query tips and techniques.

Hope this helps.

Author Comment

ID: 39295419
whao guys, superb responses. let me go through all of them and get back to yall k? thanks so much for all your help once again!! = ))
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 39297627
<No Points wanted>

In your particular case, mbizup is correct.
The source for your form is a Group By query.

In most cases, a Group By query will gives you summaries, hence you cannot update it.
For example: you have a Group by query that tells you the sum of sales for 3 divisions.
Here, you cannot "update" the sum, you must update the individual sales.
Make sense...?

You seem to have duplicate data in the source, so this may have been the reason for using  Group By query.
So your first goal may be to see if a Group by query is what is really needed here...


Author Comment

ID: 39305200
hi LukeChung-FMS, thanks for your help!! i read both your papers (the other one linked from the first) and yes i agree that the append is a very useful function!! we write our forms based on the destination table of the append. however when we want to save the changes back to the very source tables, then we would need to instantiate a recordset query, findfirst and edit the query right? so the append is like the first 50% of the battle from what i understand. but feel free to correct me if i got it wrong!! i'm happy to learn from you!! = ))

whao mbizup, superb solution as usual!! great creativity that i didn't think of at my level haha = ) however i'm just concerned that if i outsource some of the conversion to the form's source control it could get quite messy and hard to maintain.

mbizup currently how i organise my database is as such

1) Presentation Layer
2) Logic Layer
3) Data Layer

in Logic Layer i've got all my code arranged in an MVC way. hrmm in the sense that all the processing happens in the logic layer and the presentation layer is just how long the command buttons are, is it datasheetview or continuous forms etc. so i don't mix the pull of the data in both Logic and Presentation layer. i feel your way is mixing them a little bit and someone with little experience like myself could get easily confused ha.

hrmm... come to think of it i think i can further organise my code more into MVC neat organisations. that would definitely be much much better ha = )

jeff thanks for pointing out the duplicate data in my query, it was for tracking of changes of secondsawarded that i didn't implement properly. cactusdata taught me how to do it properly and i've changed it already. but thanks for pointing that out to me!! = ))
LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 39305217
i feel your way is mixing them a little bit and someone with little experience like myself could get easily confused ha

Bottom line is that the query behind the form is not updateable, so the form cannot be edited with those fields in the recordsource.

You only have a few options:

1.  Make the form's recordsource query updateable by removing those fields from the recordsource query and calculating them in the control sources instead (this was my earlier suggestion)

2.  Updating the table data indirectly through Update and Append Queries/VBA and requerying the form - this can also be confusing to beginners, since it can be code intensive. (you seem to have implemented this method in your more recent questions)

3.  Live with this form being read-only, and create a second form bound directly to your table (or an updateable query) for data-entry purposes without displaying those two fields. (not optimal because it duplicates your original form to a great extent, but it is a relatively simple solution that works)

Author Closing Comment

ID: 39318987
thanks mbizup! fantastic answers and i learnt a lot!! = ))
LVL 10

Expert Comment

by:Luke Chung
ID: 39319006
To avoid the problem of having to write back to the source table, a better way would be to create the temporary table keyed with just the ID values that can be linked to the source table. That way, you can create a query that the form uses which shows the real data and is updateable.

That eliminates all the code to try to update the original data from the temp table. This actually doesn't work in a multiuser environment because you'd end up overwriting someone else's changes.

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

719 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