Editable forms

[colinod]

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

SELECT [mp3].[mp3id], [mp3].[File], [mp3].[location], [mp3].[Artist], [mp3].[Information], [mp3].[filename], [mp3].[Information]
FROM mp3 INNER JOIN mp3voice ON [mp3].[mp3id]=[mp3voice].[mp3id]
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]))
GROUP BY [mp3].[mp3id], [mp3].[File], [mp3].[location], [mp3].[Artist], [mp3].[Information], [mp3].[filename]
HAVING (((Sum(1))=[Forms]![Form1]![HiddenObject]));

Any advice would be appreciated

[Stewart Ross]

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

[colinod]

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

[NeoPa]

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

[missinglinq]

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

[NeoPa]

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.

[colinod]

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

[Stewart Ross]

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

[NeoPa]

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(*).

[Stewart Ross]

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

[Stewart Ross]

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