HOW TO SHOw THE VALUES OF COMMON FIELDS IN DIFFERENT FORMS?

[sudhir]

I have two tables- T1 and T2
the fields in t1 are- idno- this is a primary key, surname,name,birthdate..

the fields in t2 are- idno- (duplicates allowed), surname,name, date of
operation..

form1 is based on t1 and form 2 is based on t2

i enter all the data in all fields in form1 and that data exists can be seen
in t1

In order that filling form2 becomes easy- it is important for me see the
data in surname, name fields in the form 2- though i donot enter that data
again on form2( what is the point in doing the same thing again again- why
have access then??)form2 becomes more human when i see the data in surname
and name fields. Is it possible to achieve this? these fields are empty in
t2( because they are not filled in form2) i feel it should be possible see
data in common fields any any forms- wheather they are parent/child form and
the tables have 1:1 or 1:many relationship or not..

help will be graetly appreciated

sb

 

[John Vinson]

I have two tables- T1 and T2
the fields in t1 are- idno- this is a primary key, surname,name,birthdate..

the fields in t2 are- idno- (duplicates allowed), surname,name, date of
operation..

Well, you should certainly NOT be storing surname and name redundantly
in t2.

form1 is based on t1 and form 2 is based on t2

i enter all the data in all fields in form1 and that data exists can be seen
in t1

In order that filling form2 becomes easy- it is important for me see the
data in surname, name fields in the form 2- though i donot enter that data
again on form2( what is the point in doing the same thing again again- why
have access then??)form2 becomes more human when i see the data in surname
and name fields.

The simplest way to do this is to remove surname and name from t2, and
use a Subform bound to t2 on the Form based on t1. Use idno as the
master/child link field. You'll be able to see the name on the
mainform, and the t2 data on the subform; the Subform link fields will
keep the two in synch, and you won't even need to do anything to find
the right record.

If for some reason this is impractical, you can use a Combo Box on the
form for t2; have its Bound Column be the IDNO but base it on a query
like

SELECT idno, [Surname] & ", " & [Name] ORDER BY Surname, [Name];

This will STORE the ID but DISPLAY the person's name.

Is it possible to achieve this? these fields are empty in
t2( because they are not filled in form2) i feel it should be possible see
data in common fields any any forms- wheather they are parent/child form and
the tables have 1:1 or 1:many relationship or not..

Again... THEY ARE NOT COMMON FIELDS. They are redundant fields. There
is nothing whatsoever in your table structure that would prevent you
from having IDNO 345 be "Joe Doakes" in t1 and "Mary Wilson" in t2;
the field [name] and [surname] are completely independent in the two
tables.

Note also that [name] is a bad choice of fieldname - everything in
Access has a Name property (fields, tables, forms); using name as the
name of a name field is just as confusing as this sentence. I'd use
Forename and Surname instead.

John W. Vinson[MVP]

 

[sudhir]

thank u John for ur advice.
sorry i did not give u all the details- here they are-

form2 is a subform of form1, name is actually fname. with parent /child
fileds likned on idno- i can see idno on f1 and f2- but not surname and other
details which i want to see. i was fascinated to haer that for eg. surname is
an completely independant field in two different tables in the same access
database!- i migrated to Access having used RBase- this was a good one to a
point- in Rbase- if u create surname a field in one table with its length and
properties- they cannot be changed in any other table in the same database- i
thought that was good. I was surprised that Access can take two definitions
of surname in two tables in the same datbase file..

can u please tell me how ur method of using a combobox based on the query is
to be used- does that mean i have to create a query with t1 and t2 and base
form2 on this query?

many thanks for ur advice

sb
--
sb

I have two tables- T1 and T2
the fields in t1 are- idno- this is a primary key, surname,name,birthdate..

the fields in t2 are- idno- (duplicates allowed), surname,name, date of
operation..

Well, you should certainly NOT be storing surname and name redundantly
in t2.

form1 is based on t1 and form 2 is based on t2

i enter all the data in all fields in form1 and that data exists can be seen
in t1

