Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: Search Query Results into a Subform

    Hi everyone!I need some help in getting my search query to display results in a subform.
    I have an umbound search form (MasterSearch3) where users can search the table by 2 criteria (unbound comboboxes named Customer Surname and Site Post Code that select entries from MoreSpace Master table). I have designed SQL query (MasterSearch) that searches for the record based on those 2 criteria:

    SELECT (I select all of my fields)
    FROM [MoreSpace Master]
    WHERE ((([MoreSpace Master].[Customer Surname])=Forms![MasterSearch3]![Customer Surname]) And (([MoreSpace Master].[Site Post Code])=Forms![MasterSearch3]![Site Post Code])) Or ((([MoreSpace Master].[Site Post Code])=Forms![MasterSearch3]![Site Post Code]) And ((IsNull(Forms![MasterSearch3]![Customer Surname]))<>False)) Or ((([MoreSpace Master].[Customer Surname])=Forms![MasterSearch3]![Customer Surname]) And ((IsNull(Forms![MasterSearch3]![Site Post Code]))<>False)) Or (((IsNull(Forms![MasterSearch3]![Customer Surname]))<>False) And ((IsNull(Forms![MasterSearch3]![Site Post Code]))<>False));

    Now, the query itself works fine and picks up appropriate records without any problem, but when I create a form based on this query and make it into a subform (SubFormSearch) in MasterSearch3 form and place a button in parent form ([SubFormSearch].Requery) nothing happens.

    I have been playing around with it for few days now and am stuck...
    Can anyone point me in the right direction please?
    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    This is because a form open as a subform is not a member of the Forms collection, so the references to Forms![MasterSearch3] are not valid (i.e. there is no object named MasterSearch3 in the Forms collection at the moment). Try using Form_MasterSearch3 (not tested).

    Another solution would consist in rebuilding the SQL statement dynamically in the AfterUpdate event handler of each combo and reassign the resulting string as the RecordSource of the subform.

    You could also open a QueryDef object on the query (if it's a permanent one) and modify its SQL property, which would yield the same results.
    Have a nice day!

Tags for this Thread

Posting Permissions

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