Form to search database and return found records

[klingongardener]

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.

 

[Carl Rapson]

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.

What has always worked for me is to use the filter string to actually modify
the Record Source of the subform:

Me.subform.RecordSource = "SELECT * FROM

WHERE " & filter_string

This assumes the subform initially has a Record Source of:

SELECT * FROM

Carl Rapson

 

[Klatuu]

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?

 

[klingongardener]

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

 

[Klatuu]

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.
--
Dave Hargis, Microsoft Access MVP

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

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?
--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

 

[klingongardener]

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

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.
--
Dave Hargis, Microsoft Access MVP

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

Thanks to both of you.

- Show quoted text -

 

[kansaskannan]

Dave, any further input on setting up the search mechanism? thanks.

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

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.

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
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
:
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 -

 

[Klatuu]

Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP

Dave, any further input on setting up the search mechanism? thanks.

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

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.

:
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

Thanks to both of you.

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?

:
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 -

 

[kansaskannan]

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.

Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP

Dave, any further input on setting up the search mechanism? thanks.

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
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
:
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
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
:
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 -

 

[Klatuu]

sounds like you have most of it done. What does the search string look like
when you click the search button?
--
Dave Hargis, Microsoft Access MVP

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.

Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP

Dave, any further input on setting up the search mechanism? thanks.

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).


On Jun 19, 3:57 pm, Klatuu <[email protected]> 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.

:
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

Thanks to both of you.

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?

:
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 -

 

[kansaskannan]

tblProperties.PrptyZip = "28746"

I also have:
Me.FilterOn = True
Me.Filter = strWhere

The difficulty is in passing the search string (strWhere) to the
subform.

thanks.

sounds like you have most of it done. What does the search string look like
when you click the search button?
--
Dave Hargis, Microsoft Access MVP

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.

Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP
:
Dave, any further input on setting up the search mechanism? thanks.
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
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
:
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
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
:
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 -- Hide quoted text -

- Show quoted text -

 

[Klatuu]

Problems:
The Me.Filter is filtering the main form (assuming the code is in the main
form module)
When you apply a filter, you apply it to the form's recordset, not the table.


Try it like this:

Me.SubFormControlName.Form.Filter = "trptyZip = '28746'"
Me.SubFormControlName.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

tblProperties.PrptyZip = "28746"

I also have:
Me.FilterOn = True
Me.Filter = strWhere

The difficulty is in passing the search string (strWhere) to the
subform.

thanks.

sounds like you have most of it done. What does the search string look like
when you click the search button?
--
Dave Hargis, Microsoft Access MVP

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.

Can you give me some detail on what and how you want to look up?

:
Dave, any further input on setting up the search mechanism? thanks.

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).


On Jun 19, 3:57 pm, Klatuu <[email protected]> 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.

:
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

Thanks to both of you.

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?

:
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 -- Hide quoted text -

- Show quoted text -

 

[kansaskannan]

Dave: THAT was the problem - the way I was applying the filter. with
your guidance, I have finally got it working. I also had a typo in a
field name which was further clouding the issue. Thanks a bunch;
this problem was driving me nuts, and I much appreciate your guidance.
kannan

Problems:
The Me.Filter is filtering the main form (assuming the code is in the main
form module)
When you apply a filter, you apply it to the form's recordset, not the table.

Try it like this:

Me.SubFormControlName.Form.Filter = "trptyZip = '28746'"
Me.SubFormControlName.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

tblProperties.PrptyZip = "28746"

I also have:
Me.FilterOn = True
Me.Filter = strWhere

The difficulty is in passing the search string (strWhere) to the
subform.

thanks.

sounds like you have most of it done. What does the search string look like
when you click the search button?
--
Dave Hargis, Microsoft Access MVP
:
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.
Can you give me some detail on what and how you want to look up?
--
Dave Hargis, Microsoft Access MVP
:
Dave, any further input on setting up the search mechanism? thanks.
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
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
:
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
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
:
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 -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Klatuu]

Glad I could help

"Of course I'm mad. I've always been mad."
-Roger Waters - Dark Side of the Moon
--
Dave Hargis, Microsoft Access MVP

Dave: THAT was the problem - the way I was applying the filter. with
your guidance, I have finally got it working. I also had a typo in a
field name which was further clouding the issue. Thanks a bunch;
this problem was driving me nuts, and I much appreciate your guidance.
kannan

Problems:
The Me.Filter is filtering the main form (assuming the code is in the main
form module)
When you apply a filter, you apply it to the form's recordset, not the table.

Try it like this:

Me.SubFormControlName.Form.Filter = "trptyZip = '28746'"
Me.SubFormControlName.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

tblProperties.PrptyZip = "28746"

I also have:
Me.FilterOn = True
Me.Filter = strWhere

The difficulty is in passing the search string (strWhere) to the
subform.

sounds like you have most of it done. What does the search string look like
when you click the search button?

:
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.

Can you give me some detail on what and how you want to look up?

:
Dave, any further input on setting up the search mechanism? thanks.

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).


On Jun 19, 3:57 pm, Klatuu <[email protected]> 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.

:
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

Thanks to both of you.

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?

:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -