Access VBA - edit line of a multicolumn Listbox

Hi. I add a line to a four column ListBox in Access using the following code
   oRowItem = s1 & ";" & s2 & ";" & s3 & ";" & s4  
    Me.lsbTasks.AddItem oRowItem
How would I edit the selected line in the listbox?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't edit anything directly in the listbox.

You could edit that line through VBA if you can locate it, however, you're probably better off just refilling the box with every record, with your edits in place.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. What VBA code would I use
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
If the user selects a line, surely I can use VBA code to edit that line
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

datAdrenalineCommented:
>> If the user selects a line, surely I can use VBA code to edit that line <<

Not really ... you delete the row and add the row back ...

When the user selects a line in the list box, you would simply read the .ListIndex property then delete that line (item), create the item again, then add the item back.

Dim i As Long
i = listbox.ListIndex 'get the index of the item selected.
Me.lsbTasks.RemoveItem i
<code to set up the replacement line>
oRowItem = s1 & ";" & s2 & ";" & s3 & ";" & s4  
Me.lsbTasks.AddItem oRowItem, i

---------------------------

Do take note that .ListIndex is a 0 based number with 0 being the first item in the list ... UNLESS ... you have column headers turned on.  The column headers row actually consumes an item index value, and thus is ListIndex 0, if your have that option set.

Another method that may be of use to you is the .Column method ..

listbox.Column(ColumnIndex, RowIndex)

ColumnIndex is a number between 0 and the column count -1; RowIndex is the same as ListIndex above.
--------------------------------------------

As a side note, if you set the rowsource of your list box to a SQL statement, then you would modify the data, then .Requery the listbox or set the listboxes RowSource to itself (listbox.RowSource = listbox.RowSource).

Hope that helps!
0
datAdrenalineCommented:
Oh ... the .Column() method is used to return the value of a specific column/row.  Note that .Column will return a String, even if a number is shown in the ListBox.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.