Re: Form to search database and return found records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for responding, Dave.

Let us take just 2 tables, showing details of properties (in one
table) and their owners (in another table). One owner can have many
properties, and one property can have multiple owners.

Table1 with OwnerId, FirstName, LastName.
Table2 with PropertyID, OwnerID, PctOwned, StreetAddress, City,
ZipCode, State.

Query1 pulls in fields from both tables.

I have a form - call this SearchForm - is populated with fields from
Query1; the same form has a subform - call this ResultsSubForm - with
fields from Query1. The SearchForm and ResultsSubForm are linked
through OwnerID. In the SearchForm I will one or more property details
(e.g., zipcode) and/or one or more owner details.

On the form I also have a command button 'Search' that captures the
required fields from the search form. When I click the Search command
button - I have the code to generate a search string - I want to
filter the ResultsSubForm with the search string and return matching
records.

thanks.

On Jun 21, 10:00 am, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP



"kansaskan...@xxxxxxxxx" wrote:
Dave, any further input on setting up the search mechanism? thanks.

On Jun 19, 5:09 pm,klingongardener<klingongarde...@xxxxxxxxx> wrote:
Dave:

Appreciate the detailed guidance.

I created a query that pulls in required data from all four tables.
I then created a form populated with fields from this query. To this
form I added a sub-form.
The sub-form is based on the same query. Hence there are two groups
of the same
fields (except that one group is within a sub-form). The two groups
are linked through
the Link Master Field and Link Child Field, such that when I press
page-up/down in
the Master Field, the records in both groups change in a synchronized
fashion. (At this
point, only one record is visible at a time in each group).

Thanks!!

kannan

On Jun 19, 3:57 pm, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Seems like we are getting a couple of things intermixed here. Lets start
with the subform. The way you keep a subform in sync with the main form
(that is, showing the correct child records in the subform for the record in
the mainform) is by using the Link Master Fields and Link Child Fields
properties of the subform control.

Now, lets be sure we understand what a subform really is. A subform is a
misleadingly named control type on a form. It is just a control, like a text
box, a combo box, a check box, etc. It contains no data of it's own. It
does, however, have a property named Source Object. The SourceObject
property will contain the name of a form. That form is just like any other
form except it is most likely either in datasheet or continuous view.

The subform control also has the Link Master Field(s) and Link Child
Field(s) properties. This is a lot like setting a relationship. You
identify the field(s) in the Master record that point to the child records in
the Link Master Field(s) property and the field(s) in the subform's source
object form that match the child records to the parent.

This way, each time you change master records, the subform will display the
related child records.

See if you can get that going, then lets talk in more detail about doing the
searches.
--
DaveHargis, Microsoft Access MVP

"klingongardener" wrote:
Thank you Carl. Unfortunately, I do not know enough about Access to
follow up on your suggestion. However, I appreciate your taking the
time to write.

And thank you too,Dave.
Yes, I need to be able to search across the four tables and retrieve
data by filtering with the search criteria.
I give some additional details below. For simplicity of exposition, I
use only one table:
--------------------------------
I have a form. In the Form Header Section, I have a single combo box
which is populated with zip codes from one of the four tables. (I
will eventually have additional combo boxes spanning all four tables).
--------------------------------
Properties for this combobox include:
Row Source: SELECT tblProperties.PrptyID, tblProperties.PrprtyZip FROM
tblProperties ORDER BY [PrprtyZip];
Bound Column: 2
--------------------------------
In the Detail Section (of the same form), I have a subform based on a
query. (I know enough to run the query separately, and filter/
retrieve data by zip code; and the query works).
--------------------------------
Incidentally, I put this together using various bits and pieces on
this site - but despite my best attempts, I am unable to connect the
filter to the subform.

Thanks to both of you.

kannan

On Jun 19, 2:06 pm, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Is this a form, subform, or is it a single form with a datasheet view of the
record source. Are you using it for all four tables?
--
DaveHargis, Microsoft Access MVP

"klingongardener" wrote:
In my Windows XP Access 2003 database (with 4 tables) I need a
'search form' where I can enter values (corresponding to different
tables) in the top half of the screen and have matching records
returned in the bottom half of the screen. I have been able to create
a form for the top half, and another one for the bottom half. I am
able to enter filter criteria in the top successfully (i.e.,
debug.print returns the correct filter string). However, I do not
know how to link the filter to the sub-form at the bottom.

I have gone through similar posts/solutions, but am still having
problems.

I would appreciate any advice.

Thank you.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Re: Form to search database and return found records
    ... I created a query that pulls in required data from all four tables. ... The way you keep a subform in sync with the main form ... the mainform) is by using the Link Master Fields and Link Child Fields ... identify the fieldin the Master record that point to the child records in ...
    (microsoft.public.access.forms)
  • Re: Form to search database and return found records
    ... I created a query that pulls in required data from all four tables. ... The way you keep a subform in sync with the main form ... the mainform) is by using the Link Master Fields and Link Child Fields ... identify the fieldin the Master record that point to the child records in ...
    (microsoft.public.access.forms)
  • Re: Form to search database and return found records
    ... The difficulty is in passing the search string to the ... the same form has a subform - call this ResultsSubForm - with ... the mainform) is by using the Link Master Fields and Link Child Fields ... identify the fieldin the Master record that point to the child records in ...
    (microsoft.public.access.forms)
  • Re: Form to search database and return found records
    ... I created a query that pulls in required data from all four tables. ... The way you keep a subform in sync with the main form ... the mainform) is by using the Link Master Fields and Link Child Fields ... identify the fieldin the Master record that point to the child records in ...
    (microsoft.public.access.forms)
  • Re: Deny changing value in field, based on values of multiple fields
    ... You need to count how many entries in the subform have empty dates. ... I am assuming that the subform has link master and child fields. ... In the criteria row, under DateDone type ...
    (microsoft.public.access.forms)