Append ONE Record

[aerofuego]

I have search this forum but I have not found a solution to this problem.

Ok, I have a form with a combobox and a listbox (multiselect). I want to append the combobox's value in a table based on the records I select in the listbox. So, If I select 3 records in the listbox and then I select a name from the combobox, I want the combobox value to be appended to the table for only these 3 records.

If anyone can show me how I can accomplish this or point me in the right direction, I would appreciate it.

 

[Sergeant]

Something for you to chew on...

Hello AeroFuego, hope this helps you...

Assumptions:
-You are not Appending, but Updating a field in an existing record. (right?)
-ListBox has PK (Number) as first column.
-ComboBox has Field (text) as first column. If it's a number that you want to write into the table field (like a foreign key to a value in another table), then take out the single quotes from the code.
-Change [xxx] to appropriate names.

Private Sub Command2_Click()
    'Make first field of listbox PK (Number) Field, zero width
    'First column of combobox is string (text) value field
    Dim strIn As String
    Dim strSQL As String
    For i = 0 To Me.[ListBoxName].ListCount - 1
        If Me.[ListBoxName].Selected(i) Then
            strIn = strIn & Me.[ListBoxName].ItemData(i) & ","
        End If
    Next i
    strIn = Left(strIn, Len(strIn) - 1)
    strSQL = "UPDATE [Table] SET [Field] = '" & Me.[NameOfComboBox] & _
                "' WHERE [PK NumberField] IN(" & strIn & ")"
    
    With DoCmd
        .SetWarnings False
        .RunSQL strSQL
        .SetWarnings True
    End With
End Sub

Disclaimer: There is no error control built in to account for "nothing is selected in list box".

 

[aerofuego]

I really appreciate it

Seargent,

I just want to let you know that it worked like a charm. I really want to thank you for your help.

The only thing I needed to add to your code was declaring the i variable and make the changes that you suggested.

I was trying to find a solution to this problem for quite a while.

Thanks a lot.