SQL nesting

[Swifey]

I'm sorry to bother you again - As before I'm trying to create 12 input boxes for the user to type a single or multiple customerno. I tried looking at your SQL nesting code from my previous request, but I dont know what your code is doing so that I can modify my current SQL. My current SQL code is as follows:

SELECT [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, Sum([dbo_tConsolSales PDA].quantity) AS SumOfquantity, [dbo_tConsolSales PDA].linecostvalue, Sum([dbo_tConsolSales PDA].linesellvalue) AS SumOflinesellvalue, [dbo_tConsolSales PDA].salesperiod
FROM ([dbo_tConsolSales PDA] INNER JOIN dbo_tSAPMaterials ON [dbo_tConsolSales PDA].material = dbo_tSAPMaterials.material) INNER JOIN [dbo_tCustomer pda] ON [dbo_tConsolSales PDA].customerno = [dbo_tCustomer pda].customerno
GROUP BY [dbo_tConsolSales PDA].salesoffice, [dbo_tConsolSales PDA].billingdoc, [dbo_tConsolSales PDA].customerno, [dbo_tCustomer pda].custname, dbo_tSAPMaterials.prodgrp, dbo_tSAPMaterials.product, dbo_tSAPMaterials.description, [dbo_tConsolSales PDA].linecostvalue, [dbo_tConsolSales PDA].salesperiod
HAVING ((([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto1] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto2] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto3] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto4] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto5] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto6] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto7] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto8] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto9] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto10] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto11] Or ([dbo_tConsolSales PDA].customerno)=[Forms]![Frontpage]![soldto12]) AND (([dbo_tConsolSales PDA].salesperiod) Between [Forms]![Frontpage]![cstart] And [Forms]![Frontpage]![month]));

Could you please help.
Swifey

 

[SydneyGeek]

How about this?

Change the following...
With Me!lstCustomer

To...
With [lstCustomer]

Or...
With lstCustomer

Denis

 

[Swifey]

cheers - fingers crossed

 

[Swifey]

Morning - It now passes

With lstCustomer

but fails at

For Each varItem In .ItemsSelected

withh the following message
"run-time error '424' Object required"

Swifey

 

[SydneyGeek]

Just checking -- you do have a listbox called lstCustomer? The lower case L and 1 are easy to confuse in print -- make sure that the name in the code matches the one on your form

Denis

 

[Swifey]

ok - I'll check for you

 

[Swifey]

my name on the form and module are of the same

 

[SydneyGeek]

One more idea -- try
With CodeContextObject!lstCustomer

Denis

 

[Swifey]

Its now saying run-time error 7955 There is no current code context.object

 

[Swifey]

Its now saying run-time error 7955 There is no current code context.object

 

[SydneyGeek]

Strange -- It's got me stumped. Here's my original code in full. It runs in 97 and 2000 (just converted the database to check it). My SQL code is more straightforward, but that's not where your problem lies.

A thought -- you are triggering this from the OnClick event of a button on the form module, not a standard module?

Denis

Private Sub cmdPreview_Click()
  Dim strName As String
  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim strQuote
  Dim strSQL
  Dim qdf
  Dim dbs As Database
  
  Set dbs = CurrentDb()
  strQuote = Chr$(34)
  strList = ""
  
  With Me!lstEquip
    For Each varItem In .ItemsSelected
      strName = strQuote & .Column(0, varItem) & strQuote & ","
      strList = strList & strName
    Next
    
    strFilterText = Left(strList, Len(strList) - 1)
  End With
 [txtList] = strFilterText
 
 strSQL = "SELECT Equipment.* FROM Equipment WHERE (((Equipment.EquipmentNo) In (" & strFilterText & ")));"
 
 dbs.QueryDefs.Delete "qryEquipNoFilter"
 Set qdf = dbs.CreateQueryDef("qryEquipNoFilter", strSQL)
 
 
Select Case [cSheet]
  Case 8
    DoCmd.OpenReport "Inspection Sheet 8 Multi", acViewPreview
  Case 9
    DoCmd.OpenReport "Inspection Sheet 9 Multi", acViewPreview
  Case Else
    DoCmd.OpenReport "Inspection Sheet Multi", acViewPreview
End Select
End Sub