Detecting if data exists on "Insert Into" and changing the value in field if exists!

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I am using the following Insert code which inserts data into the table
"tblOrder_Details" which is then displayed in a datasheet subform.

What I need to do is if the data already exists then take a value from
Forms![frmOrder_Form]![numNumber] (Unbound) and add it to the value already
in [numQuantity] in the "tblOrder_Details" thefore increasing the amount
ordered without inserting further lines.

The data is matched on the [strProduct_Code] of "tblOrder_Details"

strsql = "Insert Into tblOrder_Details
(strProduct_Code,strProduct_Description,curRetail_Price,numSize,numQuantity,
strproduct_Identifyer,curNet_SSP_Price,id) Values ('" & Me!strProduct _
& "','" & Me!strDescription _
& "' ,'" & curGross _
& " ','" & strSize _
& "','" & numNumber _ 'Add this value to [numQuantity] in the table
"tblOrder_Details"
& "','" & strGoods_Category _
& "','" & curNet_SSP_Price _
& "','" & id & "')"

Any help greatfully recieved.

Les.
 
You should check if it exists before the insert. You can use dlookup or
dcount to do this. I am assuming that id is unique to the order and is
available on the form.
If you were to use dcount, it would look something like this:

If DCount("strProduct_Code", "ShareRegister", "[id] = " & Me!id & _
"[strProduct_Code] = " & Me!strProduct) > 0 Then

intTotalQty = Dlookup("numQuantity","tblOrder_Details", "[id] = " &
Me!id & _
"[strProdcut_Code] = " & Me!strProduct) + Me!numNumber
strsql="UPDATE tblOrder_details SET numQuantity = intTotalQty " & _
"WHERE tbleOrder_Details.id = Me!id AND
tbleOrder_Details.strProduct_Code = Me!strProduct"

Else

...insert your code here...

End If
 
George
Thanks for this I have adapted it to the code below as I do not use id in
this instance.

Although I get the correct value showing in "intTotalQty" I cant get it to
update the table. Can you assist further please and point out where the
UPDATE code is wrong.

If DCount("[strProduct_Code]", "tblOrder_details", "[strProduct_Code] = '" &
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity = intTotalQty " & _
"WHERE tblOrder_Details = Me!strProduct"

Thanks

Les




George Lob said:
You should check if it exists before the insert. You can use dlookup or
dcount to do this. I am assuming that id is unique to the order and is
available on the form.
If you were to use dcount, it would look something like this:

If DCount("strProduct_Code", "ShareRegister", "[id] = " & Me!id & _
"[strProduct_Code] = " & Me!strProduct) > 0 Then

intTotalQty = Dlookup("numQuantity","tblOrder_Details", "[id] = " &
Me!id & _
"[strProdcut_Code] = " & Me!strProduct) + Me!numNumber
strsql="UPDATE tblOrder_details SET numQuantity = intTotalQty " & _
"WHERE tbleOrder_Details.id = Me!id AND
tbleOrder_Details.strProduct_Code = Me!strProduct"

Else

...insert your code here...

End If

Les said:
I am using the following Insert code which inserts data into the table
"tblOrder_Details" which is then displayed in a datasheet subform.

What I need to do is if the data already exists then take a value from
Forms![frmOrder_Form]![numNumber] (Unbound) and add it to the value already
in [numQuantity] in the "tblOrder_Details" thefore increasing the amount
ordered without inserting further lines.

The data is matched on the [strProduct_Code] of "tblOrder_Details"

strsql = "Insert Into tblOrder_Details
Click to expand...
(strProduct_Code,strProduct_Description,curRetail_Price,numSize,numQuantity,
strproduct_Identifyer,curNet_SSP_Price,id) Values ('" & Me!strProduct _
& "','" & Me!strDescription _
& "' ,'" & curGross _
& " ','" & strSize _
& "','" & numNumber _ 'Add this value to [numQuantity] in the table
"tblOrder_Details"
& "','" & strGoods_Category _
& "','" & curNet_SSP_Price _
& "','" & id & "')"

Any help greatfully recieved.

Les.
Click to expand...
Click to expand...
 
Back
Top