Sub form question

[Abay]

Hello .. I have a Master form with two detail forms .. both linked by a
"name" field as the index.. I have a data entry form with the combination
of the Master plus detail form 1. I am trying to link to the second detail
form which will all entry of a currency rate, which is a field on the detail
data set. Sub form 2 is opened when the relevant field in sub form 1 is
clicked once.

The second form appears fine, but the linked "name" is not the one on the
current master .. the name shown on the second detail form is the first name
in the master table.

How can I pass the current "name" to the second form?

Apologies if this is not clear enough.

Any help would be most appreciated.

Abay

 

[Arvin Meyer MVP]

If the first subform is correctly connected to the main form on the Primary
Key from the main form connected to the Foreign Key on Subform1, then use
the Primary Key of the underlying table of Subform 1 to connect to the
Foreign Key on Subform2. Because of the 2 connections, there should be no
problem with subform2 relating to the main form.

It is possible that you are linking on an index other than the
Primary/Foreign Keys. You need to change that.

 

[Abay]

Many thanks for your reply Arvin .. as far as I know all is set up ok ..
this is the code that I had working in Access 2003. I work in Access 2003
at home, and in Access 2007 at the office ..

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[Cname]" ... note cname is the primary key ...
stDocName = "Fclient_and_sub1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above doesn't work in 2007 as far as I can see ...

Abay

 

[Arvin Meyer MVP]

That is the code that opens the form, but it has nothing at all to do with
the subforms. stLinkCriteria is a Microsoft variable name which may be
misleading.

To find the relational links in your database, look in the relationships
window. To find them in your form/subform, go into design view and open the
property sheet. Click on subform1 and check the LinkMaster and Link Child
properties. Do the same for subform2 the difference there being that you
need to define the Master link as coming from the subform1. I find that the
easiest way to do that is to set a textbox on the main form = to the value
of the Primary Key of subform1 (NOT the PK of the main form).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Many thanks for your reply Arvin .. as far as I know all is set up ok ..
this is the code that I had working in Access 2003. I work in Access
2003 at home, and in Access 2007 at the office ..

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[Cname]" ... note cname is the primary key ...
stDocName = "Fclient_and_sub1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above doesn't work in 2007 as far as I can see ...

Abay

If the first subform is correctly connected to the main form on the
Primary Key from the main form connected to the Foreign Key on Subform1,
then use the Primary Key of the underlying table of Subform 1 to connect
to the Foreign Key on Subform2. Because of the 2 connections, there
should be no problem with subform2 relating to the main form.

It is possible that you are linking on an index other than the
Primary/Foreign Keys. You need to change that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Abay]

Many thanks again for your response .. your advise is most valuable .. I
will do as you suggest and will get back, hopefully with success later .. I
have to do some errands now ...

Abay

That is the code that opens the form, but it has nothing at all to do with
the subforms. stLinkCriteria is a Microsoft variable name which may be
misleading.

To find the relational links in your database, look in the relationships
window. To find them in your form/subform, go into design view and open
the property sheet. Click on subform1 and check the LinkMaster and Link
Child properties. Do the same for subform2 the difference there being that
you need to define the Master link as coming from the subform1. I find
that the easiest way to do that is to set a textbox on the main form = to
the value of the Primary Key of subform1 (NOT the PK of the main form).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Many thanks for your reply Arvin .. as far as I know all is set up ok ..
this is the code that I had working in Access 2003. I work in Access
2003 at home, and in Access 2007 at the office ..

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[Cname]" ... note cname is the primary key ...
stDocName = "Fclient_and_sub1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above doesn't work in 2007 as far as I can see ...

Abay

If the first subform is correctly connected to the main form on the
Primary Key from the main form connected to the Foreign Key on Subform1,
then use the Primary Key of the underlying table of Subform 1 to connect
to the Foreign Key on Subform2. Because of the 2 connections, there
should be no problem with subform2 relating to the main form.

It is possible that you are linking on an index other than the
Primary/Foreign Keys. You need to change that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hello .. I have a Master form with two detail forms .. both linked by a
"name" field as the index.. I have a data entry form with the
combination of the Master plus detail form 1. I am trying to link to
the second detail form which will all entry of a currency rate, which
is a field on the detail data set. Sub form 2 is opened when the
relevant field in sub form 1 is clicked once.

The second form appears fine, but the linked "name" is not the one on
the current master .. the name shown on the second detail form is the
first name in the master table.

How can I pass the current "name" to the second form?

Apologies if this is not clear enough.

Any help would be most appreciated.

Abay

 

[Abay]

Hello again Arvin ... I finally got it to work thanks to your advise .. once
again many many thanks.

Abay

That is the code that opens the form, but it has nothing at all to do with
the subforms. stLinkCriteria is a Microsoft variable name which may be
misleading.

To find the relational links in your database, look in the relationships
window. To find them in your form/subform, go into design view and open
the property sheet. Click on subform1 and check the LinkMaster and Link
Child properties. Do the same for subform2 the difference there being that
you need to define the Master link as coming from the subform1. I find
that the easiest way to do that is to set a textbox on the main form = to
the value of the Primary Key of subform1 (NOT the PK of the main form).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Many thanks for your reply Arvin .. as far as I know all is set up ok ..
this is the code that I had working in Access 2003. I work in Access
2003 at home, and in Access 2007 at the office ..

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[Cname]" ... note cname is the primary key ...
stDocName = "Fclient_and_sub1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above doesn't work in 2007 as far as I can see ...

Abay

If the first subform is correctly connected to the main form on the
Primary Key from the main form connected to the Foreign Key on Subform1,
then use the Primary Key of the underlying table of Subform 1 to connect
to the Foreign Key on Subform2. Because of the 2 connections, there
should be no problem with subform2 relating to the main form.

It is possible that you are linking on an index other than the
Primary/Foreign Keys. You need to change that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hello .. I have a Master form with two detail forms .. both linked by a
"name" field as the index.. I have a data entry form with the
combination of the Master plus detail form 1. I am trying to link to
the second detail form which will all entry of a currency rate, which
is a field on the detail data set. Sub form 2 is opened when the
relevant field in sub form 1 is clicked once.

The second form appears fine, but the linked "name" is not the one on
the current master .. the name shown on the second detail form is the
first name in the master table.

How can I pass the current "name" to the second form?

Apologies if this is not clear enough.

Any help would be most appreciated.

Abay