query / form not editable

Posted on 2013-07-01
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 250 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)
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 125 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 125 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 250 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

635 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