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.
Reply to group, rather than allenbrowne at mvps dot org.
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?
