Updatable Form When using Stored Procedure 3

[Cheryl3D]

Hello:

I have a ADP project that I'm working on using SQL Server 2000. I have a form that needs to be available for both entering new records(updatable)and viewing.

Currently, the form is bound to a stored procedure called 'procqryAnnexSummation' as the Record Source. As a result of this type of binding, I can't designate a unique table in the in the UniqueTable property for the form's properties. So while I can view this form just fine,I'm unable to edit the form as it is set to a stored procedure.

The stored procedure called 'procqryAnnexSummation' looks like this:

CREATE Procedure dbo.procqryAnnexSummation
@ID nvarchar(6)
As
SELECT DISTINCT
dbo.TabSupplier.[ID #], dbo.TabSupplier.SRAN,
dbo.TabSupplier.JULIAN, dbo.TabAnnex.[ID #] AS ID,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabSupplier.Host_ID, dbo.TabAnnex.Categories, dbo.TabAnnex.SubCategory,
dbo.TabAnnex.Support, dbo.TabAnnex.RDescription,
dbo.TabAnnex.RUnits, dbo.TabAnnex.RUnitCost,
dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription,
dbo.TabAnnex.NRUnits, dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower * dbo.TabSupplier.WorkyearCost,
0) AS NRWorkyearCost,
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost,
0) AS RCost,
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost,
0) AS NRCost, dbo.TabAnnex.Document, dbo.TabAnnex.Billed,
dbo.TabAnnex.Coll, dbo.TabSupplier.Annex_Complete,
dbo.TabSupplier.Status, SUM(dbo.qryAnnex_View.RCost)
AS RCost_Sum,
SUM(dbo.qryAnnex_View.NRWorkyearCost + dbo.qryAnnex_View.NRCost)
AS NRCost_Sum, SUM(dbo.qryAnnex_View.NRManpower)
AS Manpower_sum
FROM dbo.TabSupplier LEFT OUTER JOIN
dbo.qryAnnex_View ON
dbo.TabSupplier.[ID #] = dbo.qryAnnex_View.[ID #] LEFT OUTER JOIN
dbo.TabAnnex ON
dbo.TabSupplier.[ID #] = dbo.TabAnnex.[ID #]
WHERE dbo.TabSupplier.[ID #] = @ID
GROUP BY dbo.TabAnnex.[ID #], dbo.TabSupplier.[ID #],
dbo.TabSupplier.SRAN, dbo.TabSupplier.JULIAN,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabSupplier.Host_ID,
dbo.TabAnnex.Categories, dbo.TabAnnex.SubCategory,
dbo.TabAnnex.Support, dbo.TabAnnex.RDescription,
dbo.TabAnnex.RUnits, dbo.TabAnnex.RUnitCost,
dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription,
dbo.TabAnnex.NRUnits, dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower * dbo.TabSupplier.WorkyearCost,
0),
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost,
0),
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost,
0), dbo.TabAnnex.Document, dbo.TabAnnex.Billed,
dbo.TabAnnex.Coll, dbo.TabSupplier.Annex_Complete,
dbo.TabSupplier.Status

Should I make the form unbound and use ADO code to set up a dynamic Record Source for the form? Then use code to set the UniqueTable property?

The unique table, if I can assign it would be 'TabAnnex'.

If this is the correct way to make it work (making the form updatable), then can someone provide a sample as to how the code would look?

Any assistance in this matter is greatly appreciated.

Thanks,
Cheryl3D

 

[cmmrfrds]

Even if you could bind to this stored procedure it is not updateable. The Select statement is an aggregation. Only unique records are updateable. Return a recordset with unique records and then you can update them.

 

[Cheryl3D]

Thanks cmmrfrds.

But what alternatives do I have in this case? I need to perform the aggregation (summing) on some the fields. Can I possibly use a nested query or something to make it updatable? Or can I use a separate query without the summation?

Here is the original query in MS Access 2000 that I converted to the stored procedure that you saw earlier:

SELECT DISTINCTROW TabSupplier.[ID #],
TabSupplier.SRAN,
TabSupplier.JULIAN,
TabAnnex.[ID #],
[TabSupplier]![ID #] AS ID,
TabSupplier.Agreement,
TabSupplier.Supplier,
TabSupplier.Receiver,
TabSupplier.WorkyearCost,
TabAnnex.Categories,
TabAnnex.SubCategory,
TabAnnex.Support,
TabAnnex.RDescription,
TabAnnex.RUnits,
TabAnnex.RUnitCost,
TabAnnex.NRManpower,
TabAnnex.NRDescription,
TabAnnex.NRUnits,
TabAnnex.NRUnitCost,
(IIf([NRManpower]=0 Or [NRManpower] Is Null,0,([NRManpower]*[WorkyearCost]))) AS NRWorkyearCost,
(IIf([RUnits]=0 Or [RUnits] Is Null,0,([RUnits]*[RUnitCost]))) AS RCost, (IIf([NRUnits]=0 Or [NRUnits] Is Null,0,([NRUnits]*[NRUnitCost]))) AS NRCost, TabAnnex.Document,
TabAnnex.Billed,
TabAnnex.Coll,
TabSupplier.Annex_Complete,
TabSupplier.Status
FROM TabSupplier LEFT JOIN TabAnnex ON TabSupplier.[ID #] = TabAnnex.[ID #];

As you can see there still was some aggregation taking place where certain fields are being multipled by others (e.g.[NRUnits]*[NRUnitCost]). Yet, in MS Acccess the query above is updatable.

Is there any way that I can get around the aggregation?

Cheryl3D

 

[cmmrfrds]

SQL Server needs something unique to identify the record to update, usually this is the primary key that is indexed but it could be a timestamp field in the record which is also unique. On the sql statement you show the iif function that needs to be converted to a comparable sql server function/replacement.

TabAnnex.NRUnitCost,
(IIf([NRManpower]=0 Or [NRManpower] Is Null,0,([NRManpower]*[WorkyearCost]))) AS NRWorkyearCost,
(IIf([RUnits]=0 Or [RUnits] Is Null,0,([RUnits]*[RUnitCost]))) AS RCost, (IIf([NRUnits]=0 Or [NRUnits] Is Null,0,([NRUnits]*[NRUnitCost]))) AS NRCost, TabAnnex.Document,
TabAnnex.Billed,

Check into the Case Statement in sql server.

It is something like. Please lookup the syntax.

TabAnnex.NRUnitCost,
CASE
WHEN NRManpower IS NULL THEN (NRManpower *
WorkyearCost)
WHEN NRManpower = 0 THEN (NRManpower *
WorkyearCost)
ELSE 0
END AS NRWorkyearCost,
TabAnnex.Document,
TabAnnex.Billed,

 

[Cheryl3D]

Okay cmmrfrds:

I have added a unique (primary) key to the 'TabAnnex' table that would be the table assigned to the UniqueTable property.

Here is the altered stored procedure:


Alter Procedure procqryAnnexSummation
@ID nvarchar(6)
As
SELECT DISTINCT
dbo.TabSupplier.[ID #], dbo.TabSupplier.SRAN,
dbo.TabSupplier.JULIAN, dbo.TabAnnex_New.[ID #] AS ID,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabSupplier.Host_ID, dbo.TabAnnex_New.Categories,
dbo.TabAnnex_New.SubCategory, dbo.TabAnnex_New.Support,
dbo.TabAnnex_New.RDescription, dbo.TabAnnex_New.RUnits,
dbo.TabAnnex_New.RUnitCost,
dbo.TabAnnex_New.NRManpower,
dbo.TabAnnex_New.NRDescription,
dbo.TabAnnex_New.NRUnits, dbo.TabAnnex_New.NRUnitCost,
COALESCE (dbo.TabAnnex_New.NRManpower * dbo.TabSupplier.WorkyearCost,
0) AS NRWorkyearCost,
COALESCE (dbo.TabAnnex_New.RUnits * dbo.TabAnnex_New.RUnitCost,
0) AS RCost,
COALESCE (dbo.TabAnnex_New.NRUnits * dbo.TabAnnex_New.NRUnitCost,
0) AS NRCost, dbo.TabAnnex_New.Document,
dbo.TabAnnex_New.Billed, dbo.TabAnnex_New.Coll,
dbo.TabSupplier.Annex_Complete, dbo.TabSupplier.Status,
SUM(dbo.qryAnnex_View.RCost) AS RCost_Sum,
SUM(dbo.qryAnnex_View.NRWorkyearCost + dbo.qryAnnex_View.NRCost)
AS NRCost_Sum, SUM(dbo.qryAnnex_View.NRManpower)
AS Manpower_sum, dbo.TabAnnex_New.s_GUID
FROM dbo.TabSupplier LEFT OUTER JOIN
dbo.qryAnnex_View ON
dbo.TabSupplier.[ID #] = dbo.qryAnnex_View.[ID #] LEFT OUTER JOIN
dbo.TabAnnex_New ON
dbo.TabSupplier.[ID #] = dbo.TabAnnex_New.[ID #]
WHERE (dbo.TabSupplier.[ID #] = @ID)
GROUP BY dbo.TabAnnex_New.[ID #], dbo.TabSupplier.[ID #],
dbo.TabSupplier.SRAN, dbo.TabSupplier.JULIAN,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabSupplier.Host_ID, dbo.TabAnnex_New.Categories,
dbo.TabAnnex_New.SubCategory, dbo.TabAnnex_New.Support,
dbo.TabAnnex_New.RDescription, dbo.TabAnnex_New.RUnits,
dbo.TabAnnex_New.RUnitCost,
dbo.TabAnnex_New.NRManpower,
dbo.TabAnnex_New.NRDescription,
dbo.TabAnnex_New.NRUnits, dbo.TabAnnex_New.NRUnitCost,
COALESCE (dbo.TabAnnex_New.NRManpower * dbo.TabSupplier.WorkyearCost,
0),
COALESCE (dbo.TabAnnex_New.RUnits * dbo.TabAnnex_New.RUnitCost,
0),
COALESCE (dbo.TabAnnex_New.NRUnits * dbo.TabAnnex_New.NRUnitCost,
0), dbo.TabAnnex_New.Document, dbo.TabAnnex_New.Billed,
dbo.TabAnnex_New.Coll, dbo.TabSupplier.Annex_Complete,
dbo.TabSupplier.Status, dbo.TabAnnex_New.s_GUID

The new field is called 's_GUID'.

Now I need to ADO code to set up a dynamic Record Source for the form.

Thanks,

Cheryl3D

 

[cmmrfrds]

I didn't communicate very effectively. Anytime there is a "group by" the query is an aggregation. Why can't you set up a Main Form and Sub Form relationship and treat the table relationship as in an Access MDB. Access has dynasets which are unigue and allow the update of multiple tables. This is not true in ANSI SQL so each table much be maintained individually. I don't know the data model so I can't say which table is the one side of the one to many relationship.

 

[Draknor39]

Just in case someone doesn't know this, you can update records in a form bound to a Stored Procedure.

By reading Cheryl3D's original post you may think this isn't possible. However you will have to set the Unique Table property through vba. Something Like Me.UniqueTable = "Clients". As Access won't let you set this property using the property sheet in design view.

Of course this won't work if the stored procedure is an aggregation.

Hope this helps someone.