Results 1 to 7 of 7
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Passing Data from a Text Box to a Query (A2K - SR1)

    Is it possible to pass data from a Text Box to the Criteria Section of a query?

    I have a multi select list box where the user selects data. I have written the code to concatenate the data selected into the proper format, i.e, 'C.1.1.X'.,'C.1.2.3','C.1.2.X' I have then passed this concatenated string to a text box. Can the text box then be referenced in the Criteria section of a query as part of an IN statement, i.e,. In (([Forms]![FORM - PA_WBS]![GetString])) ... GetString is the name of the text box.

    The SQL code looks as follows:

    SELECT [TABLE - PA_WBS WBS SELECTION].WBSID, [TABLE - PA_WBS WBS SELECTION].[WBS DESCRIPTION], [TABLE - PA_WBS WBS SELECTION].[WBS LEVEL]
    FROM [TABLE - PA_WBS WBS SELECTION]
    WHERE ((([TABLE - PA_WBS WBS SELECTION].WBSID) In (([Forms]![FORM - PA_WBS]![GetString]))));

    If I type the values 'C.1.1.X'.,'C.1.2.3','C.1.2.X' as part of the IN statement, the query returns the correct data. However, when I pass the Textbox data, I do not get any results. The textbox contains the correct data as well as shown on the form.

    Any ideas??
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Data from a Text Box to a Query (A2K - SR1)

    Gary,

    strSQL = "SELECT [TABLE - PA_WBS WBS SELECTION].WBSID, [TABLE - PA_WBS WBS SELECTION].[WBS DESCRIPTION], [TABLE - PA_WBS WBS SELECTION].[WBS LEVEL]
    FROM [TABLE - PA_WBS WBS SELECTION]
    WHERE ((([TABLE - PA_WBS WBS SELECTION].WBSID) In ((" & [Forms]![FORM - PA_WBS]![GetString] & "))));"


    [Forms]![FORM - PA_WBS]![GetString] must not be include in the quotes if you are refering the textbox from within code.
    Francois

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Passing Data from a Text Box to a Query (A2K - SR1)

    [Forms]![FORM - PA_WBS]![GetString] must not be include in the quotes if you are refering the textbox from within code.

    [Forms]![FORM - PA_WBS]![GetString] is not included in the quotes, but is is still not working.

    Can I pass the textbox string to the parameter portion of the query without generating the strSQL statement that you suggested?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Data from a Text Box to a Query (A2K - SR1)

    With the function IN, I don't know
    What you can do is add a column to you query with the expression
    InStr([forms]![FORM - PA_WBS]![Getstring],[TABLE - PA_WBS WBS SELECTION].[WBSID])
    and in the criteria <> 0

    You can make this column invisible.

    Hope this help
    Francois

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Passing Data from a Text Box to a Query (A2K - SR1)

    I came up with a "Crude" way to do this, but it works.

    After the user selects the listbox records by highlighting them (I put a limit to the total number of 25), they press a get data button to execute the code. I placed a counter in the For Each VarItem In Me!SELECTWBS.ItemsSelected such that it increments on each pass. If the counter = 1, then I assigned the value of the first item in the listbox to Texbox1, 2 to Textbox 2, and so on. Once complete, I can use the IN clause in the query object to pass the data as a parameter query. Note: Upon each start, all values in the textbox are set to "".

    A copy of the "Crude" code is shown below. Please let me know if there is a better way to do this (which I am sure there is!)


    Private Sub Command22_Click()
    Dim StrItemsSelcted As String, VarItem As Variant
    Dim SELECT_WBS_RESULTS As String
    Dim STRSQL As String
    Dim C As Integer ' Provides total count of items selected
    Dim X As Integer

    'RESET ALL INPUT TEXTBOX DATA TO NULL
    INPUT1 = ""
    INPUT2 = ""
    INPUT3 = ""
    INPUT4 = ""
    INPUT5 = ""
    INPUT6 = ""
    INPUT7 = ""
    INPUT8 = ""
    INPUT9 = ""
    INPUT10 = ""
    INPUT11 = ""
    INPUT12 = ""
    INPUT13 = ""
    INPUT14 = ""
    INPUT15 = ""
    INPUT16 = ""
    INPUT17 = ""
    INPUT18 = ""
    INPUT19 = ""
    INPUT20 = ""
    INPUT21 = ""
    INPUT22 = ""
    INPUT23 = ""
    INPUT24 = ""
    INPUT25 = ""
    INPUT26 = ""

    'Exit Subroutine if the User does not select anything in the listbox
    If Me!SELECTWBS.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more WBSIDs before pressing the Get Data Button"
    Exit Sub
    End If

    ' Set C = to the total number of records selected
    C = Me!SELECTWBS.ItemsSelected.Count

    If Me!SELECTWBS.ItemsSelected.Count > 25 Then
    MsgBox "Do Not Select More then 25 WBS Elements as one time."
    Exit Sub
    End If

    X = 0
    'Loop through the items selected in the listbox
    For Each VarItem In Me!SELECTWBS.ItemsSelected
    X = X + 1

    If X = 1 Then
    INPUT1 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 2 Then
    INPUT2 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 3 Then
    INPUT3 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 4 Then
    INPUT4 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 5 Then
    INPUT5 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 6 Then
    INPUT6 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 7 Then
    INPUT7 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 8 Then
    INPUT8 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 9 Then
    INPUT9 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 10 Then
    INPUT10 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 11 Then
    INPUT11 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 12 Then
    INPUT12 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 13 Then
    INPUT13 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 14 Then
    INPUT14 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 15 Then
    INPUT15 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 16 Then
    INPUT16 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 17 Then
    INPUT17 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 18 Then
    INPUT18 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 19 Then
    INPUT19 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 20 Then
    INPUT20 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 21 Then
    INPUT21 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 22 Then
    INPUT22 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 23 Then
    INPUT23 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 24 Then
    INPUT24 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 25 Then
    INPUT25 = Me!SELECTWBS.Column(1, VarItem)
    ElseIf X = 26 Then
    INPUT26 = Me!SELECTWBS.Column(1, VarItem)
    Else: INPUT27 = Me!SELECTWBS.Column(1, VarItem)
    End If

    ' Grab the Column for each selected item
    'StrItemsSelcted = StrItemsSelcted & "'" & Me!SELECTWBS.Column(1, VarItem) & "'" & ","

    Next VarItem

    'Remove Extra Comma
    'StrItemsSelcted = Left$(StrItemsSelcted, (Len(StrItemsSelcted) - 1))

    GetString = StrItemsSelcted
    'STRSQL = "SELECT [TABLE - PA_WBS WBS SELECTION].WBSID,[TABLE - PA_WBS WBS SELECTION].[WBS DESCRIPTION],[TABLE - PA_WBS WBS SELECTION].[WBS LEVEL]FROM [TABLE - PA_WBS WBS SELECTION]WHERE ((([TABLE - PA_WBS WBS SELECTION].WBSID) In ((StrItemsSelcted))));"


    End Sub
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post
    Charlotte

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Passing Data from a Text Box to a Query (A2K - SR1)

    Charlotte,

    Bravo and thanks for the input. I looked at Dev Ashish's original article, but, the other thread you provided reviews exactly what I want to do and I may have a variation on it as well. Once again, thanks for your input and help.
    <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> - You deserve one.....
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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