title: Access - Update Query from Form Values with Multiselect Listbox VBA
body: <p>I have an issue that I feel I'm close to solving, but am missing something. I have an unbound form with five objects.</p>
  
  <p>'SSN' is a standard text box.
  'Appointment State' is a listbox with multiselect enabled. The source is a table named States.
  'Carrier' is a combobox.
  'Appointment Status' is a combobox.
  'Appt Request Date' is a date field.</p>
  
  <p>Once values are in all five objects, I have a button that runs the code below. I want to pass the values to an update query to be used as criteria. </p>
  
  <p>The fields that are being updated are 'Appointment Status' and 'Appt Request Date'. The other fields are to select the correct record(s).</p>
  
  <p>Currently the code runs, and the update query receives most of the criteria. The only field that does not populate correctly is the 'Appointment State', which I thought would pull from strCriteria. </p>
  
  <p>When the code runs, it asks the user to populate the strCriteria value when it needs to pull from the 'Appointment States' listbox.</p>
  
  <p>I found the code from a site that told how to grab the values from a multiselect listbox and then creates the SQL for a one criteria Select query. I've tried to adapt it as best as I can. The main difference is the SQL build. When I use the original, it works.</p>
  
  <p>My modified code to operate the update query:</p>
  
  <pre><code>Private Sub Command35_Click()
  
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim varItem As Variant
  Dim strCriteria As String
  Dim strSQL As String
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("Appointment Update Query")
  
  If Me![Appointment State].ItemsSelected.Count &gt; 0 Then
    For Each varItem In Me![Appointment State].ItemsSelected
       strCriteria = strCriteria &amp; "States.States = " &amp; Chr(34) _
                     &amp; Me![Appointment State].ItemData(varItem) &amp; Chr(34) &amp;  " OR "
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
  Else
    strCriteria = "States.States Like '*'"
  End If
  strSQL = "UPDATE [Appointment Query]" _
  &amp; " SET [Appointment Query].[Appointment Status] = [Forms].[Appointment Update].[Appointment Status], [Appointment Query].[Appt Request Date] = [Forms]![Appointment Update].[Appt Request Date]" _
  &amp; " WHERE ((([Appointment Query].SSN)=[Forms]![Appointment Update].[SSN]) AND (([Appointment Query].[Appointment State])=" &amp; strCriteria &amp; ") AND (([Appointment Query].Carrier)=[Forms]![Appointment Update].[Carrier]));"
  
  qdf.SQL = strSQL
  qdf.Close  
  
  DoCmd.OpenQuery "Appointment Update Query"
  
  Set db = Nothing
  Set qdf = Nothing
  
  End Sub
  </code></pre>
  
  <p>The original code:</p>
  
  <pre><code>    Private Sub cmdOK_Click()
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim varItem As Variant
  Dim strCriteria As String
  Dim strSQL As String
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("qryMultiSelect")
  If Me!lstRegions.ItemsSelected.Count &gt; 0 Then
    For Each varItem In Me!lstRegions.ItemsSelected
       strCriteria = strCriteria &amp; "tblData.Region = " &amp; Chr(34) _
                     &amp; Me!lstRegions.ItemData(varItem) &amp; Chr(34) &amp; "OR "
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
  Else
    strCriteria = "tblData.Region Like '*'"
  End If
  strSQL = "SELECT * FROM tblData " &amp; _
          "WHERE " &amp; strCriteria &amp; ";"
  qdf.SQL = strSQL
  DoCmd.OpenQuery "qryMultiSelect"
  Set db = Nothing
  Set qdf = Nothing
  End Sub
  </code></pre>
  
  <p>Any ideas on how I could get this to work properly?</p>
  
  <p>Debug.Print strSQL:</p>
  
  <pre><code>    UPDATE [Appointment Query] SET [Appointment Query].[Appointment Status] = [Forms].[Appointment Update].[Appointment Status], [Appointment Query].[Appt Request Date] = [Forms]![Appointment Update].[Appt Request Date] WHERE ((([Appointment Query].SSN)=[Forms]![Appointment Update].[SSN]) AND (([Appointment Query].[Appointment State])=States.States = "AL" OR States.States = "AZ") AND (([Appointment Query].Carrier)=[Forms]![Appointment Update].[Carrier]));
  </code></pre>
  
  <p>Debug.Print strCriteria:</p>
  
  <pre><code>    States.States = "AL" OR States.States = "AZ"
  </code></pre>
  
  <p>States is the name of the table the state abbreviations are in, and the second States is the field name.</p>