Update Table from "Update Form"

[mcmichael13]

Ok so I was thinking last night about the database I have created, and I came to the conclusion that I need another form for updating existing entries. The RMA gets entered into the system but needs to be updated when we actually receive the parts and also when the case is closed.

Therefore I need to have a form to lookup the stored values, allow the user to make changes, then overwrite the old record.

So far I have the form pulling all the data from the table and the user is able to change the data, but now I need to update the table.

Yes the form is unbound.


Code:

Dim db As DAO.Database
Dim Rslt As DAO.Recordset

Set db = CurrentDb()

Set Rslt = db.OpenRecordset("RMA")

Rslt.Edit
Rslt("RMA") = RMAnumu
Rslt("Customer") = CustomerNameu
Rslt("Location") = Locationu
Rslt("DateIssued") = DIu
Rslt("DateReceived") = DRu
Rslt("DateClosed") = DCu
Rslt("PartName") = PNu
Rslt("Quantity") = Qtyu
Rslt("Quality") = Qualityu
Rslt("ReasonForReturn") = RFRu
Rslt("TotalValue") = TVu
Rslt("Comments") = Commentsu
Rslt.Update


MsgBox "RMA has been updated."


That is the code to update, but there is an error because it won't allow me to create a duplicate Primary Key entry. Thats not what I want though, I want to update that Primary Key entry. RMA is my Primary Key for the table.

Thanks,

Rob

 

[mcmichael13]

perhaps if i first coded the removal of the entry, then used this to re-enter it... that would work...

any suggestions for this or another method?

 

[mcmichael13]

nevermind i solved it with one line of code... (mind you its a long line haha)


DoCmd.RunSQL "UPDATE RMA Set Customer=[Forms]![UpdateRMAList]![CustomerNameu], Location=[Forms]![UpdateRMAList]![Locationu],DateIssued=[Forms]![UpdateRMAList]![DIu], DateReceived=[Forms]![UpdateRMAList]![DRu], DateClosed=[Forms]![UpdateRMAList]![DCu], PartName=[Forms]![UpdateRMAList]![PNu], Quality=[Forms]![UpdateRMAList]![Qualityu], ReasonforReturn=[Forms]![UpdateRMAList]![RFRu], Quantity=[Forms]![UpdateRMAList]![Qtyu], TotalValue=[Forms]![UpdateRMAList]![TVu], Comments=[Forms]![UpdateRMAList]![Commentsu] WHERE RMA=[Forms]![UpdateRMAList]![RMAnumu]"