Calculated Field Update Problem in SQL View

[Gary Ayers via AccessMonster.com]

I have a form that uses a SQL view as the record source. This view has a
calculated field "ExtPrice" which multiplies the "UnitPrice" by the
"QtyOrd". When I update either the "UnitPrice" or the "QtyOrd" using the
form, the calculated field updates immediately. This is how I want the
form to work...

However, this only works when the SQL view that I use as the record source
contains only one table. I have a requirement to join two additional
tables in this view. When I do this, the form still works but the
calculated field does not update automatically - only by refreshing the
form which is my current workaround.

Any thoughts on how to have the calculated field automatically update
without refreshing the form?

Thanks in advance!

Here is the view:

SELECT TOP 100 PERCENT dbo.tblSubcontractDetail.*,
dbo.tblSubcontractDetail.RorOtherUnitPrice +
dbo.tblSubcontractDetail.RorEvalUnitPrice +
dbo.tblSubcontractDetail.RorRepairUnitPrice AS RorUnitPrice,
(dbo.tblSubcontractDetail.RorOtherUnitPrice +
dbo.tblSubcontractDetail.RorEvalUnitPrice +
dbo.tblSubcontractDetail.RorRepairUnitPrice +
dbo.tblSubcontractDetail.UnitPrice) * dbo.tblSubcontractDetail.QtyOrd AS
ExtPrice

FROM dbo.tblSubcontractNameDetail INNER JOIN dbo.tblSubcontractName ON
dbo.tblSubcontractNameDetail.SubcontractNameID =
dbo.tblSubcontractName.SubcontractNameID INNER JOIN
dbo.tblSubcontractDetail INNER JOIN dbo.tblContractDetail ON
dbo.tblSubcontractDetail.ContractDetailID =
dbo.tblContractDetail.ContractDetailID ON
dbo.tblSubcontractNameDetail.SubcontractNameDetailID =
dbo.tblSubcontractDetail.SubcontractNameDetailID

WHERE dbo.tblSubcontractDetail.SubcontractNameDetailID IN (1413)

ORDER BY dbo.tblContractDetail.Clin, dbo.tblContractDetail.Elin,
dbo.tblSubcontractName.SubcontractName,
dbo.tblSubcontractNameDetail.SubcontractDeliveryOrder,
dbo.tblSubcontractDetail.Slin, dbo.tblSubcontractDetail.PartNumber,
dbo.tblSubcontractDetail.Description

 

[Sylvain Lafontaine]

You must create a resync command: first, write a stored procedure with one
parameter: the primary key for your main table (probably
tblSubcontractDetailId in your case) and returning only one record. The
Select query is identical to your exemple with the exception that the WHERE
clause of this record is simply an equality based on tblSubcontractDetailId
and that you don't need the Order By, of course.

The property of the Resync command is simply the name of your resync stored
procedure followed by a single question mark:

My_ResyncQuery ?

I think that you must also set the Unique Table property but I'm not sure.
(Often, you cannot set the Unique Table property directly in the GUI when
the Record Source is a complex SP. The solution is to change temporarily
the Record Source to the name of the table or to set this property in the
OnOpen event of the form.)

 

[Vadim Rapp]

Hello Gary,
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 25
May 2005 21:13:49 GMT:

GAv> I have a form that uses a SQL view as the record source. This view
GAv> has a calculated field "ExtPrice" which multiplies the "UnitPrice" by
GAv> the "QtyOrd". When I update either the "UnitPrice" or the "QtyOrd"
GAv> using the form, the calculated field updates immediately. This is how
GAv> I want the form to work...

GAv> However, this only works when the SQL view that I use as the record
GAv> source contains only one table. I have a requirement to join two
GAv> additional tables in this view. When I do this, the form still works
GAv> but the calculated field does not update automatically - only by
GAv> refreshing the form which is my current workaround.


I tried this with 3 trivial tables, but it worked ok, i.e. the calculated
field in the view was updated automatically.

When happens if you open the view in Access and do the update? does the
calculated field update?

If you still have the problem, please generate and post CREATE TABLE's for
your tables, and some sample data as INSERT.


Thanks,

Vadim Rapp

 

[Gary Ayers via AccessMonster.com]

Worked perfectly! Thank you for the quick response.

Just to document...

The stored procedure I created was:

---
CREATE PROCEDURE dbo.spSubcontractDetailResync(@Param1 int)
AS

SELECT TOP 100 PERCENT dbo.tblSubcontractDetail.*,
dbo.tblSubcontractDetail.RorOtherUnitPrice +
dbo.tblSubcontractDetail.RorEvalUnitPrice +
bo.tblSubcontractDetail.RorRepairUnitPrice AS RorUnitPrice,
(dbo.tblSubcontractDetail.RorOtherUnitPrice +
dbo.tblSubcontractDetail.RorEvalUnitPrice +
dbo.tblSubcontractDetail.RorRepairUnitPrice +
dbo.tblSubcontractDetail.UnitPrice) * dbo.tblSubcontractDetail.QtyOrd AS
ExtPrice

FROM dbo.tblSubcontractNameDetail INNER JOIN
dbo.tblSubcontractName ON dbo.tblSubcontractNameDetail.SubcontractNameID =
dbo.tblSubcontractName.SubcontractNameID INNER JOIN
dbo.tblSubcontractDetail INNER JOIN
dbo.tblContractDetail ON dbo.tblSubcontractDetail.ContractDetailID =
dbo.tblContractDetail.ContractDetailID ON
dbo.tblSubcontractNameDetail.SubcontractNameDetailID =
dbo.tblSubcontractDetail.SubcontractNameDetailID

WHERE (dbo.tblSubcontractDetail.SubcontractDetailID = @Param1)

GO