query / form not editable

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!!
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

mbizupConnect With a Mentor Commented:
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)
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.
Dale FyeCommented:
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.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

mbizupConnect With a Mentor Commented:
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)
Luke ChungConnect With a Mentor PresidentCommented:
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.
developingprogrammerAuthor Commented:
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!! = ))
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<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...

developingprogrammerAuthor Commented:
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!! = ))
developingprogrammerAuthor Commented:
thanks mbizup! fantastic answers and i learnt a lot!! = ))
Luke ChungPresidentCommented:
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.
All Courses

From novice to tech pro — start learning today.