Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stopping a parameter query (XP, 2000)

    I have a mainform (which is actually a subform to another mainform) with an unbound textbox that, when filled in, serves as a search parameter for a query whose results are displayed in a subform.

    It works fine when I open the form in isolation but when it is part of the main/subform family, I am prompted for a parameter when the form opens. I prefer for the form to simply display all records when it initially opens (and the textbox control is empty). Then, when the textbox control is updated, perform the search using that criteria.


    How can I make it stop asking for a parameter? I understand that subforms load first so that is probably why it asks for a parameter.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stopping a parameter query (XP, 2000)

    When the form is opened by itself, you can refer to a text box on it as
    [Forms]![FormName]![TextBoxName]
    but if it is a subform on a main form, you must refer to the same text box as
    [Forms]![MainFormName]![FormName]![TextBoxName]
    In other words, you have a problem if you want to use the same form as a stand-alone form and as a subform and also use the value of a text box on it as parameter in the same query. You must have one query that refers to the text box in the first way, and another query that refers to the text box in the second way. And you must either create two instances of the subform, one for each query, or make the subform unbound and set the Record Source in code to one or the other query depending on how the form is opened.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stopping a parameter query (XP, 2000)

    Just to recap, I have a mainform (master input form) and a subform (query_input). The subform has an unbound textbox control (searchbox) and a subsubform (prospect_lookup) that displays selected records in a datasheet view. Whatever is put into that textbox control is used as the parameter for the search that is subsequently shown in the prospect_lookup form. When I run the main form (master input form) I receive the attached message about bad syntax. I can't find the bad syntax.

    My select statement reads:

    SELECT PROSPECTS_Table.[Prospect/Area Name], PROSPECTS_Table.[Original/Alias Prospect Name], Count(LINE_Table.[Line #]) AS [CountOfLine #] FROM PROSPECTS_Table LEFT JOIN LINE_Table ON PROSPECTS_Table.ProspectID=LINE_Table.ProspectID GROUP BY PROSPECTS_Table.[Prospect/Area Name], PROSPECTS_Table.[Original/Alias Prospect Name], PROSPECTS_Table.ProspectID HAVING (((PROSPECTS_Table.[Prospect/Area Name]) Like "*" & [forms]![master input form]![query_input]![searchbox] & "*")) Or (((PROSPECTS_Table.[Original/Alias Prospect Name]) Like "*" [forms]![master input form]![query_input]![searchbox] & "*")) Or (((Count(LINE_Table.[Line #])) Like "*" & [forms]![master input form]![query_input]![searchbox] & "*")) ORDER BY PROSPECTS_Table.[Prospect/Area Name];
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stopping a parameter query (XP, 2000)

    This is hard to say, but just from looking at it, I would say that the part

    (((Count(LINE_Table.[Line #])) Like "*" & [forms]![master input form]![query_input]![searchbox] & "*"))

    doesn't make much sense. A count of records that is like "*[parameter]*" ? Is that what you intend?

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stopping a parameter query (XP, 2000)

    That is not exactly what I intended. I want the user to to be able to perform a *XXX* search on two fields in one table and another field in a different table. Such as:

    Prospects_Table - 2 fields : [Prospect/Area Name] & [Original/Alias Prospect Name]
    LINE_Table - 1 field: [Line #]

    Since there is a many to one relationship between line_table and Prospects_table, I wanted the search to find every instance where [Prospect/Area Name], [Original/Alias Prospect Name], OR [Line #] contained the *XXX* parameter. Then display every prospect record identified along with a count of line #'s related to that prospect.

    Does that make sense? In other words, the use may have a string but not recall if their string is part of a current prospect name, an old prospect name or a Line # (which is alpha/numeric), There are too many line #'s to display so I thought a count would be sufficient.

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stopping a parameter query (XP, 2000)

    I tried to rebuild the select statement using the following code and found that Access rejects it as invalid syntax AND highlights [forms] as the problem.

    Like "*" [forms]![master input form]![query_input]![searchbox] & "*"

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Stopping a parameter query (XP, 2000)

    Change
    Like "*" [forms]![master input form]![query_input]![searchbox] & "*"
    to
    Like "*" & [forms]![master input form]![query_input]![searchbox] & "*"

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stopping a parameter query (XP, 2000)

    Thank you - I should have seen that!

Posting Permissions

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