473,549 Members | 2,699 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Searching in detail table to find master record

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
7 4530
"john" <jo**@test.comw rote
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 LinkMasterField s 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

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

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:OaUAh.4223 $E71.3332@trndd c04...
"john" <jo**@test.comw rote
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 LinkMasterField s 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
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.comw rote in message
news:er******** **@textnews.wan adoo.nl...
>
Sorry, I should have been more clear.
See inline:

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:OaUAh.4223 $E71.3332@trndd c04...
>"john" <jo**@test.comw rote
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
synchronizatio n via the LinkMasterField s 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
See inline:

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:Y0cBh.2117 $hp4.2090@trndd c02...
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
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.comw rote in message
news:er******** **@textnews.wan adoo.nl...
See inline:

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:Y0cBh.2117 $hp4.2090@trndd c02...
>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
correspondin g 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
Thanks a lot Larry for your time. I think this will get me there.
john

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:INnBh.91$v u2.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.comw rote in message
news:er******** **@textnews.wan adoo.nl...
>See inline:

"Larry Linson" <bo*****@localh ost.notschreef in bericht
news:Y0cBh.211 7$hp4.2090@trnd dc02...
>>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
correspondi ng 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
"john" <jo**@test.comw rote
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3044
by: Mark | last post by:
I have a table about people containing 25 fields. The table contains the usual fields - first, last, address, city, state and zip. There is no primary key. These fields all have data with the exception of a few records missing zip. A person may be in one to five records in the database. If a person is in multiple records, the other fields in...
2
1553
by: Chris Belcher | last post by:
While I'm sure this is simple I just can't figure it out. Table A (assignments) is on the One side of a One to Many relationship With Table B (assignees)There are many assignees assigned the one task. I have the relationship set to Join all records in Table and only the records in Table B that Match. Currently I have a form that...
1
2386
by: bastos.sergio | last post by:
Hello everyone, I'm having a bit of a problem seeying the big picture in this and I was hoping somebody more experienced in this could help tell me if I'm proceding right. I plan to construct a master invoice table with client information and a linked details invoice table with line items of all items charged. I also plan to have a payments...
1
1639
by: krzys | last post by:
here is the following situation : I have a dataset with two tables this.daCustomers.Fill(dsData1, "Customers"); this.daOrders.Fill(dsData1, "Orders"); as You see there are tables from Northwind :) these tables have a relation
0
1022
by: HS1 | last post by:
Hello I created a master and a detail table using 2 dataGrids for Client and Jobs tables with a relationship With the detail grid, I create some textboxes to present a record of Jobs table (and also to insert new data for this table) The master and detail grids work well. However, the textboxes does not present the current record in...
33
2445
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following criteria: I have a list of about 100 numbers which correspond to the ID field and also another 40 say numbers corresponding to the numbers in the...
1
973
by: John | last post by:
Hi I have dragged a master and a detail table on the form. How can I now make the master table control the detail table in that proper detail records are displayed when a master record is selected from the Master BindingNavigator? Thanks Regards
1
1615
by: qasimali84 | last post by:
I am developing a Cadiac Segment Evaluation System . In this system Patient Information is Kept in Master Table using Reg_No as Primary Key and the information about Seventeen Different segments of Heart is kept in Detail Table using Reg_No Forign Key. I am working Standard window Application in C# . I am using DataGrid. But Data Sql Query is not...
2
2294
by: Cacho | last post by:
Hi I've two tables TableA id name TableB id
0
7715
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7469
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7808
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6040
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5087
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3498
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1935
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1057
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
757
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.