Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Red face Unanswered: Queries,forms and subforms help

    I am new to this forum, please excuse any mistakes that I may make

    I have a problem with a form that is suppose to work like this. A user enters text into a text field. A button "Search" is suppose to work by running the query to find a match for what was entered into the text field and display the results on a subform (located on the main form). The problem is that I cannot get it to work. Any suggestions? From anyone? Please?

  2. #2
    Join Date
    Mar 2004
    Posts
    33

    Lightbulb

    I just want to clarify.

    1. When you open the form, is the form already displaying records, data entry mode or is only the subform bound to a data source and the main form is unbound?

    2. Is your Search button located on the Main form or the subform?

    For the first, you will need to use the DoCmd.ApplyFilter in the search button's On Click event.

    Here is an example for a bound form:
    Dim ctl As Control 'variable
    Dim VariableValue 'name of control that has the focus
    ctl = Screen.PreviousControl 'returns name of previous control since button now has the focus
    VariableValue = ctl.Name

    DoCmd.ApplyFilter , VariableValue.Value 'filters the forms record source

    If you are using an unbound form and subform, you will need to specify the RecordSource for the subform before the DoCmd.ApplyFilter statement.

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Queries,forms and subforms help

    If the form is designed in such a way that there :
    1. a single textbox to enter what the user would like to search for
    2. a Search button
    3. a subform located on the main form itself

    and it suppose to work (in theory) like this:
    Enter some data in to the text field, click on the "Search" button. Theoretically speaking this is suppose to trigger the sql query to run and perform the required search. if there is data that matches the what the user entered in the textbox, it is suppose to be visible in the subform.

    What I have so far:

    the record source for the subform is the query that looks like this (i hope that it is correct).
    SELECT Book.Book_ID, Book.Book_Name, Book.Genre, Author.Author_LName, Author.Author_FName, Author.Author_ID
    FROM Author INNER JOIN Book ON Author.Author_ID = Book.Author_ID
    WHERE (((Book.Book_Name) Like '*' & ' [Forms![frmBookTitle]![Book subform]![Text2]' & "*"));

    The textfield is unbound

    The search button has the following (i've playing with it,so the code may be wrong, I was attempting to get the information printed out on a separate screen rather than on the subform)

    Private Sub Command21_Click()
    'DoCmd "RunSQL" qrySearchBookTitle "Book_Name"=&Me![Book_Name]
    DoCmd.OpenQuery "qrySearchBook", acViewNormal

    That is in short what i have.

    The problem: NOTHING HAPPENS
    What i'm I missing here
    HELP

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Queries,forms and subforms help

    Originally posted by human_fuel
    If the form is designed in such a way that there :
    1. a single textbox to enter what the user would like to search for
    2. a Search button
    3. a subform located on the main form itself

    and it suppose to work (in theory) like this:
    Enter some data in to the text field, click on the "Search" button. Theoretically speaking this is suppose to trigger the sql query to run and perform the required search. if there is data that matches the what the user entered in the textbox, it is suppose to be visible in the subform.

    What I have so far:

    the record source for the subform is the query that looks like this (i hope that it is correct).
    SELECT Book.Book_ID, Book.Book_Name, Book.Genre, Author.Author_LName, Author.Author_FName, Author.Author_ID
    FROM Author INNER JOIN Book ON Author.Author_ID = Book.Author_ID
    WHERE (((Book.Book_Name) Like '*' & ' [Forms![frmBookTitle]![Book subform]![Text2]' & "*"));

    The textfield is unbound

    The search button has the following (i've playing with it,so the code may be wrong, I was attempting to get the information printed out on a separate screen rather than on the subform)

    Private Sub Command21_Click()
    'DoCmd "RunSQL" qrySearchBookTitle "Book_Name"=&Me![Book_Name]
    DoCmd.OpenQuery "qrySearchBook", acViewNormal

    That is in short what i have.

    The problem: NOTHING HAPPENS
    What i'm I missing here
    HELP
    Ok ... Since the subform is bound to a query all you need to do is verify the query is correct (what you post is not) and merely do a Requery on the subform ... Oh, and ditch the RunSQL stuff ... You should replace the RowSource of the subform and requery ...

    Me.SubformControlName.Requery

  5. #5
    Join Date
    Mar 2004
    Posts
    9

    Re: Subform and query

    I tried what u said, and tried the fix the sql, i got the statement to run using paramters, and output the results as a preview, but the statement is accepting only exact searches to match the data in the table. I removed the "LIKE" but it still does not work with the subforms
    This is really bugging me now....i've been stuck with this for 2 weks now.
    Any suggestions?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Subform and query

    Originally posted by human_fuel
    I tried what u said, and tried the fix the sql, i got the statement to run using paramters, and output the results as a preview, but the statement is accepting only exact searches to match the data in the table. I removed the "LIKE" but it still does not work with the subforms
    This is really bugging me now....i've been stuck with this for 2 weks now.
    Any suggestions?
    I see ... What is your new SQL query looking like? Corecting your previous SQL to this:

    "SELECT Book.Book_ID, Book.Book_Name, Book.Genre, Author.Author_LName, Author.Author_FName, Author.Author_ID
    FROM Author INNER JOIN Book ON Author.Author_ID = Book.Author_ID
    WHERE (Book.Book_Name LIKE '*" & [Forms![frmBookTitle]![Book subform]![Text2] & "*');"

Posting Permissions

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