Solved Need Assistance: How to update record from form listbox? (1 Viewer)

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
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)
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
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Jan 23, 2006
Messages
14,317
My first reaction is to adjust this line

Code:
Set rec = db.OpenRecordset("Select * from CostCenter")

to identify the record that was Selected in the listbox.

eg: where identifyingField = Me.txtWhatever uniquely identifies the record
 
  • Like
Reactions: Kyp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:17
Joined
May 7, 2009
Messages
16,393
if you use Bound form(to CostCenter table), you don't need code to update the record.
what you need is to Change the code for the listbox double-click event:
Code:
Private Sub lstbxCostCenter_DblClick(Cancel As Integer)
DoCmd.SearchForRecord , , acFirst, "ID = " & Me!lstbxCostCenter
End Sub
 

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
@jdraw Thanks for the quick reply!

Looking at https://docs.microsoft.com at the "ListBox.ItemsSelected Property"
I used the example code in my list box double click event and watched the immediate window for the result and can see the selected record ID:
Code:
Private Sub lstbxCostCenter_DblClick(Cancel As Integer)
 Dim frm As Form, ctl As Control
 Dim varItm As Variant
 
 Set frm = Forms![Cost Center Details]
 Set ctl = frm!lstbxCostCenter
 For Each varItm In ctl.ItemsSelected
 Debug.Print ctl.ItemData(varItm)
 Next varItm
End Sub

I think I will need to use logic that involves the record ID to ensure I am updating the correct record.
If I am correct, where can I find information on this process? I am interested in the theory behind the logic as well.

Cheers!
-Kyp
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Jan 23, 2006
Messages
14,317
I think I will need to use logic that involves the record ID to ensure I am updating the correct record.
If I am correct, where can I find information on this process? I am interested in the theory behind the logic as well
.


You only select 1 row in the listbox, right?
You want to edit the record in the table/recordset that matches the value you selected in the listbox.
 
  • Like
Reactions: Kyp

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
Yes, that is correct.
One record selected from the listbox populates the text boxes on the form.
Make the changes in the text boxes and save the updated record.
CostCenterDetails.png
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Jan 23, 2006
Messages
14,317
CostCenterID is numeric, right?

Try this
Set rec = db.OpenRecordset("Select * from CostCenter WHERE CostCenter.ID =" & me.txtID )
 
  • Like
Reactions: Kyp

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
CostCenterID is numeric, right?

Try this
Set rec = db.OpenRecordset("Select * from CostCenter WHERE CostCenter.ID =" & me.txtID )
Yes the CostCenterID is numeric.
I will give it a try.

Cheers!
-Kyp
 

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
Changing the statement seems to work for updating the record however, I am still getting this error.
I was able to fix "The data has been changed" message with Me.Requery at the end of the logic.

ErrorMessage.png


Thanks for the assistance!

Cheers!
-Kyp
 
Last edited:

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
@jdraw
The completed logic that properly works, appreciate your help!

Code:
Private Sub cmdSaveUpdate_Click()
  Dim frm As [Form_Cost Center Details]
  Dim db As Database
  Dim rec As Recordset
      Set db = CurrentDb
      Set rec = db.OpenRecordset("Select * from CostCenter WHERE CostCenter.ID =" & Me.txtID)
          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
      With frm
          txtID.Value = ""
          txtLocation.Value = ""
          txtCompanyNumber.Value = ""
          txtCostCenter.Value = ""
          txtDescription.Value = ""
          Requery
      End With
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:17
Joined
Feb 28, 2001
Messages
22,778
If the record is bound to a form, it is possible that you are stepping on yourself. The form has the recordset open for update (if the form.AllowEdit property is true) AND you are using a separately opened recordset to update the particular record. Note that Access didn't tell you whether that error came from the recordset or the form. The form has a lock on the buffer that contains the record so will detect that a change has occurred. That is often the source of that particular problem.

Your solution is usually to just use the listbox or combobox to make the selected record become the current record. Then use Me.controlname syntax to update the record in the form and force a .Refresh so that you can see the changes. That way there is only one channel open for update to that record - unless there really IS another user diddling with the same record.
 
  • Like
Reactions: Kyp

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
@The_Doc_Man
Thanks for the explanation. I am always looking for the "Theory of operation", and "Cause and effect".
For me, knowing the theory as well as the cause/effect helps me to fully understand the logic and further educate myself.

Cheers!
-Kyp
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Jan 23, 2006
Messages
14,317
You are very welcome. I agree with arnelgp and Doc that a bound form is the approach that uses Access' functionality directly.
 
  • Like
Reactions: Kyp

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Feb 19, 2002
Messages
36,284
Access is a RAD (Rapid Application Development) tool and it does many things for you. The ONLY reason to use Access to develop an application is if you are going to let Access be Access and that means using bound forms. If you understand how form events work, you have complete control over whether or not a record gets added/updated.

If you don't want to use bound forms, you are eliminating the biggest helper of all (for no apparent reason) and rather than accepting the heavy overhead and limitations Access imposes, you should be using a different development platform. You can still use the desktop database engines Jet and ACE if you actually like them. They are available via ODBC from any platform that can use ODBC.
 

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
@Pat Hartman
I do understand what you are saying. (Don’t try to reinvent the wheel) Can you point me to any documentation or example that accomplish this same task?

cheers!
-Kyp
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:17
Joined
Feb 19, 2002
Messages
36,284
The others have offered solutions for how to locate a record. A simple solution is to create a bound form. Let the wizard do it for you. You can make it pretty later. Save the form. Then in the form's header, add an unbound combobox. The wizard will give you options. Choose the "find a record on this form option". The wizard will create an embedded macro. You can use a ribbon option to convert the macro to vba and you will see that it probably matches one of the suggestions.

Technically, you don't need a single line of code to make a bound form work. It is generated with a navigation bar so you can move from record to record. Records that you modify are automatically saved if you close the form or move to another record. Most people have trouble stopping the save, not making it happen. Access takes its job of protecting data quite seriously and will save whenever it needs to. Eventually, you will get a better understanding of how the event model works so you will know which actions you perform will automatically force a save.

However, once you move above the most basic level of development, you will realize that you do need to write code to validate the data so you can ensure that only valid and complete data gets saved. To accomplish that, you will need to understand the form's BeforeUpdate event. That event is the last event that runs before a record gets saved to think of it as the flapper at the end of a funnel. If the flapper is open, the record is saved. If the flapper is closed, the record is not saved. That is what your validation code in the BeforeUpdate event does. It closes the flapper by cancelling the update ---

Cancel = True

Go to the samples section. I've posted a few different samples that show how forms work. Create a test database and play in there until you understand how a bound form works. This one is most likely to be useful for you.

 
  • Like
Reactions: Kyp

Kyp

Member
Local time
Today, 17:17
Joined
Aug 5, 2021
Messages
68
@Pat Hartman
I am back at it this morning and will have a look.
Thanks for the template!

Cheers!
-Kyp
 

Users who are viewing this thread

Top Bottom