SQL Update Query

[mcktigger]

Hi

Hoping someone can help with this problem. I have the following code on frmpricing when the record is saved.

For Each Ctl In Me.Controls
If Ctl.Tag = "QuoteLine" Then
If Ctl.Value > 0 Then
iProductID = DLookup("[ProductID]", "tblpricelist", "[FormName] = '" & Ctl.Name & "'")
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tblQuoteLines (QuoteID, PricingId, Description, ProductID, SellPrice) VALUES (" & Me.QuoteID & "," & Me.PricingId & ",'" & Me.JobDescription & "'," & iProductID & "," & Ctl.Value & ")")
DoCmd.SetWarnings True
End If
End If
Next

This created multiple records in tblquotelines if the value in the Sell Price is > 0. This all works perfectly. However the problem I now have is when the user goes back to edit a tblpricing record. 2 problems

Problem 1 - If the user amends a Sell Price in tblpricing I need to update the exisiting records in tblquotelines but I'm not sure of the syntax for this. tblpricing and tblquotelines is a 1 to M with pricingid as the foreign key in tblquotelines.

Problem 2 - When the user goes back to tblpricing the can add enter a Sell Price if they want to add another product which I need to add to tblquotelines. However if I use the above code I would get duplicate records for those records added previosuly. I amended the code as follows

For Each Ctl In Me.Controls
If Ctl.Tag = "QuoteLine" Then
if Ctl.OldValue = 0 then
If Ctl.Value > 0 Then
etc etc

I thought that this would add new records for only those records with an oldvalue of zero but it doesn't do anything. No error or tblupdate. Also tried
If Ctl.Tag = "QuoteLine" Then
if Ctl.OldValue = 0 and If Ctl.Value > 0 Then
etc etc

This didn't work either. Sorry for the waffle but any help would be grately appreciated.

 

[mdlueck]

You need to issue an UPDATE rather than an INSERT SQL statement when modifying a record. Use the WHERE clause of the UPDATE statement to correctly identify which record(s) you want to be updated.

Example from a Stored Procedure of mine:

BEGIN TRAN
UPDATE [dbo].[products]
SET [authid]=@authid,
[logtimestamp]=CURRENT_TIMESTAMP,
[projectid]=@projectid,
[title]=@title,
[productnumber]=@productnumber,
[bomcad]=@bomcad
WHERE (([id]=@id) AND ([logtimestamp]=@logtimestamp));
COMMIT TRAN

So not only must it be the unique ID number, but the logtimestamp must not have changed... prevents two people editing the same record at the same time.