Hello,
I am having some difficulty in updating data using a form. I am using Access 2003, with SQL Server 2005.

I have a header table (tblJobs), a sub header table (tblJobSegment) and a line detail table (tblJobSegmentDetails).
I have a SQL view (vwJobSegmentSummary) which gathers data from the JobSegmentDetails table plus a number of other tables to generate the Job Segment Totals. (TotalCost, TotalSell. - Profit and GP can then be calculated as part of the update process).

What I am trying to do is to pass the values from the JobSegmentSummary view and update them to the JobSegment table.

The fields which I am trying to update are TotalCost, TotalSell, TotalProft, and TotalGP (Gross Profit).

Generally there are multplie Job Segments entries per job.

I have a query which holds the Totals for the Job Segment Table and the Totals for the Job Segment Summary view.
From the query I have created a continuous form which opens Job Segements for a Job, and lists which Job Segments belong to the job. the fields diplayed are:
tblJobSegment - JobNo, JobSegmentNo, TotalCost, TotalSell, TotalProfit, TotalGP.
vwJobSegmentSummary - TotalCost, TotalSell, TotalProfit, TotalGP.

I have an update button in the header section of the continuous form and have hobbled together the following code:

Private Sub cmdDoUpdate_Click()
On Error GoTo Err_cmdDoUpdate_Click

Dim rst As DAO.Recordset
Dim strQuery As String

Set dbs = CurrentDb
Set rst = Forms!frmTotalsUpdate.Recordset

rst.MoveFirst
With rst
Do Until .EOF

.Edit
Me!TotalCost.Value = [TotalCostCalc]
Me!TotalSell.Value = [TotalSellCalc]
Me!Profit.Value = [TotalSellCalc] - [TotalCostCalc]

If Me!TotalSell.Value = 0 Then
Exit Sub
Else
Me!GP.Value = [GPCalc]
End If

.Update
.MoveNext

Loop

End With

Exit_cmdDoUpdate_Click:

Exit Sub

Err_cmdDoUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdDoUpdate_Click

End Sub


When the code is run, the values do actually update on the continuous form and display as expected.
However, it is not updating the values in the Job Segment Table. I feel that maybe I have not referenced the recordset correctly.
I think I'm close to solving this but can't get past this last hurdle.

Any assistance would be really appreciated.

Dave

You can't use DAO with SQL Server 2005 (or any flavor). You have to use ADO if you want this to work. This will update the forms. I assume they are either not bound, or are bound to a non-updatable query.

I would process this as a separate transaction. What options and events do you have to work with?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry for the delay in replying, I've been away.

Thanks for your answer.
The DAO vs ADO has been an area which has had me stumped on numerous occasions.
I think after reading quite a bit on this topic that I am finally strting to understand why I need to use ADO in this instance.
It means quite a change to how I am constructing this type of form, and I'm in the process of re-creating forms which will use ADO.

I suppose I'm still too early in the process to continue to ask questions in this thread, now that I've started to base this on ADO, but will come back to it once I get a little further down the track.

Thanks again for you reply.

Dave