Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Search in Forms with three tables

Discussion in 'Information Technology' started by MarkusJohn, Jul 28, 2006.

  1. MarkusJohn

    MarkusJohn
    Expand Collapse
    Guest

    Hello,

    I have a form linking to three tables. (Customer, flats and orders)

    Making a new entry I can selct a customer, a flat and enter afterwards
    the order details.
    I used drop down boxes to display customer and flats.

    How can I after having entered an order search for a specific order?
    Would like to search for name, flat or order number.

    Do I have to make three extra forms to realise that?
    I´m thinking of three forms with a query for the searchable value.

    Or is there a better solution?

    Where can I find tips about entering, altering, deleting data for a
    form with more than one table?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The answer will depend how the tables are related.

    If your Orders table has fields such as:
    OrderID primary key
    CustomerID relates to the Customer table's primary key
    FlatID relates to the Flat table's primary key
    then you could design the form so that its RecordSource is just the Orders
    table, and still select values for CustomerID and FlatID by using a pair
    combos with their RowSource set to the respective tables.

    When you bind a form to a multi-table query, you run the chance that the
    recordset is not updatable. If the other table(s) are just lookups (i.e. the
    table you are actually entering data into has foreign key fields to those
    tables), you can still get away with it. The end of this reply lists things
    that prevent the query being updatable.

    Now, your question was about searching the form. Essentially, you can search
    on any field that is in the form's RecordSource. So if you have CustomerID
    in the recordsource (because it is in the Orders table, or because it is in
    the query that you are using for the form's RecordSource), you can search
    this field. If you use an unbound combo as the interface for the search, the
    user will be able to choose the customer by name, even though the CustomerID
    is the field you have available.

    In general, if a search can return multiple records (one customer can have
    multiple orders), the simplest interface is to filter the form to results
    that match. You can provide multiple unbound controls for the search, and
    filter the form based on any combination of those that the user chooses to
    use. For an example of doing that, download this little example (Access 2000
    and later):
    http://allenbrowne.com/unlinked/Search2000.zip

    If the field you want to search on is not in the form's RecordSource, you
    still have 2 options:

    a) Dynamically change the RecordSource to an INNER JOIN that limits it. That
    technique is illustrated in this article:
    Filter a Form on a Field in a Subform
    at:
    http://allenbrowne.com/ser-28.html

    b) Include a subquery in the filter string. The subquery can include other
    tables. For example, if you have a form bound to your Customer table, and
    you want to filter it to those who had an order last year:
    Me.Filter = "EXISTS (SELECT OrderID FROM Orders WHERE (Orders.CustomerID
    = Customer.CustomerID) AND (OrderDate Between #1/1/2005# And #12/31/2005#))"
    Me.FilterOn = True

    If subqueries are new, see:
    How to Create and Use Subqueries
    at:
    http://support.microsoft.com/?id=209066

    The query will be read-only if any of the following apply:
    .. It has a GROUP BY clause (totals query).
    .. It has a TRANSFORM clause (crosstab query).
    .. It contains a DISTINCT predicate.
    .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
    (performs aggregation).
    .. It involves a UNION.
    .. It has a subquery in the SELECT clause.
    .. It uses JOINs of different directions on multiple tables in the FROM
    clause.
    .. The query's Recordset Type property is set to Snapshot.
    .. The query is based on another query that is read-only (stacked query.)
    .. Your permissions are read-only (Access security.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "MarkusJohn" <john.m@gmx.net> wrote in message
    news:1147159341.106615.180010@u72g2000cwu.googlegroups.com...
    Hello,

    I have a form linking to three tables. (Customer, flats and orders)

    Making a new entry I can selct a customer, a flat and enter afterwards
    the order details.
    I used drop down boxes to display customer and flats.

    How can I after having entered an order search for a specific order?
    Would like to search for name, flat or order number.

    Do I have to make three extra forms to realise that?
    I´m thinking of three forms with a query for the searchable value.

    Or is there a better solution?

    Where can I find tips about entering, altering, deleting data for a
    form with more than one table?
     
  4. MarkusJohn

    MarkusJohn
    Expand Collapse
    Guest

    Thank you for you tip, it works great! I just have one problem..
    In the form order I display only the customer number, but the search
    result should display the customer´s name as well. I can´t select in
    the control source customername.
    1) How can I lookup this field in the detail section
    2) what do I have to type in in the event procedure for ([Name)]. This
    field should search the typed in name within the result list..
    Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtNachname) Then
    strWhere = strWhere & "([Name] Like ""*" & Me.TxtNachname &
    "*"") AND "
    End If

    Thank you very much for you help!!
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Firstly, don't use a control named Name. Forms (and most other things in
    Access) have a Name property, so Access will misunderstand your code, and
    search for the name of the form instead of the contents of the Name textbox.

    Now for some possible solutions for your question:

    Solution a:
    If you only have a few hundred names -- even a couple of thousand -- you
    could avoid this issue by using a combo instead of a text box. You can set
    up the unbound combo so its Bound Column is the CustomerID, but the Column
    Widths property sets this column as zero width. The user can then select the
    name in the combo, and the invisible CustomerID is available to you.

    Solution b:
    Use DLookup() to get the CustomerID that matches the name. More info on how
    to do that:
    http://allenbrowne.com/casu-07.html

    Solution c:
    Use a subquery that returns all matches for that name or part of the name.
    More info:
    http://support.microsoft.com/?id=209066

    Solution d:
    Change the RecordSource of the subform to a query that includes the Customer
    name field(s). You can then sort/filter/search on that field.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "MarkusJohn" <john.m@gmx.net> wrote in message
    news:1147672056.935615.116610@i40g2000cwc.googlegroups.com...
    Thank you for you tip, it works great! I just have one problem..
    In the form order I display only the customer number, but the search
    result should display the customer´s name as well. I can´t select in
    the control source customername.
    1) How can I lookup this field in the detail section
    2) what do I have to type in in the event procedure for ([Name)]. This
    field should search the typed in name within the result list..
    Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtNachname) Then
    strWhere = strWhere & "([Name] Like ""*" & Me.TxtNachname &
    "*"") AND "
    End If
     

Share This Page