In order that filling form2 becomes easy- it is important for me see the
data in surname, name fields in the form 2- though i donot enter that data
again on form2( what is the point in doing the same thing again again- why
have access then??)form2 becomes more human when i see the data in surname
and name fields.

The simplest way to do this is to remove surname and name from t2, and
use a Subform bound to t2 on the Form based on t1. Use idno as the
master/child link field. You'll be able to see the name on the
mainform, and the t2 data on the subform; the Subform link fields will
keep the two in synch, and you won't even need to do anything to find
the right record.

If for some reason this is impractical, you can use a Combo Box on the
form for t2; have its Bound Column be the IDNO but base it on a query
like

SELECT idno, [Surname] & ", " & [Name] ORDER BY Surname, [Name];

This will STORE the ID but DISPLAY the person's name.

Is it possible to achieve this? these fields are empty in
t2( because they are not filled in form2) i feel it should be possible see
data in common fields any any forms- wheather they are parent/child form and
the tables have 1:1 or 1:many relationship or not..

Again... THEY ARE NOT COMMON FIELDS. They are redundant fields. There
is nothing whatsoever in your table structure that would prevent you
from having IDNO 345 be "Joe Doakes" in t1 and "Mary Wilson" in t2;
the field [name] and [surname] are completely independent in the two
tables.

Note also that [name] is a bad choice of fieldname - everything in
Access has a Name property (fields, tables, forms); using name as the
name of a name field is just as confusing as this sentence. I'd use
Forename and Surname instead.

John W. Vinson[MVP]

 

[John Vinson]

thank u John for ur advice.
sorry i did not give u all the details- here they are-

form2 is a subform of form1, name is actually fname. with parent /child
fileds likned on idno- i can see idno on f1 and f2- but not surname and other
details which i want to see. i was fascinated to haer that for eg. surname is
an completely independant field in two different tables in the same access
database!- i migrated to Access having used RBase- this was a good one to a
point- in Rbase- if u create surname a field in one table with its length and
properties- they cannot be changed in any other table in the same database- i
thought that was good. I was surprised that Access can take two definitions
of surname in two tables in the same datbase file..

RBase is one program. Access is a different program! It can be HARDER
to transfer from a different program, since you not only have to learn
how Access does things, but unlearn "the way things are always done".

Access is more in accord with the ANSI SQL specs than is RBase, in
that the fields in any table are specific to that table. Fieldnames
can be anything - they can be the same or different between tables,
they're still independent fields.

can u please tell me how ur method of using a combobox based on the query is
to be used- does that mean i have to create a query with t1 and t2 and base
form2 on this query?

No. Form1 (the parent form) would be based on T1; Form2 (the subform)
would be based on t2, and would be displayed in a Subform Control on
Form1. If you're clicking a button to open form2, then *it is not a
subform* - you need to use the Subform control.

If you are doing so, then I don't see any point in showing the fname
and surname fields twice (once on the mainform and once on the
subform). You can, if you wish - I just don't see why you would want
to, since they'll be identical for all the records displayed on the
subform.

If you wish to do so, you can create a Query based on t1, let's name
it qrycboName:

SELECT t1.idno, t1.Surname & ", " & t1.Fname FROM t1 ORDER BY Surname,
Fname;

You can use this query as the Rowsource property of a Combo Box. Set
the other properties to:

Control Source idno
Column Count 2
Column Widths 0;1.5
Bound Column 1

This combo will now STORE the IDno field, but DISPLAY the name in the
format "Martins, David". There is really no point in doing this on the
Subform - the idno is filled in automatically by the Master/Child Link
Field properties of the subform control, and you shouldn't be editing
it; and you don't need to store the name fields in t2 in any case.
However, the combo could be used on any other form where you want to
enter an idno value but see the name.

To see the name and the t2 data on a Report, simply base the Report on
a Query joining t1 to t2 by idno. Pull the name fields from t1.fname
and t2.fname; pull the t2 data from the fields in t2; use the report's
Sorting and Grouping property to put all of the information for one
person together.

John W. Vinson[MVP]