none
Using Text boxes for Searching RRS feed

  • Question

  • Hello,

    I am having trouble with using a text box that includes a SQL statement.

    Here's what I got so far in SQL Code:

    SELECT *
    FROM WhiteboardX
    WHERE (((WhiteboardX.Entry)=[forms]![QueryTextBox].[value]));

    What I want to do is to embed it in an unbound Sub Form control within the Form.

    What I want to do is to use the Query Text Box as a search query, so that it can receive results in the sub form below.
    Should I try to use the DLookup function?
    • Edited by JohnDBCTX Monday, January 4, 2016 4:02 AM DLookup Question
    Monday, January 4, 2016 2:09 AM

Answers

  • DLookup is an Access function, and is not available in .NET

    I have converted the data into VB.net as a working copy of it.

    VB.NET is a programming platform, not a data storage environment (like a database). Perhaps you mean you converted it to SQL Server, or something of that nature?

    Also, if you're now using VB.NET to work with the data, you should post a new question about that. .NET and Access are two entirely different animals.

    You can search a .NET datagridview, of course. Depending on what you'd like to do, there are several different ways to achieve that. But, as mentioned, you really should close this one out, and ask a new question if you're now doing this in .NET.


    -- Scott McDaniel, Microsoft Access MVP

    • Marked as answer by JohnDBCTX Monday, January 11, 2016 6:02 AM
    Wednesday, January 6, 2016 1:12 PM

All replies

  • Hi. I'm not sure I understand what you're asking. But if you're trying to create a search form, you might want to take a look at some of the demos available in the Code Archive at UtterAccess. Hope that helps...
    Monday, January 4, 2016 4:11 AM
  • Hello DB guy,

    I am having a lot of trouble using DLookup, which applies to the following source code.

    Private Sub FilterThis_Click()
    Dim qryX As String
    qryX = Me.QueryTextBox.Text
    Me.WhiteboardXSub.SetFocus
    Me.WhiteboardXSub.Requery
    Me.QueryTextBox.Text = DLookup("[Entry]", "WhiteboardX", [WhiteboardX]("Entry") = [Forms]![QueryTextBox].[Value])
    End Sub

    Can you figure this out?

    This also applies to the picture that I have uploaded.

    JohnDBCTX

    • Edited by JohnDBCTX Monday, January 4, 2016 6:25 AM Picture Relation
    Monday, January 4, 2016 6:24 AM
  • What sort of trouble are you having? Does the code work, but not give you the right results? Or do you receive an error?

    If you receive an error, then please post the error here, with the full text (a screenshot is best).

    Otherwise, if you want to show the results of your query in your subform, then you'd do something like this:

    Me.NameOfYourSubformCONTROL.Form.RecordSource = "SELECT * FROM WhiteboardX WHERE WhiteboardX.Entry=" & Me.QueryTextBox

    Note that "NameOfYourSubformCONTROL" is the name of the Subform Control on the Parent form, and may or may not be the name of the Form you're using as a subform. Be sure this is right - this trips up even seasoned developers.

    If "WhiteboardX.Entry" is a Text value, then you have to enclose that in single or double quotes:

    Me.NameOfYourSubformCONTROL.Form.RecordSource = "SELECT * FROM WhiteboardX WHERE WhiteboardX.Entry='" & Me.QueryTextBox & "'"

    This assumes that you're running this code on the same form that is hosting your subform.


    -- Scott McDaniel, Microsoft Access MVP

    Monday, January 4, 2016 10:17 AM
  • Hi John. Have you had a chance to take a look at any of the demos at UtterAccess? You might be able to save some time from reinventing the wheel, so to speak. Just a thought...
    Monday, January 4, 2016 9:17 PM
  • >>>SELECT *
    FROM WhiteboardX
    WHERE (((WhiteboardX.Entry)=[forms]![QueryTextBox].[value]));<<<

    According to your description, Could you make sure that this query is correct? If not, based on your screenshot, you could modify your query like below:

    SELECT *
    FROM WhiteboardX
    WHERE (((WhiteboardX.Entry)=[Forms]![PreEntriesSwitchboard].[QueryTextBox].[value]));

    Otherwise could you provide more information about your issue, for example error, screenshot etc., that will help us reproduce and resolve it.
    Thanks for your understanding.

    Tuesday, January 5, 2016 6:24 AM
  • I would try to put this in another way.

    Would the DLookup function be better then just VBA coding?

    By the way,  I have converted the data into VB.net as a working copy of it.

    Is the DLookup function included in the VB.net library.  If it is so, how can I use it to search data or connect between two or more tables known as data grids? 

    Probably, the purpose of utilizing a text box as a searchable query is almost similar to that of its data searching capabilities; such as a combo box, or a list box.

    Here is an updated screenshot:

    JohnDBCTX

    Wednesday, January 6, 2016 12:54 PM
  • DLookup is an Access function, and is not available in .NET

    I have converted the data into VB.net as a working copy of it.

    VB.NET is a programming platform, not a data storage environment (like a database). Perhaps you mean you converted it to SQL Server, or something of that nature?

    Also, if you're now using VB.NET to work with the data, you should post a new question about that. .NET and Access are two entirely different animals.

    You can search a .NET datagridview, of course. Depending on what you'd like to do, there are several different ways to achieve that. But, as mentioned, you really should close this one out, and ask a new question if you're now doing this in .NET.


    -- Scott McDaniel, Microsoft Access MVP

    • Marked as answer by JohnDBCTX Monday, January 11, 2016 6:02 AM
    Wednesday, January 6, 2016 1:12 PM