I realize this is an issue that has been touched on a number of times. Unfortunately, I have no been able to secure a working result, and I am desperate to find a working solution. I am running Access 2007.
The end result of the project is to create a summarized report of data types from a linked SQL database.
I have the following elements:
After examing a ton of example code, including this site, I've utilized the following for the GetCriteria() function, placed in a module:
The function works as intended when I only select one project. However, when I select multiple items, I get NONE of the results from the database.
If anyone has any suggestions, I would very, very much appreciate the assistance.
The end result of the project is to create a summarized report of data types from a linked SQL database.
I have the following elements:
- Three linked SQL databases:
- ClientDB
- ProjectDB
- ItemsDB
- A form ("Job Selection Form") containing:
- One combo-box (single select) that lists clients from ClientDB. A change in the chosen client results in a load of ProjectsDB for the client, read from the ProjectDB database into:
- A simple multi-select listbox with two fields (ProjectID,ProjectName) named JobList
- The form works as intended.
- A SQL query that is intended to utilize the selected ProjectID fields, and pull only the appropriate summary data from the SQL database. (broken)
Code:
SELECT Extension, Count(ItemID) AS FileCount, Sum(PageCount) AS Pages, Sum(ItemFileSize) AS SizeKB
FROM ItemsDB
WHERE ProjectID IN(GetCriteria())
GROUP BY Extension
ORDER BY Extension;
Code:
Public Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Forms![Job Selection Form]!JobList.ItemsSelected
stDocCriteria = stDocCriteria & Forms![Job Selection Form]!JobList.Column(0, VarItm) & ","
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 1)
Else
stDocCriteria = "True"
End If
MsgBox "GetCriteria: " & stDocCriteria
GetCriteria = stDocCriteria
End Function
If anyone has any suggestions, I would very, very much appreciate the assistance.
Last edited: