Updated info in data and save activities done into a Log Table

[desserts]

Hi,

I am creating a button that enables to perform an update or a swap of information from old to new. Example, same User but change of old address to new address after I select from a list option.

At the same time, when the update is perform, the old information is saved into a Log Table.

I got error after I program the script "Field cannot be updated".

Pls advise.

Thanks

[Stewart Ross]

Hi desserts. You'll need to post the code you are using so we can help you with this error. There are many ways to accomplish an update, but without seeing what you are doing we cannot help you pinpoint the problem.

The simplest approach to such updates is to build an SQL statement as a string and use the DoCmd.RunSQL or CurrentDB.Execu te commands to run the SQL, but like all update queries (whether run from code or from the query editor) the resultant query has to be updatable for it to work. It is possible to build a correctly-formatted SQL statement which is not updatable, and we cannot guess at this without seeing what you are doing.

Please post your code, including any SQL you generate, so we can advise further.

-Stewart

[desserts]

Hi Stewart,

I'd appreciate the help. Here is the code

Private Sub Command38_Click ()
On Error GoTo Err_Command38_C lick

If MsgBox("Confirm ?", 1) = 2 Then
Exit Sub
End If
If [Status] = "Assigned" Then
MsgBox "Status is not changed"
Exit Sub
End If

DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Set HPDB = CurrentDb
HPno = [Battery_BattID]
BattID = [Emp Data_HP no]


'Update new Battery data
Set rstEmployees = HPDB.OpenRecord set("Battery")
With rstEmployees
.Index = "PrimaryKey "
.Seek "=",[List39].Value
.Edit
![Status] = "Assigned"
'If ![Date First Issued] = "" Then
' ![Date First Issued] = Date
'End If
.Update
End With
rstEmployees.Cl ose

'Update link in Battery Table
Set rstEmployees = HPDB.OpenRecord set("Emp Data")
With rstEmployees
.Index = "PrimaryKey "
.Seek "=", HPno
.Edit
![BattID] =[List39].Value
.Update
End With
rstEmployees.Cl ose

'Save to Log
Set rstEmployees = HPDB.OpenRecord set("Log Job", dbOpenTable)
With rstEmployees
.AddNew
![Date Reported] = Date & " " & Time
![HP Number] = HPno
![BattID] = BattID
![Replacement BattID] =[List39].Value
![Problem Definition] = [Text18]


![User Name] = [User Name]
![UserID] = [UserID]
![Dept] = [Dept]
![Remark] = [Remark] '& " Warranty Date: " & [Warranty Date] & ". "
![Status] = [Status]
If ([Status] = "Lost") Or ([Status] = "Repairing" ) Or ([Status] = "Beyond Repair") Then
![Out] = Date
![In] = Date
End If
![Out] = Date
.Update
End With
rstEmployees.Cl ose


HPDB.Close
DoCmd.Close
MsgBox "Updated"
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Swap Battery"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command38_ Click:
Exit Sub

Err_Command38_C lick:
MsgBox Err.Description
Resume Exit_Command38_ Click

End Sub

Private Sub Command41_Click ()
On Error GoTo Err_Command41_C lick


Screen.Previous Control.SetFocu s
DoCmd.DoMenuIte m acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command41_ Click:
Exit Sub

Err_Command41_C lick:
MsgBox Err.Description
Resume Exit_Command41_ Click

End Sub

Private Sub Combo25_AfterUp date()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Cl one
rs.FindFirst "[HP Number] = '" & Me![Combo25] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub List39_AfterUpd ate()


'MsgBox Me![List39].Value

Set HPDB = CurrentDb
Set rstEmployees = HPDB.OpenRecord set("Battery")
With rstEmployees
.Index = "PrimaryKey "
.Seek "=",[List39].Value
[Text43] = ![BattID]
[Text45] = ![Battery]
[Text49] = ![Warranty Date]
End With
rstEmployees.Cl ose

HPDB.Close

End Sub

[Stewart Ross]

Thanks for the code listing. You are not using SQL updates, which are simpler than using recordsets to do so. Looking at the recordset code overall you are using Edit/Update OK, but the updates are dependent on the seeks resulting in a match. I would recommend you verify that the record you seek is indeed being found. Otherwise it is not at all obvious from this end what might be going wrong.

In what line of the code posted does the error occur? What steps have you taken so far to set breakpoints, step through the code, and test values as the code runs? What have you seen in the debugger when you look at values for the various fields involved?

-Stewart