Solved

query / form not editable

Posted on 2013-07-01
10
260 Views
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!!
DMS-Cerebro---SSB-Recon.mdb
0
Comment
Question by:developingprogrammer
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 6

Expert Comment

by:Dulton
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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.
0
 
LVL 61

Assisted Solution

by:mbizup
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


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

Open in new window


(Those two textboxes will not/ can not be updateable)
DMS-Cerebro---SSB-Recon.mdb
0
 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS 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.
0
 

Author Comment

by:developingprogrammer
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!! = ))
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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...

JeffCoachman
0
 

Author Comment

by:developingprogrammer
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!! = ))
0
 
LVL 61

Accepted Solution

by:
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)
0
 

Author Closing Comment

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

Expert Comment

by:LukeChung-FMS
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

20 Experts available now in Live!

Get 1:1 Help Now