Can this be done?

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I have linked two tables to sql server database
the key field on table1 is "tab1key1" + "tab1key2"
+ "tab1key3"

key field on table2 is "tab2key1" + "tab2key2"
+ "tab2key3"

these "tab1key2" from table1 maps to "tab2key1" in table2

When I bring up a record in form1, Is it possible
to link it to records in form2 with table2?

For eg. Table 1 values are = A+AA01+999
B+AA01+298
C+AA01+999

Table 2 values are = AA01+XYZ+001
= AA01+XYZ+002
= AA01+ABC+003
= BA01+XYA+001

etc...

If I bring up a form for value = say A+AA01+999 from
table1
I should be able to click on a button and bring up
another form with data from table2 that will show
AA01+XYZ+001
AA01+XYZ+002
AA01+ABC+003

Thank you,
Linda
 
Personally I would put table2 on a sub-form, the Parent/Child properties will
then do what you want, and you can always put it on a tab control if you are
short of space on the form.

If you do want to open another form, use the where condition clause of OpenForm
to restrict the records :-

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][,
windowmode][, openargs]
 
John,

Thanks for the reply!
I already tried creating the Parent/Child relationship,
it doesn't work in this context as the 2nd table
doesn't contain all parts of table1 key. That's the reason
I explicitily put the key there. Table2 contains
the 2nd part of the key + additional key elements(the
additional key elements should be ok), but the
other portions of key1 missing doesn't allow me
to create parent/child relationship.

Unless I am doing something wrong here.

Could you please let me know if you think this way
also it should work.

Thanks for your time,
-Linda
-----Original Message-----
Personally I would put table2 on a sub-form, the Parent/Child properties will
then do what you want, and you can always put it on a tab control if you are
short of space on the form.

If you do want to open another form, use the where condition clause of OpenForm
to restrict the records :-

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][,
windowmode][, openargs]

--
HTH
John

I have linked two tables to sql server database
the key field on table1 is "tab1key1" + "tab1key2"
+ "tab1key3"
key field on table2 is "tab2key1" + "tab2key2"
+ "tab2key3"
these "tab1key2" from table1 maps to "tab2key1" in table2
When I bring up a record in form1, Is it possible
to link it to records in form2 with table2?
For eg. Table 1 values are = A+AA01+999
B+AA01+298
C+AA01+999
Table 2 values are = AA01+XYZ+001
= AA01+XYZ+002
= AA01+ABC+003
= BA01+XYA+001

If I bring up a form for value = say A+AA01+999 from
table1
I should be able to click on a button and bring up
another form with data from table2 that will show
AA01+XYZ+001
AA01+XYZ+002
AA01+ABC+003
Click to expand...


.
Click to expand...
 
I think that you are confusing the Primary Key with the Foreign Key. The three
fields in table1 make it's Primary Key, but only tab1key2 is the Foreign Key
for table2 (incidentally a Foreign Key is not necessarily part of the Primary
key at all). Also, you would normally have other fields in the Primary key of
the second table, otherwise you could not have a one-to-many relationship.

You need to put tab1key2 as the Parent value and tab2key1 as the Child value to
define the relationship between the forms. If you create a relationship
between the tables Access will fill it in for you, otherwise you have to do it
yourself.

--
HTH
John

Linda said:
John,

Thanks for the reply!
I already tried creating the Parent/Child relationship,
it doesn't work in this context as the 2nd table
doesn't contain all parts of table1 key. That's the reason
I explicitily put the key there. Table2 contains
the 2nd part of the key + additional key elements(the
additional key elements should be ok), but the
other portions of key1 missing doesn't allow me
to create parent/child relationship.

Unless I am doing something wrong here.

Could you please let me know if you think this way
also it should work.

Thanks for your time,
-Linda
-----Original Message-----
Personally I would put table2 on a sub-form, the Parent/Child properties will
then do what you want, and you can always put it on a tab control if you are
short of space on the form.

If you do want to open another form, use the where condition clause of OpenForm
to restrict the records :-

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][,
windowmode][, openargs]

--
HTH
John

I have linked two tables to sql server database
the key field on table1 is "tab1key1" + "tab1key2"
+ "tab1key3"
key field on table2 is "tab2key1" + "tab2key2"
+ "tab2key3"
these "tab1key2" from table1 maps to "tab2key1" in table2
When I bring up a record in form1, Is it possible
to link it to records in form2 with table2?
For eg. Table 1 values are = A+AA01+999
B+AA01+298
C+AA01+999
Table 2 values are = AA01+XYZ+001
= AA01+XYZ+002
= AA01+ABC+003
= BA01+XYA+001

If I bring up a form for value = say A+AA01+999 from
table1
I should be able to click on a button and bring up
another form with data from table2 that will show
AA01+XYZ+001
AA01+XYZ+002
AA01+ABC+003
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top