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.

=======
[code=vb]
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.OpenRecordse t("tblCardstock Accountability" )
If Me!List9.ItemsS elected.Count > 0 Then
For Each varSelected In Me!List9.ItemsS elected
strWhere = strWhere & """" & Me!List9.ItemDa ta(varSelected) & """, "
Next varSelected
strSQL = ""
strSQL = strSQL & "UPDATE tblCardstockAcc ountability " & _
strSQL = strSQL & "SET strClerkCardIss uedTo = """ & Me.cmb11 & """"
strSQL = strSQL & " WHERE strClerkCardIss uedTo IN (" & strSQL = strSQL & Left$(strWhere, Len(strWhere) - 2) & ")"
Debug.Print strSQL
CurrentDb.Execu te strSQL
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")
End If
End Sub
[/code]
=======

[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]

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 tblCardstockAcc ountability 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 tblCardstockAcc ountability SET strClerkCardIss uedTo ='" & strEmp & "'"
For Each varselected In ctl.ItemsSelect ed
strSql2 = "Update tblCardstockAcc ountability SET [DATE ISSUED TO CLERK] = '" & Date & "'"
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(va rselected) & "'"
DoCmd.RunSQL strSql & strSql2 & strWhere
Next varselected
=======
Thanks,
Aric

[jaxjagfan]

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 tblCardstockAcc ountability SET strClerkCardIss uedTo ='" & strEmp & "'"
For Each varselected In ctl.ItemsSelect ed
strSql2 = "Update tblCardstockAcc ountability SET [DATE ISSUED TO CLERK] = '" & Date & "'"
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(va rselected) & "'"
DoCmd.RunSQL strSql & strSql2 & strWhere
Next varselected
=======
Thanks,
Aric

strSql = "Update tblCardstockAcc ountability SET strClerkCardIss uedTo ='" & strEmp & "', [DATE ISSUED TO CLERK]=#" & Date() & "#"
For Each varselected In ctl.ItemsSelect ed
strWhere = " Where [CARD NUMBER] = " & "'" & ctl.ItemData(va rselected) & "'"
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.
[code=vb]
strSQL = strSQL & " WHERE strClerkCardIss uedTo IN (" & strSQL = strSQL & Left$(strWhere, Len(strWhere) - 2) & ")"
[/code]

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