By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Searching in detail table to find master record

P: n/a
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent records. Should I design a new
form for this or can I somehow make this work in the same form.
Thanks in advance,
john
Feb 13 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"john" <jo**@test.comwrote
In my form I have a master table and a details
table linked 1xM. I can search through the whole
parent table but I also like to be able to search
through the child table fields to find parent records.
Should I design a new form for this or can I
somehow make this work in the same form.
First, would you clarify _how_ you have in your Form a master Table and a
details Table linked 1xM?

I am guessing that the Tables are set up as follows: you have a Field (or
Fields) in the master Table to uniquely identify each master Record, and
that you have copies of that (or those) Fields in the related detail Record
as a Foreign Key to the master Record. If my guess is incorrect, please
explain the Tables and the key(s) that allow you to identify the master
Record from a detail Record.

(1) That could mean you have a Query, joining the master Table and the
details Table on the common Field, as the RecordSource of the Form, or

(2) it could mean you have a main Form with the master Table as its
RecordSource, and in that main Form, you have a Subform Control in which you
have embedded a Form with the details Table as its RecordSource, with
synchronization via the LinkMasterFields and LinkChildFields of the Subform
Control.

Please explain which, or if some other arrangement, describe in detail.

And, how are you "searching"? Are you

(1) entering a search value and using that to locate a record in code
behind the Form, then positioning the Form to that Record, or

(2) do you mean that you just scroll the Form until you reach the Record
you want?

With this information, perhaps someone can offer a useful suggestion.

--
Larry Linson
Microsoft Access MVP
Feb 15 '07 #2

P: n/a

Sorry, I should have been more clear.
See inline:

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:OaUAh.4223$E71.3332@trnddc04...
"john" <jo**@test.comwrote
In my form I have a master table and a details
table linked 1xM. I can search through the whole
parent table but I also like to be able to search
through the child table fields to find parent records.
Should I design a new form for this or can I
somehow make this work in the same form.

First, would you clarify _how_ you have in your Form a master Table and a
details Table linked 1xM?

I am guessing that the Tables are set up as follows: you have a Field (or
Fields) in the master Table to uniquely identify each master Record, and
that you have copies of that (or those) Fields in the related detail
Record as a Foreign Key to the master Record. If my guess is incorrect,
please explain the Tables and the key(s) that allow you to identify the
master Record from a detail Record.
Your guess is correct. Master had autonumber ID field and child has numeric
ID field and those two are linked. Apart from the ID field the child has
another field, a text field, called description. I have a primary index on
the two child fields to make sure a description is not linked more than once
to a master record.
(1) That could mean you have a Query, joining the master Table and the
details Table on the common Field, as the RecordSource of the Form, or

(2) it could mean you have a main Form with the master Table as its
RecordSource, and in that main Form, you have a Subform Control in which
you have embedded a Form with the details Table as its RecordSource, with
synchronization via the LinkMasterFields and LinkChildFields of the
Subform Control.
Yes, this is the case.
Please explain which, or if some other arrangement, describe in detail.

And, how are you "searching"? Are you

(1) entering a search value and using that to locate a record in code
behind the Form, then positioning the Form to that Record, or

(2) do you mean that you just scroll the Form until you reach the Record
you want?
Neither, I meant the standard Ctrl-F search option. But my guess is I have
to do something like your option (1) to be able to locate master records by
searching in the child records.

For instance: It would be nice if I could move to the description field and
press Ctrl-F, search for a value and then the first master record is located
that has the search value in its child table. Though this would mislead the
user since it's not standard access behavior. I'm curious what's best
practice in this case.
With this information, perhaps someone can offer a useful suggestion.

--
Larry Linson
Microsoft Access MVP
Thanks,
john
Feb 15 '07 #3

P: n/a
If I understand, you have a main Table that is related one-to-many with a
table containing descriptions. A description may not be repeated for the
same Record in the mainTable, ***but the same description may occur for
different Records in the mainTable***. You want to display all the
Description Records, choose one and have the corresponding mainTable Record
displayed.

*** unless you have another index, that you did not
mention, just on the description text field, with
no duplicates allowed -- that would prevent the
description from applying to more than one
mainTable Records.

That won't work, if I understand correctly... because multiple mainTable
Records may be linked to separate (related) Records in the Child table that
contain the same description. Thus, if you find three records with the same
description, which one would you display in the mainForm?

Can you clarify whether the same description can occur, related to different
mainTable Records, in different Records in the Child table? If not, then you
need to add a no-duplicates index on the description Field.

