By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Editable forms

100+
P: 347
I have a form that is based on and SQL query, i was hoping that i would be able to update some of the details in the form, mainly a comments text box for each entry in the DB. I have the properties set to allow edits, but it is not helping i just cant type anything into the textbox. I am wondering if this has something to do with the SQL as i did not write that part myself, im not sure what the group by bit does

here is the SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT [mp3].[mp3id], [mp3].[File], [mp3].[location], [mp3].[Artist], [mp3].[Information], [mp3].[filename], [mp3].[Information]
  2. FROM mp3 INNER JOIN mp3voice ON [mp3].[mp3id]=[mp3voice].[mp3id]
  3. WHERE ([mp3voice].[Voice Type] In ([Forms]![Form1]![selected_type_1],[Forms]![Form1]![selected_type_2],[Forms]![Form1]![selected_type_3],[Forms]![Form1]![selected_type_4],[Forms]![Form1]![selected_type_5],[Forms]![Form1]![selected_type_6],[Forms]![Form1]![selected_type_7],[Forms]![Form1]![selected_type_8],[Forms]![Form1]![selected_type_9],[Forms]![Form1]![selected_type_10],[Forms]![Form1]![selected_type_11]))
  4. GROUP BY [mp3].[mp3id], [mp3].[File], [mp3].[location], [mp3].[Artist], [mp3].[Information], [mp3].[filename]
  5. HAVING (((Sum(1))=[Forms]![Form1]![HiddenObject]));
  6.  
  7.  

Any advice would be appreciated
Aug 13 '08 #1
Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Group By queries are not updatable I'm sorry to say. Such queries are generally used to provide totals etc at a level above that of the individual rows involved, so as it stands you can't keep the grouping if you want users to modify or add entries.

Looking at the query it does not appear to be doing any aggregation, so it may not be necessary to have the GROUP BY clauses at all. Open the query in design view and untick View, Totals, save it under a new name (so as not to overwrite the original for test purposes) then run the query and try modifying an entry. If all is ok you can just take the View, Totals off on the original query and your form should then be updatable for your users.

-Stewart
Aug 13 '08 #2

100+
P: 347
thanks for that im not sure what the group by and having lines did but i took them out and the querys all work fine and i can edit fields in the form
Aug 13 '08 #3

NeoPa
Expert Mod 15k+
P: 31,615
They changed the query from an editable one to one which wasn't :->

These things are usually left over from development or they have been nicked from somewhere else with similar requirements. Clearly this wasn't similar enough ;)

Nice spot by Stewart though:)
Aug 15 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Since we've got Ade's attention, maybe he can explain why this post requires scrolling right thru about three pages to read an entire sentence? The problem appears localized to this thread.

BTW, Congrats, Ade, on passing the 10,000 post mark!

It's a shame the "scoreboard" on the main page on the forum only shows 7903!

Linq ;0)>
Aug 16 '08 #5

NeoPa
Expert Mod 15k+
P: 31,615
Hey Linq.

I have no idea why that might be happening for you. I don't see anything different about this page. Sorry to be so little help.

On the scoreboard issue, 7,903 was the number of posts I've put in in the Access area. To see the quoted total (this doesn't include posts in the Moderators area) you can visit Bytes.com directly. The home page of the site lists all the top ten posters site-wide.
Aug 16 '08 #6

100+
P: 347
Hi going back to the SQL the reason for the GROUP BY is because without it i cant get my results to return the files that inclued more than 1 word entered in the selected_type_* fields in my form, the idea is that you select a number of words from drop lists on a form and have the results returned if the file contains all the words
Aug 21 '08 #7

Expert Mod 2.5K+
P: 2,545
Hi. We'd need more details to help you here, as you don't list the field names of the fields involved, nor where the count you refer to is located. This is not a GROUP BY issue; in post 1 there is a HAVING (WHERE) clause which is intended to restrict the records I guess in the way you mention. Unfortunately, the clause as listed is incorrect (involving SUM(1) which is just 1).

If you could tell me the names of the fields involved I can advise on what a suitable WHERE clause might be. Meantime, have a look again at your HAVING clause in post 1, remove the unnecessary SUM part, then try this as a WHERE clause in your SQL (making whatever changes you think necessary to the suspicious constant '1' listed which is meaningless to me) and see how you get on.

-Stewart
Aug 21 '08 #8

NeoPa
Expert Mod 15k+
P: 31,615
Stewart, I think you may be mistaken about the Sum(1) part. It can have meaning (although the post is very unclear and offers too little explanatory information).

Think of Sum(1) as equivalent to Count(*).
Aug 21 '08 #9

Expert Mod 2.5K+
P: 2,545
Thanks NeoPa - I'm confusing Excel's Sum function here, where beginner spreadsheet students often used cell formulas like =SUM([cell1]+[Cell2]+[Cell3]) when =[cell1]+[cell2]+[cell3] was enough.

Apologies to Colinod.

Whoever wrote the original did Colinod no favours...

-Stewart
Aug 21 '08 #10

Expert Mod 2.5K+
P: 2,545
Since as NeoPa said Sum(1) is equivalent to Count(*), if it is necessary to compare a count of the rows to a control value in your query you should use the DCount domain function to do so instead. That way you won't end up with the query becoming non-updatable again as the result of using an aggregate function within it.

-Stewart
Aug 21 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.