update if "data" exists in table else then (repost)

[wshm]

this continous from thread181-1344029

tbl_PSCtx can have duplicate contract numbers but
tbl_PSC should only have one contract number.
its like total

and i forgot to think that
before update, If 'contractNumber' already exists in
tbl_PSC, only one field needs to be updated.

so im thinking something like this.

If Contract Number exists in tbl_PSC(database)already
Then Update tbl_PSC.[UpdatedContAmt] =
frm_PSC_new.[TranxAmt] + tbl_PSCtx.[ContAmt]
Where 'contractNo' = 'contractNo'

Else
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tbl_PSC", dbOpenDynaset)
RS.AddNew
RS![Bmonitor] = Me![cboBmonitor]
RS![ContractNo] = Me![txtContractNo]
RS![ContractName] = Me![txtcontractName]
RS![Description] = Me![TxtDescription]
RS![PaID] = Me![cboPaID]
RS![ContractAmt] = Me![txtTranxAmt]
RS![Bfsix] = Me![cboBfsix]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

im having hard time coming up /w the coding for the top part... any help?

 

[wshm]

i played around with it and i think this might work..

Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tbl_PSC", dbOpenDynaset)
Set bs = DB.OpenRecordset("tbl_PSCtx", dbOpenDynaset)

If Me.txtContractNo = DLookup("ContractNo", "tbl_PSC", "contractno = '" & Me![txtContractNo] & "'") Then
RS.Edit
RS![UpdatedConAmt] = Me![txtTranxAmt] + bs![TranxAmt]
RS.Update

Else


Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tbl_PSC", dbOpenDynaset)
RS.AddNew
RS![Bmonitor] = Me![cboBmonitor]
RS![ContractNo] = Me![txtContractNo]
RS![ContractName] = Me![txtcontractName]
RS![Description] = Me![TxtDescription]
RS![PaID] = Me![cboPaID]
RS![ContractAmt] = Me![txtTranxAmt]
RS![Bfsix] = Me![cboBfsix]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

End If