And, you are correct -- that's not the "Access Way". The Access Way is that
the Subform Control is synchronized to what's displayed in the main Form,
not the reverse as you describe. If you follow the Access Way when using
Access, it works nicely and smoothly, because that's what it's designed to
do.

If you are "bound and determined" to use Access to do something else, you
can often accomplish what you want, but at the expense of some time and
effort to make it behave "Your Way".

If you'll clarify whether the same description can be used in multiple
Records in the Child Table, each of those separate Records related to a
different record in the MainTable, then maybe we will be a little farther
along the road to helping you.

Larry Linson
Microsoft Access MVP

"john" <jo**@test.comwrote in message
news:er**********@textnews.wanadoo.nl...
>
Sorry, I should have been more clear.
See inline:

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:OaUAh.4223$E71.3332@trnddc04...
>"john" <jo**@test.comwrote
In my form I have a master table and a details
table linked 1xM. I can search through the whole
parent table but I also like to be able to search
through the child table fields to find parent records.
Should I design a new form for this or can I
somehow make this work in the same form.

First, would you clarify _how_ you have in your Form a master Table and a
details Table linked 1xM?

I am guessing that the Tables are set up as follows: you have a Field (or
Fields) in the master Table to uniquely identify each master Record, and
that you have copies of that (or those) Fields in the related detail
Record as a Foreign Key to the master Record. If my guess is incorrect,
please explain the Tables and the key(s) that allow you to identify the
master Record from a detail Record.

Your guess is correct. Master had autonumber ID field and child has
numeric ID field and those two are linked. Apart from the ID field the
child has another field, a text field, called description. I have a
primary index on the two child fields to make sure a description is not
linked more than once to a master record.
> (1) That could mean you have a Query, joining the master Table and the
details Table on the common Field, as the RecordSource of the Form, or

(2) it could mean you have a main Form with the master Table as its
RecordSource, and in that main Form, you have a Subform Control in which
you have embedded a Form with the details Table as its RecordSource, with
synchronization via the LinkMasterFields and LinkChildFields of the
Subform Control.

Yes, this is the case.
>Please explain which, or if some other arrangement, describe in detail.

And, how are you "searching"? Are you

(1) entering a search value and using that to locate a record in code
behind the Form, then positioning the Form to that Record, or

(2) do you mean that you just scroll the Form until you reach the Record
you want?

Neither, I meant the standard Ctrl-F search option. But my guess is I have
to do something like your option (1) to be able to locate master records
by searching in the child records.

For instance: It would be nice if I could move to the description field
and press Ctrl-F, search for a value and then the first master record is
located that has the search value in its child table. Though this would
mislead the user since it's not standard access behavior. I'm curious
what's best practice in this case.
>With this information, perhaps someone can offer a useful suggestion.

--
Larry Linson
Microsoft Access MVP

Thanks,
john

Feb 16 '07 #4

P: n/a
See inline:

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:Y0cBh.2117$hp4.2090@trnddc02...
If I understand, you have a main Table that is related one-to-many with a
table containing descriptions. A description may not be repeated for the
same Record in the mainTable, ***but the same description may occur for
different Records in the mainTable***.
Yes.
You want to display all the Description Records, choose one and have the
corresponding mainTable Record displayed.
That's not the idea. Let's say in my current form 'Car salesman' is the
master table and 'Car brand' is the child table and a salesman can sell more
than one kind of car brands:

Master Child
Field: Name Salesman Car brand
Johnson
Audi
Volkswagen
Smith
Mercedes
Volkswagen

For all of the master fields I can easily use the Ctrl-F search option but
of course not for the child fields. I was wondering if there's a way to for
instance set my cursor in a random child record in the field Car Brand and
perform a search on that field so that the first Salesman is found that
contains that value in its child. For instance, in the above example, I
search for Mercedes, and the master record of Smith will be located. Search
for Volskwagen, Johnson is located. Performing a Search Next after that will
result in moving to the Smith record.

I made this app for a user and the first question was, how can I search on
the Car brand. My reaction was, you can't do that in this form, I'll have to
make another form or query.
*** unless you have another index, that you did not
mention, just on the description text field, with
no duplicates allowed -- that would prevent the
description from applying to more than one
mainTable Records.
No, that's not the case.
That won't work, if I understand correctly... because multiple mainTable
Records may be linked to separate (related) Records in the Child table
that contain the same description. Thus, if you find three records with
the same description, which one would you display in the mainForm?
The first one and by locating next, the next one would show.
Can you clarify whether the same description can occur, related to
different mainTable Records, in different Records in the Child table?
Yes. The particular Johnson record that has two Mercedes records is not
possible.
And, you are correct -- that's not the "Access Way". The Access Way is
that the Subform Control is synchronized to what's displayed in the main
Form, not the reverse as you describe. If you follow the Access Way when
using Access, it works nicely and smoothly, because that's what it's
designed to do.

