Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: Beginners Question

    I have a form with a table as it's source. A command button on this form runs a macro with a WHERE condition that calls a query. A second from is then opened with the query results as it's source.

    Is it possible to return the results of this query into the first form without using a subform? How can I do this?

    If it is not possible is there another approach that I can take?

    Many thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Somewhere in Form2. You could place it in the Form2 'OnCurrent' event or perhaps in the 'OnClick' event on a button control:

    Code:
    Forms![Form1].RecordSource = Me.RecordSource
    Forms![Form1].Requery

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    Thanks CyberLynx,

    Actually, I'm not happy using a second form at all and would prefer to just run the query from form1 and return the results to form1.

  4. #4
    Join Date
    Dec 2003
    Posts
    16
    Originally posted by PeteShay
    Thanks CyberLynx,

    Actually, I'm not happy using a second form at all and would prefer to just run the query from form1 and return the results to form1.
    Would you be satisfied with a list box on form1 showing the results of your query (assuming it doesn't return more than 1K records or so)? It will be readonly. There are a couple of ways to approach this.

    1) OnClick runs a sub which builds a SQL string from the data in your form controls and uses it to set the list box rowsource.

    2) OnClick requeries the listbox bound to a stored query which references the appropriate controls in your form.

    Using this technique along with appropriate listbox properties is a quick way to focus on either a single record or a group.

  5. #5
    Join Date
    Dec 2003
    Posts
    10
    Thanks for your reply,

    I am very new to this (this is my first database!).

    I have a table - MailingList - containing a list of names, addresses etc and a form - DataEntry - with this table as it source. I have a text box on the form in which the user will type a surname then click a button which I want to update the form to contain only records from the table with this surname.

    At the moment, I can send the results to a second form by having a macro execute when the button is clicked - the macro performs the OpenForm action with the following WHERE condition

    [MailingList]![Surname]=[Forms]![DataEntry]![SurnameSearch].

    However, I don't wish to use a second form, just update the first.


    Appreciate your patience, I'm at the early stages of trying to get to grips with access...

    Thanks.

  6. #6
    Join Date
    Dec 2003
    Posts
    16
    In that case I would just use the filter property of the form itself.

    In the button's OnClick sub:

    Me.Filter = "Surname=""Your surname""
    Me.FilterOn = True
    Me.Requery

    Strings have to be handled carefully.

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Or.......

    Me.Filter = "[Surname] = '" & Me.SurnameTextBox & "'"
    Me.FilterOn = True
    Me.Requery



  8. #8
    Join Date
    Dec 2003
    Posts
    10
    Thanks bluejamul and CyberLynx, that worked just great.

    I put a second textbox and button on the from to do the same, but with a different field (autonumber) in the table.

    When I enter a value in the textbox (Code) and click the button (CodeSearch) I get the following error:

    Run-time error '2001';

    You cancelled the previous operation.

    Any idea why I'm getting this error?

    As I said, using the Surname search as you recommended works just fine.

    Many thanks.

  9. #9
    Join Date
    Dec 2003
    Posts
    10
    LIKE instead of = works.

Posting Permissions

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