Check if record exists, if it does then update value

[A_Dunbar]

Hi guys,

I have just started using access this week after making the jump from excel. So far, i have pretty much taught myself enough to get me to the point i am at now...which is where i get stuck.

I am creating a new table which info from another table. My code checks to see if the record already exists, and if it doesn't then it adds the record and the required info. Where I am stuck is if the record does exist then I want to update that record. The code I am using is below...(I have additional code in here and therefore defined all variables etc previously)...

Any help/guidance is greatly appreciated...

DoCmd.CopyObject , "Receipts Loyalty - " & strReceiptsPeriod, acTable, "Receipts Loyalty BLANK"
Set rsReceipts = CustomerProfitability.OpenRecordset("Receipts - " & strReceiptsPeriod)
Set rsReceiptsLoyalty = CustomerProfitability.OpenRecordset("Receipts Loyalty - " & strReceiptsPeriod, dbOpenTable)
Do Until rsReceipts.EOF = True
strDimension = rsReceipts("Dimension")
strForm = rsReceipts("Form")
strGrade = rsReceipts("Grade")
strSize = rsReceipts("Size")
strReceiptWeight = rsReceipts("ReceiptWeight")

strAlreadyAdded = DCount("[Dimension]", "Receipts Loyalty - " & strReceiptsPeriod, _
"[Dimension]= '" & strDimension & "'")

If rsReceipts("SupplierClassification").Value = "Exclude" Then
strTataSupportedWeight = 0
strTataNonSupportedWeight = 0
strNonTataWeight = 0
strExcludeWeight = strReceiptWeight
strIrelandWeight = strTataSupportedWeight + strTataNonSupportedWeight + _
strNonTataWeight + strExcludeWeight

ElseIf rsReceipts("SupplierClassification").Value = "Tata Supported" Then
strTataSupportedWeight = strReceiptWeight
strTataNonSupportedWeight = 0
strNonTataWeight = 0
strExcludeWeight = 0
strIrelandWeight = strTataSupportedWeight + strTataNonSupportedWeight + _
strNonTataWeight + strExcludeWeight

ElseIf rsReceipts("SupplierClassification").Value = "Tata Non-Supported" Then
strTataSupportedWeight = 0
strTataNonSupportedWeight = strReceiptWeight
strNonTataWeight = 0
strExcludeWeight = 0
strIrelandWeight = strTataSupportedWeight + strTataNonSupportedWeight + _
strNonTataWeight + strExcludeWeight

ElseIf rsReceipts("SupplierClassification").Value = "Non Tata" Then
strTataSupportedWeight = 0
strTataNonSupportedWeight = 0
strNonTataWeight = strReceiptWeight
strExcludeWeight = 0
strIrelandWeight = strTataSupportedWeight + strTataNonSupportedWeight + _
strNonTataWeight ' + strExcludeWeight
End If

If strAlreadyAdded = 0 Then
rsReceiptsLoyalty.AddNew
rsReceiptsLoyalty("Form").Value = strForm
rsReceiptsLoyalty("Grade").Value = strGrade
rsReceiptsLoyalty("Size").Value = strSize
rsReceiptsLoyalty("Dimension").Value = strDimension
rsReceiptsLoyalty("Tata Supported").Value = strTataSupportedWeight
rsReceiptsLoyalty("Tata Non-Supported").Value = strTataNonSupportedWeight
rsReceiptsLoyalty("Non Tata").Value = strNonTataWeight
rsReceiptsLoyalty("Exclude").Value = strExcludeWeight
rsReceiptsLoyalty("Ireland").Value = strIrelandWeight
rsReceiptsLoyalty("Loyalty").Value = (strTataSupportedWeight / strIrelandWeight)
rsReceiptsLoyalty.Update
Else
Set rsLoyaltyUpdate = CustomerProfitability.OpenRecordset("Receipts Loyalty - " & strReceiptsPeriod)
' 'rsLoyaltyUpdate.Refresh
' DoCmd.FindRecord [strDimension], acEntire, False, acSearchAll, , acAll, True
rsLoyaltyUpdate.Edit
rsLoyaltyUpdate("Tata Supported").Value = rsLoyaltyUpdate("Tata Supported").Value + _
strTataSupportedWeight
rsLoyaltyUpdate("Tata Non-Supported").Value = rsLoyaltyUpdate("Tata Non-Supported").Value + _
strTataNonSupportedWeight
rsLoyaltyUpdate("Non Tata").Value = rsLoyaltyUpdate("Non Tata").Value + _
strNonTataWeight
rsLoyaltyUpdate("Exclude").Value = rsLoyaltyUpdate("Exclude").Value + _
strExcludeWeight
rsLoyaltyUpdate("Ireland").Value = rsLoyaltyUpdate("Ireland").Value + _
strIrelandWeight
rsLoyaltyUpdate("Loyalty").Value = rsLoyaltyUpdate("Tata Supported").Value / _
rsLoyaltyUpdate("Ireland").Value
rsLoyaltyUpdate.Update

End If

rsReceipts.MoveNext
Loop
MsgBox "Receipts Loyalty table for " & strReceiptsPeriod & " Complete"

Thanks for taking the time to look at this,

Andrew

 

[LPurvis]

Hi, welcome to AWF.

What's the actual, specific, problem that you're having?
You've tried to implement the UPDATE or APPEND philosophy, but rather than one of us pouring over the code to see what could be wrong (time consuming much?) if you narrow it down then you'll get more responses I'll wager.

Cheers.

 

[A_Dunbar]

Hi LPurvis,

Thanks for the heads up...

Basically, I have tried setting up code which looks at the receipted material and then looks to see if the material is on a master table. If it isn't then the material is added to the master table with the weight of the receipt, if it is already on the master table then the current material receipt weight needs to be updated to include the new receipt weight. It is this updating that I cannot get to work. Can you advise an efficient approach to take??

Thanks again,

Andrew