Morning all.
Trying to educate myself on how to update a record selected from a list box on a form.
This is what I have and while it somewhat works, it's not correct. Errors listed at the end of the post.
Form Name: (Cost Center Details)
In the form, I have an unbound list box (lstbxCostCenter). The list box is populated via a query (Query1)
Query1 SQL: (Not sure if it is correct but it works to populate the list box)
The form has unbound text boxes:
txtID; txtLocation; txtCompanyNumber; txtCostCenter; txtDescription
Event for the list box to populate the text boxes: (On Dbl Click)
This works to populate the textboxes on the form. I want to be able to edit the selected record from the text boxes.
The event I am using to save the updated record I found via a search and is as followed:
Two issues:
1 - The instead of updating the selected record, the code is creating a NEW RECORD.
2 - I get this error: "The data has been changed." Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.
I appreciate all the help the community has given.
Cheers!
-Kyp
Trying to educate myself on how to update a record selected from a list box on a form.
This is what I have and while it somewhat works, it's not correct. Errors listed at the end of the post.
Form Name: (Cost Center Details)
In the form, I have an unbound list box (lstbxCostCenter). The list box is populated via a query (Query1)
Query1 SQL: (Not sure if it is correct but it works to populate the list box)
SELECT CostCenter.ID, CostCenter.Location, CostCenter.CompanyNumber, CostCenter.CostCenter, CostCenter.Description FROM CostCenter ORDER BY CostCenter.Location;
The form has unbound text boxes:
txtID; txtLocation; txtCompanyNumber; txtCostCenter; txtDescription
Event for the list box to populate the text boxes: (On Dbl Click)
Code:
Private Sub lstbxCostCenter_DblClick(Cancel As Integer)
Me.txtID = Me.lstbxCostCenter.Column(0)
Me.txtLocation = Me.lstbxCostCenter.Column(1)
Me.txtCompanyNumber = Me.lstbxCostCenter.Column(2)
Me.txtCostCenter = Me.lstbxCostCenter.Column(3)
Me.txtDescription = Me.lstbxCostCenter.Column(4)
End Sub
This works to populate the textboxes on the form. I want to be able to edit the selected record from the text boxes.
The event I am using to save the updated record I found via a search and is as followed:
Code:
Private Sub cmdSaveUpdate_Click()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from CostCenter")
rec.Edit
rec("Location") = Me.txtLocation
rec("CompanyNumber") = Me.txtCompanyNumber
rec("CostCenter") = Me.txtCostCenter
rec("Description") = Me.txtDescription
rec.Update
rec.Close
Set rec = Nothing
Set db = Nothing
Me.lstbxCostCenter.Requery
Me.txtLocation.Value = ""
Me.txtCompanyNumber.Value = ""
Me.txtCostCenter.Value = ""
Me.txtDescription.Value = ""
End Sub
Two issues:
1 - The instead of updating the selected record, the code is creating a NEW RECORD.
2 - I get this error: "The data has been changed." Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.
I appreciate all the help the community has given.
Cheers!
-Kyp