Multi Selct List Box Problems

Aric Green

I have a form in a database that I am trying to use a combo box to select an employee and then a listbox to select multiple card numbers to assign to the employee selected. When I push a button I want it to put the name of the employee in the table for all card numbers selected. Here is the following code that I am trying. I am not sure what is wrong. (Note: In the Immediate window "False" is what is displayed after I run the code and get the error
Run-time error '3078':
The Microsoft Jet database engine cannot find the input table or query 'False'. Make sure it exists and that its name is spelled correctly.

=======

Expand|Select|Wrap|Line Numbers

 
Private Sub ASSIGN_USER_TO_CARDS_Click()

    Dim db As Database

    Dim rec As DAO.Recordset

    Dim strWhere As String

    Dim varSelected As Variant

    Dim strSQL As String

        Set db = CurrentDb()

        Set rec = db.OpenRecordset("tblCardstockAccountability")

        If Me!List9.ItemsSelected.Count > 0 Then

            For Each varSelected In Me!List9.ItemsSelected

                strWhere = strWhere & """" & Me!List9.ItemData(varSelected) & """, "

            Next varSelected

    strSQL = ""

    strSQL = strSQL & "UPDATE tblCardstockAccountability " & _

    strSQL = strSQL & "SET strClerkCardIssuedTo = """ & Me.cmb11 & """"

    strSQL = strSQL & " WHERE strClerkCardIssuedTo IN (" & strSQL = strSQL & Left$(strWhere, Len(strWhere) - 2) & ")"

    Debug.Print strSQL

    CurrentDb.Execute strSQL

    Set db = Nothing   ' Clear db...

    MsgBox ("This data is now in your table..")

    End If

    End Sub

=======

FishVal

Hi, Aric.

Could you post a value of strSQL your code prints to immediate window?

Regards,
Fish

Aric Green

Fish,
The value that is in the immediate window is "False". Not sure why.

Thanks,
Aric

jaxjagfan

Expand|Select|Wrap|Line Numbers

 
Dim ctl As Control, varSelected As Variant, strEmp as String, strWhere as String, strSQL as String

strEmp = Me.cmb11

Set ctl = Me!List9

strSQL = "Update tblCardstockAccountability SET strClerkCardIssuedTo ='" &  strEmp & "'

For Each varSelected In ctl.ItemsSelected

   strWhere = " Where YourCardNumberField = " & "'" & ctl.ItemData(varSelected) & "'"

   Docmd.RunSQL strSQL & strWhere

Next varSelected

From your writeup you are trying to assign Employee to multiple card numbers. For each card number selected in listbox update tblCardstockAccountability with the employee selected in the combobox where the card number selected equals card number in the table. Your Where clause is looking at employee field.
I think this will work for you. You will need to modify slightly. Need the field name for the card numbers also.

Aric Green

Thanks jaxjagfan that worked like a charm. I also need to update a date issued to employee field with the current date. I have tryed a couple things and am stuck on this as well because I am fairly new to coding. Here is what I have.
=======
Dim ctl As Control, varselected As Variant, strEmp As String, strWhere As String, strSql As String, strSql2 As String
strEmp = Me.cmb11
Set ctl = Me!List9
strSql = "Update tblCardstockAccountability SET strClerkCardIssuedTo ='" & strEmp & "'"
For Each varselected In ctl.ItemsSelected
strSql2 = "Update tblCardstockAccountability SET [DATE ISSUED TO CLERK] = '" & Date & "'"
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(varselected) & "'"
DoCmd.RunSQL strSql & strSql2 & strWhere
Next varselected
=======
Thanks,
Aric

jaxjagfan

Quote:

Originally Posted by Aric Green

Thanks jaxjagfan that worked like a charm. I also need to update a date issued to employee field with the current date. I have tryed a couple things and am stuck on this as well because I am fairly new to coding. Here is what I have.
=======
Dim ctl As Control, varselected As Variant, strEmp As String, strWhere As String, strSql As String, strSql2 As String
strEmp = Me.cmb11
Set ctl = Me!List9
strSql = "Update tblCardstockAccountability SET strClerkCardIssuedTo ='" & strEmp & "'"
For Each varselected In ctl.ItemsSelected
strSql2 = "Update tblCardstockAccountability SET [DATE ISSUED TO CLERK] = '" & Date & "'"
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(varselected) & "'"
DoCmd.RunSQL strSql & strSql2 & strWhere
Next varselected
=======
Thanks,
Aric

strSql = "Update tblCardstockAccountability SET strClerkCardIssuedTo ='" & strEmp & "', [DATE ISSUED TO CLERK]=#" & Date() & "#"
For Each varselected In ctl.ItemsSelected
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(varselected) & "'"
DoCmd.RunSQL strSql & strWhere
Next varselected

It should look similar to the above. Get rid of strSQL2. What you are basically trying to tell it to do is - UPDATE tablename SET Column1 = Value1, Column2 = Value2 Where Column3 = Value3. You may have to change the "#" to a "'" depending on field types in your table.

FishVal

Pay attention to this code line.

Expand|Select|Wrap|Line Numbers

 
strSQL = strSQL & " WHERE strClerkCardIssuedTo IN (" & strSQL = strSQL & Left$(strWhere, Len(strWhere) - 2) & ")"

You set value of strSQL variable to the result of boolean operation (=).
No wonder it appears as False.

Regards,
Fish

Aric Green

Works great. I had been trying to figure out how to do this for a couple of days now. I wish I would have came here for help first.
Thanks,
Aric

Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

re: Multi Selct List Box Problems

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about