I have a few combo, and one multiselect list box for States delimiting. I am trying to feed a query based on this info. I have it working as a combo box, but now I am getting "type mismatch" and "object variable or with block variable not set" errors.
Help would be much appreciated in the "listbox" commented section and the stSQL definition.
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Dim db As DAO.Database
- Dim qdf As DAO.QueryDef
- Dim rs As DAO.Recordset
- Dim prm As DAO.Parameter
- Private Sub Run_Click()
- On Error GoTo Err_Run_Click
- Dim SpreadSheetSource As String
- Dim SpreadSheetOutput As String
- Dim stTerritory As String
- Dim stState As String
- Dim stStatePrint As String
- Dim stBrand As String
- Dim dFirstMonth As Date
- Dim stDocName As String
- Dim Suc As Boolean
- Dim varItem As Variant
- Dim listItems As Control
- Dim stSQL As String
- 'Data definitions
- 'SpreadSheetSource = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow.xls"
- 'SpreadSheetOutput = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow_new.xls"
- SpreadSheetSource = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow.xls"
- SpreadSheetOutput = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow_new.xls"
- stDocName = "output"
- dFirstMonth = Me.firstmonth
- 'list box
- Set listItems = Me.State
- For Each varItem In listItems.ItemsSelected
- If stState > "" Then
- stState = stState & " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
- stStatePrint = stStatePrint & ", " & listItems.ItemData(varItem)
- Else
- stState = " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
- stStatePrint = listItems.ItemData(varItem)
- End If
- MsgBox (stSate)
- Next varItem
- stSQL = "SELECT [zz Profit and Loss Flow Final].[Year-Month], Sum([zz Profit and Loss Flow Final].[Cases Shipped]) AS [Cases Shipped], Sum([zz Profit and Loss Flow Final].[Cases Depl]) AS [Cases Depl], Sum([zz Profit and Loss Flow Final].[Cases Depl Budget]) AS [Cases Depl Budget], Sum([zz Profit and Loss Flow Final].[Gross Profit]) AS [Gross Profit], Sum([zz Profit and Loss Flow Final].[SPAs]) AS SPAs, Sum([zz Profit and Loss Flow Final].[Samples]) AS Samples, Sum([zz Profit and Loss Flow Final].[Other Selling Exp]) AS OtherSellingExp, Sum([zz Profit and Loss Flow Final].[Salaries]) AS Salaries, Sum([zz Profit and Loss Flow Final].[Travel & Enter]) AS [Travel & Enter], Sum([zz Profit and Loss Flow Final].[Primary Budget]) AS [Primary Budget], Sum([zz Profit and Loss Flow Final].[T&E Bud]) AS [T&E Bud], Sum([zz Profit and Loss Flow Final].[Add'l Budget]) AS [Add'l Budget], Sum([zz Profit and Loss Flow Final].[Sal Budget]) AS [Sal Budget] INTO storeOutput" & vbCrLf & _
- "FROM [zz Profit and Loss Flow Final]" & vbCrLf & _
- "WHERE ((([zz Profit and Loss Flow Final].[Region]) Like " & Chr(34) & " * " & Chr(34) & " & [Forms]![Sort].[Territory] & " & Chr(34) & " * " & Chr(34) & ") And (([zz Profit and Loss Flow Final].[State]) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![Sort].[State] & " & Chr(34) & " * " & Chr(34) & ") And (([zz Profit and Loss Flow Final].[Brand]) Like " & Chr(34) & " * " & Chr(34) & " & [Forms]![Sort].[Brand] & " & Chr(34) & " * " & Chr(34) & "))" & vbCrLf & _
- "GROUP BY [zz Profit and Loss Flow Final].[Year-Month];"
- If Me.Territory <> "" Then
- stTerritory = Me.Territory
- Else
- stTerritory = "All"
- End If
- If stStatePrint = "" Then
- stState = "All"
- End If
- If Me.Brand <> "" Then
- stBrand = Me.Brand
- Else
- stBrand = "All"
- End If
- DoCmd.SetWarnings False
- 'DoCmd.OpenQuery "makeOutput"
- DoCmd.SetWarnings True
- 'create the recordset
- Set db = CurrentDb()
- db.Execute stSQL, dbFailOnError
- 'Set qdf = db.CreateQueryDef("MakeOutput2", stSQL)
- 'Query Parameters
- 'qdf.Parameters("Region") = Me.Territory
- 'qdf.Parameters("State") = Me.State
- 'qdf.Parameters("Brand") = Me.Brand
- 'Resolve the Parameters
- 'For Each prm In qdf.Parameters
- ' prm.Value = Eval(prm.Name)
- 'Next
- Set rs = db.OpenRecordset("storeOutput")
- 'Set rs = qdf.OpenRecordset()
- 'rs.MoveLast: rs.MoveFirst
- If rs.RecordCount = 0 Then Exit Sub