If you are "bound and determined" to use Access to do something else, you
can often accomplish what you want, but at the expense of some time and
effort to make it behave "Your Way".

If you'll clarify whether the same description can be used in multiple
Records in the Child Table, each of those separate Records related to a
different record in the MainTable, then maybe we will be a little farther
along the road to helping you.
I'm starting to get the feeling that I should just make another form and use
another table, the Car Brand lookup table, (with only unique car brand
values) as the master and that table linked to the Salesman Table on the
field Car Brand.
Larry Linson
Microsoft Access MVP
john
Feb 16 '07 #5

P: n/a
Unfortunately, you have to jump through some hoops to show subforms in a
continuous forms view form, as I'd guess the illustration you include would
imply.

I think, with some VBA programming, what you want could be shown using the
TreeView (see the information on using TreeView and ListView with Access at
MVP Jeff Conrad's site,
http://home.bendbroadband.com/conrad...treeview.html).

Or, I would guess you could use a combination of Forms and Subforms to do
something similar starting with a ComboBox whose RowSource is a unique
values Query to pick the various "brands". Then, in the AfterUpdate, you'd
use the value returned to create the WHERE clause of a Query to use as
Record Source to show Sales Personnel in the Form embedded in one Subform
Control, and use the first of the Sales People who sell that brand to create
the Query to use as RecordSource for a second Subform showing the list of
brands. Then, as the user clicks on a particular SalesPerson, use the
OnCurrent event to reset the RecordSource of the Form in the Subform showing
the list of brands. (CAVEAT: this paragraph is worse than untested "air
code"; it's untested, off-the-top-of-the-head "air design.")

Larry Linson
Microsoft Access MVP

"john" <jo**@test.comwrote in message
news:er**********@textnews.wanadoo.nl...
See inline:

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:Y0cBh.2117$hp4.2090@trnddc02...
>If I understand, you have a main Table that is related one-to-many with a
table containing descriptions. A description may not be repeated for the
same Record in the mainTable, ***but the same description may occur for
different Records in the mainTable***.

Yes.
>You want to display all the Description Records, choose one and have the
corresponding mainTable Record displayed.

That's not the idea. Let's say in my current form 'Car salesman' is the
master table and 'Car brand' is the child table and a salesman can sell
more than one kind of car brands:

Master Child
Field: Name Salesman Car brand
Johnson
Audi
Volkswagen
Smith
Mercedes
Volkswagen

For all of the master fields I can easily use the Ctrl-F search option but
of course not for the child fields. I was wondering if there's a way to
for instance set my cursor in a random child record in the field Car Brand
and perform a search on that field so that the first Salesman is found
that contains that value in its child. For instance, in the above example,
I search for Mercedes, and the master record of Smith will be located.
Search for Volskwagen, Johnson is located. Performing a Search Next after
that will result in moving to the Smith record.

I made this app for a user and the first question was, how can I search on
the Car brand. My reaction was, you can't do that in this form, I'll have
to make another form or query.
>*** unless you have another index, that you did not
mention, just on the description text field, with
no duplicates allowed -- that would prevent the
description from applying to more than one
mainTable Records.

No, that's not the case.
>That won't work, if I understand correctly... because multiple mainTable
Records may be linked to separate (related) Records in the Child table
that contain the same description. Thus, if you find three records with
the same description, which one would you display in the mainForm?

The first one and by locating next, the next one would show.
>Can you clarify whether the same description can occur, related to
different mainTable Records, in different Records in the Child table?

Yes. The particular Johnson record that has two Mercedes records is not
possible.
>And, you are correct -- that's not the "Access Way". The Access Way is
that the Subform Control is synchronized to what's displayed in the main
Form, not the reverse as you describe. If you follow the Access Way when
using Access, it works nicely and smoothly, because that's what it's
designed to do.

If you are "bound and determined" to use Access to do something else, you
can often accomplish what you want, but at the expense of some time and
effort to make it behave "Your Way".

If you'll clarify whether the same description can be used in multiple
Records in the Child Table, each of those separate Records related to a
different record in the MainTable, then maybe we will be a little farther
along the road to helping you.

I'm starting to get the feeling that I should just make another form and
use another table, the Car Brand lookup table, (with only unique car brand
values) as the master and that table linked to the Salesman Table on the
field Car Brand.
> Larry Linson
Microsoft Access MVP

john

Feb 16 '07 #6

P: n/a
Thanks a lot Larry for your time. I think this will get me there.
john

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:INnBh.91$vu2.14@trnddc04...
Unfortunately, you have to jump through some hoops to show subforms in a
continuous forms view form, as I'd guess the illustration you include
would imply.

I think, with some VBA programming, what you want could be shown using the
TreeView (see the information on using TreeView and ListView with Access
at MVP Jeff Conrad's site,
http://home.bendbroadband.com/conrad...treeview.html).

Or, I would guess you could use a combination of Forms and Subforms to do
something similar starting with a ComboBox whose RowSource is a unique
values Query to pick the various "brands". Then, in the AfterUpdate, you'd
use the value returned to create the WHERE clause of a Query to use as
Record Source to show Sales Personnel in the Form embedded in one Subform
Control, and use the first of the Sales People who sell that brand to
create the Query to use as RecordSource for a second Subform showing the
list of brands. Then, as the user clicks on a particular SalesPerson, use
the OnCurrent event to reset the RecordSource of the Form in the Subform
showing the list of brands. (CAVEAT: this paragraph is worse than untested
"air code"; it's untested, off-the-top-of-the-head "air design.")

Larry Linson
Microsoft Access MVP

"john" <jo**@test.comwrote in message
news:er**********@textnews.wanadoo.nl...
>See inline:

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:Y0cBh.2117$hp4.2090@trnddc02...
>>If I understand, you have a main Table that is related one-to-many with
a table containing descriptions. A description may not be repeated for
the same Record in the mainTable, ***but the same description may occur
for different Records in the mainTable***.

Yes.
>>You want to display all the Description Records, choose one and have the
corresponding mainTable Record displayed.

That's not the idea. Let's say in my current form 'Car salesman' is the
master table and 'Car brand' is the child table and a salesman can sell
more than one kind of car brands:

Master Child
Field: Name Salesman Car brand
Johnson
Audi
Volkswagen
Smith
Mercedes
Volkswagen

For all of the master fields I can easily use the Ctrl-F search option
but of course not for the child fields. I was wondering if there's a way
to for instance set my cursor in a random child record in the field Car
Brand and perform a search on that field so that the first Salesman is
found that contains that value in its child. For instance, in the above
example, I search for Mercedes, and the master record of Smith will be
located. Search for Volskwagen, Johnson is located. Performing a Search
Next after that will result in moving to the Smith record.

I made this app for a user and the first question was, how can I search
on the Car brand. My reaction was, you can't do that in this form, I'll
have to make another form or query.
>>*** unless you have another index, that you did not
mention, just on the description text field, with
no duplicates allowed -- that would prevent the
description from applying to more than one
mainTable Records.

No, that's not the case.
>>That won't work, if I understand correctly... because multiple mainTable
Records may be linked to separate (related) Records in the Child table
that contain the same description. Thus, if you find three records with
the same description, which one would you display in the mainForm?

The first one and by locating next, the next one would show.
>>Can you clarify whether the same description can occur, related to
different mainTable Records, in different Records in the Child table?

Yes. The particular Johnson record that has two Mercedes records is not
possible.
>>And, you are correct -- that's not the "Access Way". The Access Way is
that the Subform Control is synchronized to what's displayed in the main
Form, not the reverse as you describe. If you follow the Access Way when
using Access, it works nicely and smoothly, because that's what it's
designed to do.

If you are "bound and determined" to use Access to do something else,
you can often accomplish what you want, but at the expense of some time
and effort to make it behave "Your Way".

If you'll clarify whether the same description can be used in multiple
Records in the Child Table, each of those separate Records related to a
different record in the MainTable, then maybe we will be a little
farther along the road to helping you.

I'm starting to get the feeling that I should just make another form and
use another table, the Car Brand lookup table, (with only unique car
brand values) as the master and that table linked to the Salesman Table
on the field Car Brand.
>> Larry Linson
Microsoft Access MVP

john


Feb 17 '07 #7

P: n/a
"john" <jo**@test.comwrote
Thanks a lot Larry for your time. I think
this will get me there.
You're welcome, John.

If it works, post back and let us know what approach you used, and how well
it worked. It's always good to know.

Larry Linson
Microsoft Access MVP
Feb 19 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.