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.

 

[Pat Hartman]

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]

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

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.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[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