I have an issue that I feel I'm close to solving, but am missing something. I have an unbound form with five objects.
'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.
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.
The fields that are being updated are 'Appointment Status' and 'Appt Request Date'. The other fields are to select the correct record(s).
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.
When the code runs, it asks the user to populate the strCriteria value when it needs to pull from the 'Appointment States' listbox.
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.
My modified code to operate the update query:
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 > 0 Then
For Each varItem In Me![Appointment State].ItemsSelected
strCriteria = strCriteria & "States.States = " & Chr(34) _
& Me![Appointment State].ItemData(varItem) & Chr(34) & " OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "States.States Like '*'"
End If
strSQL = "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])=" & strCriteria & ") 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
The original 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 > 0 Then
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
& Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tblData.Region Like '*'"
End If
strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub
Any ideas on how I could get this to work properly?
Debug.Print strSQL:
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]));
Debug.Print strCriteria:
States.States = "AL" OR States.States = "AZ"
States is the name of the table the state abbreviations are in, and the second States is the field name.
