Cannot Use Query Criteria From text Box?

[Gscrot]

Hi. I wonder if I could get some help please. My issue is as follows.



I have some code that I have adapted to store a string of selections from a multi select list box In a variable. I then put the string from the variable in a hidden text box. But if I try to use that text by as a criteria in a query it won't show any results. But here is the puzzler. If I copy and paste the string into the criteria of the query it works. What am I missing.

Here is the code I have bodged together. Any help much appreciated.

sub Command24_Click()

'On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   
    Dim varItem As Variant      'Selected items
    Dim strWhere As String       'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    Dim ListFilter As String
    Dim hty As String
    Dim sql_temp_table As String
    Dim strPath As String
    Dim StrClean As String
      
    strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "all report"

'Form!OPB_Sub.Form.Visible = True

    'Loop through the ItemsSelected in the list box.
    With Forms![Order_Pad_builder]![Sheet_List]
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
               ' strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," And [House_Type] = " & Forms![Order_Pad_Builder]![House Type].value  & """
               strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
               StrClean = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'strWhere = strWhere(House_Type = """ & Me.House_Type & """)
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
            Next
          
        End With
           SQLSub_Order = " SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, [STOCK]![COST]*[spec]![QTY] AS Total FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![HouseType]));"

Forms![Order_Pad_builder]![OPB_Sub].Form.RecordSource = SQLSub_Order
 
     'Remove trailing comma. Add field name, IN operator, and brackets.
        
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
   StrClean = "In (" & Left$(strWhere, lngLen) & ")"
        strWhere = "[SHEET] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "SHEET: " & Left$(strDescrip, lngLen)
                      
             Forms![Order_Pad_builder]![OPB_Sub].Form.Filter = strWhere
             Forms![Order_Pad_builder]![OPB_Sub].Form.FilterOn = True
            
             Me.Txt_WhereStr = StrClean
          
            sql_temp_table = "SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, [STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019 FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![House Type]) AND ((spec.SHEET)='" & StrClean & "'));"
      
             DoCmd.RunSQL sql_temp_table
           
          End If
   
End If

End sub

[June7]

Please post code within CODE tags to retain indentation and readability.

IN() criteria in a query object cannot be dynamic so referencing a control for its input won't work. Pasting the string into query works because then it is static.

SELECT INTO will create a new table. Do you really want that?

[Gscrot]

Thanks for the reply.

Yes what I am trying to do is make a table from the query. It does not work if the query is select also. Can I just reference the variable with the string in it withing the sql query instead of the form control or will that not work also?

[June7]

Try concatenating reference to [House Type]. If it is a text type field, add apostrophe delimiters but don't use apostrophes for the IN() criteria and no = sign.

WHERE (((spec.HOUSE_TYPE)='" & [Forms]![Order_Pad_Builder]![House Type] & "') AND ((spec.SHEET) " & StrClean & "));"

Your looping code looks messed up. Review http://allenbrowne.com/ser-50.html

[Gscrot]

Thanks I will give that a try let you know if it works

[Gscrot]

Hi

I have tried that and it still returns no records for the query. However, when I post the line from the text box into the query builder it works. What I am trying to do is get the filtered records from a subform into a table. I thought an easy way to do this would be to make this query then filter it the same as the form and make the table. I am thinking that perhaps I have gone about this the wrong way.

[June7]

Did you make suggested edits? Post current code or provide db for analysis. Follow instructions at bottom of my post.

What is the ultimate goal - a report? Why do you feel saving filtered dataset to a new table is a solution?

[Gscrot]

Did you make suggested edits? Post current code or provide db for analysis. Follow instructions at the bottom of my post.

What is the ultimate goal - a report? Why do you feel saving filtered dataset to a new table is a solution?

Hi

The ultimate goal I am trying to achieve is this. I trying to create an order form to produce itemized orders that I can then send out as a report and store in a table. On the Order builder form, I enter the following items.

Use this data to get the items to work.

Site = PHLHP
Plot = 049
Date = Date

Then I pick the pack codes I want to send from the multi-select list box. I then click the command button to execute the code

The code then filters the data into the subform that I require.

I then struggled to work out how to append that data into a table so I could store what I had ordered. Then I got into the trouble we have already discussed and that is where I am stuck.

[June7]

Usually a form does data entry/edit for one table. Which table should OrderPadBuilder edit?

I am not understanding your tables and relationships. You did not set up relationships in Relationships window so no clue there.

Your code would be more legible with cleaner spacing and indentation.

You reference StrClean variable in the SQLSub_Order = line but StrClean has no value at that point. Move code to the block that removes trailing comma.

Have you step debugged?

[Gscrot]

Usually a form does data entry/edit for one table. Which table should OrderPadBuilder edit?

I am not understanding your tables and relationships. You did not set up relationships in Relationships window so no clue there.

Your code would be more legible with cleaner spacing and indentation.

You reference StrClean variable in the SQLSub_Order = line but StrClean has no value at that point. Move code to the block that removes trailing comma.

Have you step debugged?

That got it. I cannot thank you enough for your help.

Many thanks