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.Execute 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_Click

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

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

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


'Update new Battery data
Set rstEmployees = HPDB.OpenRecordset("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.Close

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

'Save to Log
Set rstEmployees = HPDB.OpenRecordset("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.Close


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_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox Err.Description
Resume Exit_Command41_Click

End Sub

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

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

Private Sub List39_AfterUpdate()


'MsgBox Me![List39].Value

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

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