listbox

[mjs]

I have a form that has a listbox and the listbox value is from one of my table. What I want to do is get the id of the selected items and append it to one of the field on my table. But if multiple are selected on the listbox I want to assign the first id of the record on all of them.

For example if I select only one record/item, I only want to get the id of that record and append it to the code field of the record but if I selected 3 records/items I want to get the first selected record/item id and append it to the code field of the 3 records on the table.

Any help and suggestion is much appreciated.

Thank you in advanced.

 

[ezfriend]

You can check to see which item is selected. Then keep track of the first item and loop through the list. Also keep track of the rest that are selected.

At the end, just do a insert/update to change these records in the table.

lstName below link to a table tblName (ID, Name)

Private Sub Command2_Click()

    Dim i As Long
    
    For i = 0 To lstName.ListCount - 1
        
        If lstName.Selected(i) = True Then
            MsgBox lstName.Column(1, i)
        End If
    
    Next
    
End Sub

 

[mjs]

Hi Exfriend

Thanks for the reply.

I did try it and it works but the problem now I don't know how to update each selected record on my table. If you can show me a sample code on how to do it this will be very helpful for me. Sorry for asking too much as I'm new with access vba...


Thank you again

 

[jal]

One method is this:
DoCMd.RunSQL "UPDATE tblCustomers SET LastName = 'Smith' WHERE CustID = 55"

Another is a recordset:

Dim rs as New ADODB.Recordset
rs.Open "SELECT * FROM tblCustomers WHERE CustID = 55", currentProject.Connection, adOpenKeyset, adLockOptimistic

rs("LastName") = "Smith"
rs.Update
rs.Close
set rs = nothing.

If you want to add a record:

rs.AddNew
rs("LastName") = "Smith"
rs("FirstName") = "John"
rs.Update
rs.Close
set rs = nothing