By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,077 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,077 IT Pros & Developers. It's quick & easy.

Multi-select list box query

P: 7
I have read the other posts on this subject, but I am still having issues.

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
  1. Option Compare Database
  2. Dim db As DAO.Database
  3. Dim qdf As DAO.QueryDef
  4. Dim rs As DAO.Recordset
  5. Dim prm As DAO.Parameter
  6. Private Sub Run_Click()
  7. On Error GoTo Err_Run_Click
  8.     Dim SpreadSheetSource As String
  9.     Dim SpreadSheetOutput As String
  10.     Dim stTerritory As String
  11.     Dim stState As String
  12.     Dim stStatePrint As String
  13.     Dim stBrand As String
  14.     Dim dFirstMonth As Date
  15.     Dim stDocName As String
  16.     Dim Suc As Boolean
  17.     Dim varItem As Variant
  18.     Dim listItems As Control
  19.     Dim stSQL As String
  20.  
  21.     'Data definitions
  22.     'SpreadSheetSource = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow.xls"
  23.     'SpreadSheetOutput = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow_new.xls"
  24.     SpreadSheetSource = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow.xls"
  25.     SpreadSheetOutput = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow_new.xls"
  26.  
  27.  
  28.     stDocName = "output"
  29.         dFirstMonth = Me.firstmonth
  30.  
  31.     'list box
  32.     Set listItems = Me.State
  33.     For Each varItem In listItems.ItemsSelected
  34.         If stState > "" Then
  35.             stState = stState & " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
  36.             stStatePrint = stStatePrint & ", " & listItems.ItemData(varItem)
  37.         Else
  38.             stState = " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
  39.             stStatePrint = listItems.ItemData(varItem)
  40.         End If
  41.         MsgBox (stSate)
  42.     Next varItem
  43.  
  44.     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 & _
  45.             "FROM [zz Profit and Loss Flow Final]" & vbCrLf & _
  46.             "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 & _
  47.             "GROUP BY [zz Profit and Loss Flow Final].[Year-Month];"
  48.  
  49.  
  50.     If Me.Territory <> "" Then
  51.         stTerritory = Me.Territory
  52.     Else
  53.         stTerritory = "All"
  54.     End If
  55.     If stStatePrint = "" Then
  56.         stState = "All"
  57.     End If
  58.     If Me.Brand <> "" Then
  59.         stBrand = Me.Brand
  60.     Else
  61.         stBrand = "All"
  62.     End If
  63.  
  64.     DoCmd.SetWarnings False
  65.     'DoCmd.OpenQuery "makeOutput"
  66.     DoCmd.SetWarnings True
  67.  
  68.     'create the recordset
  69.     Set db = CurrentDb()
  70.     db.Execute stSQL, dbFailOnError
  71.     'Set qdf = db.CreateQueryDef("MakeOutput2", stSQL)
  72.  
  73.  
  74.     'Query Parameters
  75.     'qdf.Parameters("Region") = Me.Territory
  76.     'qdf.Parameters("State") = Me.State
  77.     'qdf.Parameters("Brand") = Me.Brand
  78.  
  79.     'Resolve the Parameters
  80.     'For Each prm In qdf.Parameters
  81.     '   prm.Value = Eval(prm.Name)
  82.     'Next
  83.     Set rs = db.OpenRecordset("storeOutput")
  84.  
  85.     'Set rs = qdf.OpenRecordset()
  86.     'rs.MoveLast: rs.MoveFirst
  87.  
  88.     If rs.RecordCount = 0 Then Exit Sub
May 12 '09 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 487
You have to user ‘New’ Instances to create objects
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. 'To
  3. Dim db As New DAO.Database
  4.  
May 13 '09 #2

P: 7
I now get, "Invalid use of New keyword"
May 13 '09 #3

QVeen72
Expert 100+
P: 1,445
Hi,

For DAO, not Necessary to Instantiate with "New" keyword..

Just try this, Wrap the Sates with Single Quotes, Instead of Double..:
And Use an "OR"

stState = stState & " ([zz Profit and Loss Flow Final].[State]) Like '*" & listItems.ItemData(varItem) & "*) ' OR "

finally strip off the last OR
If Trim(stState) <> "" Then
stState = Left(stState, Len(stState)-3)
End If

Also, In Your SQL Statement,

Remove this Part :
And (([zz Profit and Loss Flow Final].[State]) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![Sort].[State]

' No Need To Mention Again Field Name, It is already included in stState
Replace It With : And " & stState

Change the same for all the Fields


Regards
Veena
May 14 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.