Solved Need Assistance: How to update record from form listbox?

[Kyp]

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)

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:

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]

My first reaction is to adjust this line

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

 

[arnelgp]

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:

Private Sub lstbxCostCenter_DblClick(Cancel As Integer)
DoCmd.SearchForRecord , , acFirst, "ID = " & Me!lstbxCostCenter
End Sub

 

[Kyp]

@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:

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]

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.

 

[Kyp]

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.

 

[jdraw]

CostCenterID is numeric, right?

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

 

[Kyp]

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]

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.

Thanks for the assistance!

Cheers!
-Kyp

 

[Kyp]

@jdraw
The completed logic that properly works, appreciate your help!

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]

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.

 

[Kyp]

@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]

You are very welcome. I agree with arnelgp and Doc that a bound form is the approach that uses Access' functionality directly.

 

[Kyp]

@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

 

[Kyp]

@Pat Hartman
I am back at it this morning and will have a look.
Thanks for the template!

Cheers!
-Kyp

 

[Luis Fernandez]

Using a list box or combo box to choose a record and then edit it with texbox as fields is complex

A recorset allows you to position yourself in a record and modify the data, both in the form of a continuous list and in the form of fields.

Unless I don't understand the problem and I'm wrong.

 

[The_Doc_Man]

First, @Luis Fernandez , you are commenting on a year-old post that went quiet... probably meaning that the member found an answer and moved on.

Second, using a list-box to select a record to then be edited via a form is neither crazy nor silly. In fact, it is commonplace. Account managers do it all the time. Help Desks with recurring callers or continuing problems do it all the time. Doctors looking up your chart to annotate your blood pressure and heart rate do it... or their in-office assistants do it. Using a form is commonplace because humanity is a visual species.

I suggest you are having trouble understanding the OP's goal AND the fact that initially he didn't understand quite how to do it. Then we guided him to a better understanding. And that happens often around here. Please watch out for calling it silly when a person asks a question for which he did not originally know the answer. You are new here. We can be and often ARE quite rough on each other - but we don't start out that way and we try to avoid denigration of members.

 

[Luis Fernandez]

Corrected although I was not referring to the person but to the process, my apologies

 

[Luis Fernandez]

If you've never used SQL Server or other RDBMS, you won't automatically understand why using a search box to find the record you want to update is actually best practice. Access is very tightly bound to Jet and ACE and so if one of those is the BE database, Access works pretty efficiently. Once you move up in the world to one of the "big guys", you want to think about how to make the process more efficient and that means letting the RDBMS to the heavy lifting. You want Access to send a query to the server requesting the minimum number of rows/columns for any particular update. Best is just one single row for a bound form. That minimizes the traffic between Access and the server and reduces the load on the server.

If you've ever converted an old Access application to link to tables in SQL Server and didn't make any modifications to the app, you were probably flabbergasted to find out that the SQL Server version was much slower if the linked table contained more than a trivial number of rows. Why? Because when you bind a form/report to a table, you force Access to request ALL rows be downloaded from the server and brought into memory on your local PC. VERY inefficient in the relational database world. Just bring specifically what you need to update NOW. So, the very first and possibly only change you need to make is to change your bound forms to be bound to queries with WHERE clauses to minimize the network traffic. You may never need to do anything else to get acceptable speed.

I understand and you are absolutely right.
Although with the new equipment speeds, fiber optics, SSD and M.2 hard drives, I don't know if it's still necessary to bother.
My systems search floating in 33000 rows to choose from while typing a text and filter the live records within a combobox for the user to select from a small list, and all this while typing each letter and 5 people at a time on separate computers.
So I think we should take new times and updated concerns.
but of course you are very right, no joke

Combobox display of 32000 live records, floating search
The data is on a separate server and the hardware store has 5 points of sale.
It is with access 2003 but also works with access 2016 32 and 64 bits
Spanish version: Ventas = Sales

Here you can see purchases, choosing between 32,000 items and moving through each purchase invoice taking the descriptions of the products live from inventory on the server
Spanish version: Compras = Shopping