More Elegent Code

[Eddie Mason]

Hi All,

I need to run the following query in a form:

DoCmd.RunSQL "UPDATE Positions SET Positions.PercTot = " & _
"DSum(""[PercTime]"",""OccDet"",""[PosRef] = " & _
"Forms![FhPosnD]![PosRef] "")" & _
"WHERE Positions.PosRef =[Forms]![FhPosnD]![PosRef];"

Whilst this query works perfectly it doesn't look very elegent and is difficult to write. Is there any way of not having to put the double quotation marks around everything?

Regards

Eddie

 

[Rich]

I'd question why you're storing a calculated value?

 

[namliam]

I must agree with Pat sorry

 

[pono1]

Putting calculated values and worries aside for the moment, you can also call a query like this...

DoCmd.OpenQuery "ASavedQuery"

...where ASavedQuery is the name of a query you created and saved using the Access query grid.

Elegant? Don't know. Less code, though.

Regards,
Tim

 

[Eddie Mason]

Hi All,

Whilst I agree that it is undesirable and more often than not unnecessary to store a calculated value, there are times when it is required. In the case I described in my question, the reason is that although the figures making up the sub-set will change I require the original total of the sub-set to be used as a comparison with which to measure the overall change. The query I used acts to refresh the stored value when one or more of the original figures in the sub-set have been entered incorrectly.

Having said all that my question was not how could I get the query to work, it works perfectly well, but to find out if it is possible to get away from surrounding everything with double quotation marks?

Hope someone Can Help.

Regards,

Eddie

 

[namliam]

1) i dont see a need to use the "" at all...
2) "WHERE Positions.PosRef =[Forms]![FhPosnD]![PosRef];"
using a form reference like that usually results in trouble try using "WHERE Positions.PosRef = "& [Forms]![FhPosnD]![PosRef] instead.
3) To call update/inserts you might use currentdb.execute instead of docmd.runsql
4) I also would not use DSum but use an aggrate query instead, eliminating the "" alltogether

Regards

The Mailman

 

[Eddie Mason]

Hi Mailman,

Thanks for the help, I'm just about to try it the way you have suggested.

Regards

Eddie