yet ANOTHER Recordset NOT UPDATEable question

[Niki]

I have sifted through this forum to make sure that I am not repeating a question and I have looked at the FAQ's, but I could not find a similar situation. So here goes.

Created a form from ONE table. Need to do an average on a 4 similar fields, then another average on 2 other similar fields.

I created a query based on this table and created the following expression:

groAvg=Expr1: Avg(([groHAC0400]+[groHAC2200]+[groHAC1000]+[groHAC1600])/4)

and the next expression is:

pbAvg=Expr1: Avg(([pbHAC0400]+[pbHAC1600])/2)

These are in the same query grid. I get the averaging to work quite nicely in the form. YA HOOOOOO! however.............

Now I am not able to enter data. I get this error message RECORDSET NOT UPDATEABLE.

Now I have read and tried to understand the HELP... usually it HELPS me but I am daft at this one.

What in the world am I doing to cause myself such grief! X-)

TIA.... Niki [sig][/sig]

 

[PaulF]

Why are you calculating this inside the query? Since this is a calculated value it should not be stored in a table, because you can always reproduce the results when needed.

Why not use two unbound textboxes on your form and for the ControlSource use these

= iif([pbHAC0400]+[pbHAC1600]> 0, ([pbHAC0400]+[pbHAC1600])/2),0)

= iif([groHAC0400]+[groHAC2200]+[groHAC1000]+[groHAC1600]> 0, ([groHAC0400]+[groHAC2200]+[groHAC1000]+[groHAC1600])/4),0)

PaulF
[sig][/sig]

 

[Niki]

Thank-you Paul.... You asked me: Why are you calculating this inside the query? Since this is a calculated value it should not be stored in a table, because you can always reproduce the results when needed.

:-V So are you saying I don't need the QUERY on which this form is based on????? Do I just use the table as the base of my form rather than the query?

I did exactly as you have described above and I get an error
message CONTAINS INVALID SYNTEX or YOU NEED TO ENCLOSE YOUR TEXT DATA IN QUOTES. I did base my form on the table rather than the query when I applied your suggestion.

I have tried placing quotes in various places and get the same error message. I think I need a basic lesson here.

Your help and anyone else's is greatly appreciated. [sig][/sig]

 

[PaulF]

Niki
Depends on what you're trying to accomplish, if you are using this as a form where data entry is done, and you are only entering data into one table, then no, you don't need the query. And since you can recreate the average value in any form, query or report that you use in the future, there is no need to include the calculated value in your table.

As for the syntax error, yes there is, in both examples. You need to remove the right parenthesis after the /2 and /4.

Sorry about that. I was working on the example at work, but didn't get the results sent until I got home and just did a quick cut & paste, without testing the statements.

PaulF [sig][/sig]

 

[Waynest]

Your query is too gnarly - access doesnt let you use many combinations of joins, calculations etc before it gives up trying to maintain a 2 way channel to the data - its a complete pain in the ass, and access does even less when you're pulling the data over from sql server.

Sorry, but if you cant just use the table as the data source you'll have to simplify the underlying query and write some code to perform the updates. [sig][/sig]

 

[Niki]

Thank-you Paul for your assistance. It is now working and I look like a million bucks here at work ~ all thanks to you! I am now working on the security and you will probably see my name back up here soon asking security questions. This is a wonderful site ~ s-) I have recommended it to others who have the same thought as me on this site! [sig][/sig]