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
7  4521 
"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
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
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
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
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
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
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Cacho |
last post by:
Hi
I've two tables
TableA
id
name
TableB
id
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
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...
| |
