Updating reocrd in table/query

[johnbongrovey]

I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.
If the "RecordUpdate" is just specified as a number in the query it is fine.

CurrentDb.Execute "UPDATE QryIssueData " & _
" SET RecordUpdate =" & Me.txtRecordNum & _
", IssueDate ='" & Me.txtIssueDate & "'" & _
", Equipment ='" & Me.cboEquipment & "'" & _
", IssueDocNumber ='" & Me.txtIssue & "'" & _
", ReturnDocNum ='" & Me.txtReturnDocNum & "'" & _
", ReturnDate ='" & Me.txtReturnDate & "'" & _
", Laborer ='" & Me.cboLaborerAss & "'" & _
", Remarks ='" & Me.txtRemarks & "'" & _
", PlantAssigned ='" & Me.cboPlant & "'" & _
", EquipmentLocation ='" & Me.txtEquip & "'" & _
" WHERE RecordUpdate =" & Me.txtRecordNum

 

[pbaldy]

Normally you would not include the autonumber field in the SET portion of the query. It isn't changing anyway, so there's no need for it there.

 

[MarvinM]

Hi John,

Paul is right. You do not need to SET every field in your target record. You are using the RecordUpdate field in your WHERE clause. So just try leaving it out of the SET clause, like this:

CurrentDb.Execute "UPDATE QryIssueData " & _
" SET IssueDate ='" & Me.txtIssueDate & "'" & _
", Equipment ='" & Me.cboEquipment & "'" & _
", IssueDocNumber ='" & Me.txtIssue & "'" & _
", ReturnDocNum ='" & Me.txtReturnDocNum & "'" & _
", ReturnDate ='" & Me.txtReturnDate & "'" & _
", Laborer ='" & Me.cboLaborerAss & "'" & _
", Remarks ='" & Me.txtRemarks & "'" & _
", PlantAssigned ='" & Me.cboPlant & "'" & _
", EquipmentLocation ='" & Me.txtEquip & "'" & _
" WHERE RecordUpdate =" & Me.txtRecordNum & ";"

HTH
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
-------------------------------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
-------------------------------------------------------------------------------------------------------------------

 

[johnbongrovey]

Thanks Paul & Marvin
Works fine and a better understanding of the syntax