Update a Record with a matching Listbox value.

[akeller]

I have a form with a Listbox , Textbox, button, and also a checkbox on record.


The Listbox has to columns populated by a query with the first column width set to 0". This column holds FK values for the 2nd column in the Listbox and will be used to identify which records to update.

I would like to select multiple values in the listbox, add a comment into the textbox, and then click the button to update the selected records by adding the comment and selecting the checkbox on the record.

Here are the names of everything.

Form = Form1 (Only Temp name)
Listbox = lstWorkOrder
Textbox = txtComments
Button = btnPlaceHold
Table = tblIncomingJobJoin
Checkbox = Hold
Comments = Comments

Any idea how to accomplish this? I have read and tried numerous scripting solutions, including some from pbaldy, but none have worked for my application or I just cant figure them out.

Thanks in advance.

[pbaldy]

What did you try? I'd expect to adapt this:

http://www.baldyweb.com/MultiselectAppend.htm

but instead of the recordset, create and execute SQL within the loop:

strSQL = "UPDATE..."
CurrentDb.Execute strSQL, dbFailOnError

[Gicu]

Check out the attached. You need to loop through the ItemsSelected collection.

Cheers,
Vlad

[kd2017]

Here is an example:

Option Compare Database
Option Explicit

Private Sub btnPlaceHold_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    Dim fkeys As String
    
    fkeys = SQL_Criteria(Me.lstWorkOrder)
    If fkeys = "Like '*' " Then
        MsgBox "The listbox has no selections"
        
    ElseIf Me.txtComments = "" Then
        MsgBox "The comment box is empty"
        
    Else
    
        qry = "UPDATE tblIncomingJobJoin SET Hold=True, Comments=""" & Me.txtComments & """  WHERE ID " & fkeys & ";"
        
        Set db = CurrentDb
        db.Execute qry
        'Debug.Print qry
        
        Clear_MultiSelect Me.lstWorkOrder
        Me.txtComments = ""
        MsgBox "Done!"
    End If
    
ExitHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume ExitHandler
    
End Sub

Function SQL_Criteria(lstbox As Control) As String
'Build Where Condition for SQL Statement (Bound Column - Numerica data type)
'http://www.myaccesstips.com/functionality/multiselect.html
    
    Dim varItm As Variant
    Dim ctl As Control
    Dim strCriteria As String

    Set ctl = lstbox
    
    For Each varItm In ctl.ItemsSelected
        'Use the ItemData Property to select the Bound Column
        'Use the Column Property to specify the Row, Column
        strCriteria = strCriteria + ctl.ItemData(varItm) & ","
    Next varItm
    If strCriteria = "" Then
        SQL_Criteria = "Like '*' "
    Else
        SQL_Criteria = "IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")"
        'Rremove last comma
    End If

End Function

Sub Clear_MultiSelect(lstbox As Control)
'Clears all values Selected in a listbox
'http://www.myaccesstips.com/functionality/multiselect.html
    Dim varItm As Variant
    Dim ctl As Control

    Set ctl = lstbox
    
    For Each varItm In ctl.ItemsSelected
        ctl.Selected(varItm) = False
    Next varItm

End Sub

http://www.myaccesstips.com/function...ltiselect.html

[akeller]

Check out the attached. You need to loop through the ItemsSelected collection.

Cheers,
Vlad

Awesome, thank you so much Vlad!
That really helps me out, I appreciate it.

[akeller]

Here is an example:

Option Compare Database
Option Explicit

Private Sub btnPlaceHold_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    Dim fkeys As String
    
    fkeys = SQL_Criteria(Me.lstWorkOrder)
    If fkeys = "Like '*' " Then
        MsgBox "The listbox has no selections"
        
    ElseIf Me.txtComments = "" Then
        MsgBox "The comment box is empty"
        
    Else
    
        qry = "UPDATE tblIncomingJobJoin SET Hold=True, Comments=""" & Me.txtComments & """  WHERE ID " & fkeys & ";"
        
        Set db = CurrentDb
        db.Execute qry
        'Debug.Print qry
        
        Clear_MultiSelect Me.lstWorkOrder
        Me.txtComments = ""
        MsgBox "Done!"
    End If
    
ExitHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume ExitHandler
    
End Sub

Function SQL_Criteria(lstbox As Control) As String
'Build Where Condition for SQL Statement (Bound Column - Numerica data type)
'http://www.myaccesstips.com/functionality/multiselect.html
    
    Dim varItm As Variant
    Dim ctl As Control
    Dim strCriteria As String

    Set ctl = lstbox
    
    For Each varItm In ctl.ItemsSelected
        'Use the ItemData Property to select the Bound Column
        'Use the Column Property to specify the Row, Column
        strCriteria = strCriteria + ctl.ItemData(varItm) & ","
    Next varItm
    If strCriteria = "" Then
        SQL_Criteria = "Like '*' "
    Else
        SQL_Criteria = "IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")"
        'Rremove last comma
    End If

End Function

Sub Clear_MultiSelect(lstbox As Control)
'Clears all values Selected in a listbox
'http://www.myaccesstips.com/functionality/multiselect.html
    Dim varItm As Variant
    Dim ctl As Control

    Set ctl = lstbox
    
    For Each varItm In ctl.ItemsSelected
        ctl.Selected(varItm) = False
    Next varItm

End Sub

http://www.myaccesstips.com/function...ltiselect.html

kd2017,

It's giving me a VBA Error 3061 Too few parameters. Expected 1.

[akeller]

[QUOTE=pbaldy;417848]What did you try? I'd expect to adapt this:

http://www.baldyweb.com/MultiselectAppend.htm

but instead of the recordset, create and execute SQL within the loop:


I followed another post on this forum where you directed the OP to this link http://www.baldyweb.com/multiselect.htm
I kept trying to tweak the code to work for me when I should have just dug further into the link and I would have noticed the other link you shared with me.

Thanks for your help!

[kd2017]

https://stackoverflow.com/questions/...-1-access-2007

"Run-time error '3061'. Too few parameters. Expected 1."
I believe this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.

In the code edit this line to make sure it correctly corresponds to your table, field and form names

qry = "UPDATE tblIncomingJobJoin SET Hold=True, Comments=""" & Me.txtComments & """  WHERE ID " & fkeys & ";"

(Probably the ID field name)

[pbaldy]

Did you change ID to the name of the primary key field in your table? The error implies something is spelled wrong. This may help too:

http://www.baldyweb.com/ImmediateWindow.htm

[akeller]

https://stackoverflow.com/questions/...-1-access-2007



In the code edit this line to make sure it correctly corresponds to your table, field and form names

qry = "UPDATE tblIncomingJobJoin SET Hold=True, Comments=""" & Me.txtComments & """  WHERE ID " & fkeys & ";"

(Probably the ID field name)

Dang, I bet that's it. I was almost certain that everything was correct.
I've left work for the day but will try the fix first thing tomorrow morning.

Thanks Again!

[akeller]

Did you change ID to the name of the primary key field in your table? The error implies something is spelled wrong. This may help too:

http://www.baldyweb.com/ImmediateWindow.htm

I believe you are right. I've left work for the day and will check everything tomorrow.

Thanks to you as well for the help and the links. Very informative for a newbie like me!

[Gicu]

Have you had a chance to try my example, I used the object names you provided so it should have run with no errors?

Cheers,
Vlad

[akeller]

Have you had a chance to try my example, I used the object names you provided so it should have run with no errors?

Cheers,
Vlad

I did and it works great!!! Thank you so much!

[akeller]

Have you had a chance to try my example, I used the object names you provided so it should have run with no errors?

Cheers,
Vlad

Vlad,

I started thinking and decided that it might be better to have two listboxes, one to make selections, and the other to display the selected items. I suppose that I would also need a few buttons for select, select all, deselect, and deselect all. Then of course the main button to complete the update.

Can this be done by editing the existing code?
This DB will be split for multi users if that matters.

[Gicu]

Yes, of course, the code that I gave you to do the updates would not change much - you would simply need to reference the new listbox holding the selected items and changing from looping through the ItemsSelected to looping through all items (https://stackoverflow.com/questions/...ugh-a-list-box) because do not reselect them in the listbox once you move them there from "available". Here is a link you might want to have a look at: https://office-watch.com/2010/adding...-items-part-2/ (the topic came up yesterday in this forum:https://www.accessforums.net/showthr...826#post417826).
You may also want to consider the way you do the update itself; right now you are overwriting the entire content of the comments field for the selected records with the "new" comment. As I do not know the specifics of your db that might be OK. I usually design dbs to have the "comments" (or "notes") in another table in which I store the main record ID as a foreign key, the comment, timestamp (using Now()) and the user that added the comments. In this way I have a nice historical "comments" table.

Cheers,
Vlad