Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2014
    Posts
    7

    Unanswered: Passing textbox value into sql query parameter

    Hi folks,

    I'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.

    I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.

    This is an example:

    The user enter the data:

    [Text0]
    A1C556CC3C-TNNN
    C010070H13

    The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"

    Code:
    test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
    [Forms]![Search]![Text0].Value = test
    DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
    In the SQL code I use the IN operator to find the exact value for each record:

    FindPartNo sql query:

    Code:
    SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
    FROM Classifications
    WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);
    The problem here is, the query doesn't return results but if I modifiy the query and I put:


    Code:
    WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
    ... the query returns the correct results.

    What do you think? I'm passing the value incorrect into the sql code?

    Thank you for your help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You transform the multi-line textbox values into a comma-separated list, however you pass the textbox values to the query, not the list:
    Code:
    In ([Forms]![Search]![Text0]);
    Have a nice day!

  3. #3
    Join Date
    Feb 2014
    Posts
    7
    Thank for your response Sinndho.

    When I change the values into the comma-separated list, I assign this value to the textbox

    Code:
    test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
        [Forms]![Search]![Text0] = test
    Once the textbox value is changed to "A1C556CC3C-TNNN","C010070H13", I ran the query. As my logic the query will do this:

    Code:
    SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
    FROM Classifications
    WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);
    and [Forms]![Search]![Text0] will be replaced for "A1C556CC3C-TNNN","C010070H13", so the query will be converted to

    Code:
    WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
    Which is a valid query code. But I can't figure out what I'm doing wrong. How can I pass the list as you previously mentioned?

    Thank you!

  4. #4
    Join Date
    Feb 2014
    Posts
    7
    Also, I'm running a debug.print in a test sub, with this code:


    Code:
    Private Sub Command146_Click()
    SQL = "SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy FROM Classifications WHERE Classifications.PartNumber In (" & [Forms]![Search]![Text0] & ");"
    Debug.Print SQL
    End Sub
    And the debug console returns:


    Code:
    SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy FROM Classifications WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
    So, I think the query look good but still not running

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. When you paste the SQL expression from the immediate window to a new query, does this query returns the data set you expect?

    2. Do you use the following expression directly in a query (i.e. not building it dynamically)?
    Code:
    ... In ([Forms]![Search]![Text0])
    3. Did you try to create a public property in the form:
    Code:
    Public Property Get SelList() As String
    
        SelList= "'" & Replace(Me.Text0.Value, Chr(13) & Chr(10), "'","'") & "'"
    
    End Property
    Then use this property instead of addressing the text box?
    Have a nice day!

  6. #6
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    No need for code if the multiline text box points to a table. The query can join that table.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ranman256 View Post
    No need for code if the multiline text box points to a table. The query can join that table.
    Not very likely! The Jet engine cannot process the contents of a multiline textbox into a comma-separated list: this does not work, nor in Access 2003, nor in Access 2007, nor in Access 2010.

    Apart from dynamically buid the query, here's what I propose:

    1. Create a single column table named Tbl_LookUp. The single column in this table is named PartNumber and has the same data type as the column PartNumber in the table Classifications (TEXT(??)).

    2. Change the query like this:
    Code:
    SELECT Classifications.BU, 
           Classifications.WisperPlantID,
           Classifications.PartNumber,
           Classifications.PartDesc, 
           Classifications.US_CL_Code, 
           Classifications.MX_CL_Code, 
           Classifications.TARIC_CL_Code, 
           Classifications.COEProject, 
           Classifications.Supplier, 
           Classifications.BrokerRequest, 
           Classifications.CreatedBy
      FROM Classifications INNER JOIN
           Tbl_LookUp ON Classifications.PartNumber = Tbl_LookUp.PartNumber;
    3. In the form, prior to open the query, use:
    Code:
        Dim var As Variant
        Dim i As Long
    
        var = Split(Me.Text0.Value, vbNewLine)
        CurrentDb.Execute "DELETE FROM Tbl_LookUp;", dbFailOnError
        For i = 0 To UBound(var)
            CurrentDb.Execute Replace("INSERT INTO Tbl_LookUp ( PartNumber ) VALUES ( '@V' );", "@V", var(i)), dbFailOnError
        Next i
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •