Editing listbox item

Question

So i have a listbox that displays all the orders entered in the Order table using a select sql query. Now i also want to add the ability to edit the items from the listbox, i see the right click edit list item option but when i click it, it just opens the form but doesnt populate the fields. The form has textboxes that are unbound but i cant figure out how to get them to populate based on the right clicked entry. I have also tried to open the target form from vba and fill the fields through vba with the following code

Private Sub editrecordbttn_Click()
Dim valSelect As Variant
Dim v As Variant
Dim selector As Variant
Dim strValue As String ' just used for the demonstration
Dim splitvalue() As String
Dim selectedsampid As String
Dim selectedcusid As String
Dim Records As DAO.Recordset
Dim SQLcus As String
Dim SQLsamp As String
 
For Each valSelect In Me.searchlistbox.ItemsSelected
    strValue = strValue & "" & Me.searchlistbox.ItemData(valSelect) & "," & "" & Me.searchlistbox.Column(1, valSelect) & ","
Next valSelect
 
    ' to remove trailing comma
strValue = Left(strValue, Len(strValue) - 1)

splitvalue() = Split(strValue, ",")
selectedsampid = splitvalue(0)
selectedcusid = splitvalue(1)

DoCmd.OpenForm ("Add Sample")
Forms![Add Sample].fnametxt.SetFocus


'query and fill cus info
SQLcus = "SELECT * FROM CustomerInfo WHERE CusID = '" & selectedcusid & "';"
Set Records = CurrentDb.OpenRecordset(SQLcus)
Me!clienttypetxt = Records![Client type].Value

End Sub

Why use unbound form? Why not a bound form and apply filter criteria with the WHERE CONDITION argument of DoCmd.OpenForm? — June7, Nov 01 '21 at 04:24
@June7 well the form im using to add orders has unbound controls bc it needs to be separating form data into multiple tables and i wanted to reuse the same form but if it works on a bound form i would try that, i just dont know how to set the recordset for the new form thats going to open — Collette jorike, Nov 01 '21 at 04:31
Why is one form an input for multiple tables? The SQL you show references only one table so how are multiple tables involved? Edit question to show sample data structure. I have never used unbound forms for data entry/edit. Code makes no sense to me. Why build a CSV string and then split it? — June7, Nov 01 '21 at 07:04

Albert D. Kallal · Accepted Answer · 2021-11-01T17:14:50.183

Ok, so say we have a listbox, and we do this:

The first column of the listbox is assumed to be the PK or "ID" of the rows.

so, we have this:

And thus you select a row, and then click on the button.

The button code would look like this:

Private Sub cmdEdit_Click()

  Debug.Print "Hotel list id selected = " & Me.HotelList
  
  DoCmd.OpenForm "frmEditHotels", , , "ID = " & Me.HotelList
  
  
End Sub

So, in most cases, for a better user experience, it probably better to approach things as per above.

There is of course the case in which you fill the listbox (or combo) with a "list" of values NOT from the database. In that case, you can use the "edit" list option. And this allows you to specify a form (or use the built in editor).

so, if this is NOT a list that you type in, and is from the database, then don't try to use the built in "list editing"

(add a button like above, and launch the form with the "where" clause to load the form to the ONE data record as I did above.

Since oh so very often, a listbox data will come from a table, then the edit list options are not really much particular use. And using a table (as opposed to a list) to fill + drive the combo/listbox is a much better design, and idea anyway. This is especially the case if you ever want multiple-users, since the "list" edit feature would mean and suggest that each user editing the list would now have their own lists as opposed to using a table which everyone can edit.

Also, there is NO reason to use a loop to fill that list box. We can do this:

' setup critera for listbox.

Dim strSQL     As String

' prompt user for Hotel city - we just hard code for this exmaple.

Dim strCity    As String

strCity = "Banff"

strSQL = "SELECT ID,FirstName, LastName, City,HotelName FROM tblHotels " & _
        "WHERE City = '" & strCity & "' " & "ORDER BY HotelName"
        
Me.HotelList.RowSource = strSQL

Note how we do not have some MESSAY value list, but can shove the data (sql) right to the listbox. Not only do we don't have loops, but we also don't have to worry about the size limits.

With "value list" (those messy delimited ";" list), then you have a rather small limit of 4,000 characters. Don't take many larger rows to "blow up" the listbox, since it can't handel very many rows.

In fact, I often still suggest you use the wizard to build the listbox, and you can choose a datasource (sql), or the MUCH lessor choice of "value list".

Value list is only a good choice if you have say a few choices like Mr., Mrs. or what not, and it not some large table, but only say 5-10 choices.

Anything larger? Use a data table driven listbox, and avoid use of value list.

Editing listbox item

1 Answers1