Help - Updating table entries selected in Listbox

[DiverGuy]

I'm trying to use the value selected in a combobox to update the selected
items in a listbox. Running into difficulty and all of my local resources
were unable to help. Here's what we're working with:

A table called tblFileList.
A table called tblPerformers
A query called qryFileList that pulls all of the data from tblFileList.
A form with
a multi-select listbox called listboxFileList
a combobox called cboPerformer
and a button called cmdUpdate.

The listbox is populated from the query. The combobox is populated from
tblPerformers.

The goal is to be able to select, say, ten files from the list displayed in
the listbox, select a name from the combobox, click the Update button, and
the Performer field in tblFileList will be updated for those ten files.

I have started an UPDATE statement in the click event for the cmdUpdate
button:
Dim i as variant
For each i in Me.listboxFileList.ItemsSelected
'Nothing is currently in the For..Each statement
Next

Dim strSQL as String
strSQL = "UPDATE tblFileList SET tblFileList.Performer = ' " &
Me.cboPerformer.Value & " ' WHERE XXXXXXXX

DoCmd.RunSQL strSQL


I'm stumbling on the part above indicated by XXXXXXX. Can anyone tell me
how to reference or specify only the files selected in the listbox and update
them in the table?

Any help will be appreciated.

Thanks.

 

[NetworkTrade]

consider making a trial using the generic query design method to make an
update query to that file....once this trial works correctly then in design
mode select the SQL view; this will show you the correct sql syntax; you can
re-use this sql code syntax to implement your requirement.....

 

[Beetle]

I have a form in one of my db's that does basically the same thing. Here is
the code I use. I modified naming as much as possible based on your post, but
you may need to modify it further.

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSQL As String
Dim con As ADODB.Connection
Set con = CurrentProject.Connection

If IsNull(Me.cboPerformer) Or Me.cboPerformer = 0 Then

MsgBox "Please make a selection in the 'Performer' list",_
vbOKOnly, "Select a Performer"

Exit Sub
Me.cboPerformer.SetFocus

End If

For Each varItem In Me.listboxFileList.ItemsSelected

strSQL = "UPDATE tblFileList SET PerformerID = " & Me.cboPerformer _
& " WHERE [ID] = " & Me.listboxFileList.Column(0, varItem)

con.Execute strSQL

Next varItem

con.Close
Set con = Nothing

End Sub

You'll need to replace the [ID] in the where clause with the appropriate
field from tblFileList, and you may need to change the column number
depending on which column of your list box contains the value you are trying
to reference.

You should also add your own error handling.

HTH

 

[DiverGuy]

That did the trick! It only took very minor modifications. The WHERE clause
was what was needed. Thanks for your help.

Diverguy

I have a form in one of my db's that does basically the same thing. Here is
the code I use. I modified naming as much as possible based on your post, but
you may need to modify it further.

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSQL As String
Dim con As ADODB.Connection
Set con = CurrentProject.Connection

If IsNull(Me.cboPerformer) Or Me.cboPerformer = 0 Then

MsgBox "Please make a selection in the 'Performer' list",_
vbOKOnly, "Select a Performer"

Exit Sub
Me.cboPerformer.SetFocus

End If

For Each varItem In Me.listboxFileList.ItemsSelected

strSQL = "UPDATE tblFileList SET PerformerID = " & Me.cboPerformer _
& " WHERE [ID] = " & Me.listboxFileList.Column(0, varItem)

con.Execute strSQL

Next varItem

con.Close
Set con = Nothing

End Sub

You'll need to replace the [ID] in the where clause with the appropriate
field from tblFileList, and you may need to change the column number
depending on which column of your list box contains the value you are trying
to reference.

You should also add your own error handling.

HTH

--
_________

Sean Bailey

I'm trying to use the value selected in a combobox to update the selected
items in a listbox. Running into difficulty and all of my local resources
were unable to help. Here's what we're working with:

A table called tblFileList.
A table called tblPerformers
A query called qryFileList that pulls all of the data from tblFileList.
A form with
a multi-select listbox called listboxFileList
a combobox called cboPerformer
and a button called cmdUpdate.

The listbox is populated from the query. The combobox is populated from
tblPerformers.

The goal is to be able to select, say, ten files from the list displayed in
the listbox, select a name from the combobox, click the Update button, and
the Performer field in tblFileList will be updated for those ten files.

I have started an UPDATE statement in the click event for the cmdUpdate
button:
Dim i as variant
For each i in Me.listboxFileList.ItemsSelected
'Nothing is currently in the For..Each statement
Next

Dim strSQL as String
strSQL = "UPDATE tblFileList SET tblFileList.Performer = ' " &
Me.cboPerformer.Value & " ' WHERE XXXXXXXX

DoCmd.RunSQL strSQL


I'm stumbling on the part above indicated by XXXXXXX. Can anyone tell me
how to reference or specify only the files selected in the listbox and update
them in the table?

Any help will be appreciated.

Thanks.