A subform Link Child Fields, Link Master fields problem (newbiequestion)

[raylopez99]

THis is a very basic question, since I'm learning Access without a
book, so don't read too much into it, and from my description I trust
you understand where I'm going. This post is a repost of another
thread that got rather long so I broke it into a new post.

As you can see below, the issue is how to bring into your child
subform a field that is not a migrated primary key (that is, is not a
foreign key) from the parent form, so that this field can be bound to
control (e.g. a text box) in the child subform, mainly so that the
user of the subform has a visual clue (ie the field will be read
only). There must be a way, as I'm sure this is a frequent problem.
My workaround below is not elegant and I trust there's a better way to
do this.

Thanks

RL


Hello Tina--Everything worked fine, exactly as you planned it, thanks
again, it's perfect with one small caveat (I will repost this question
in microsoft.public.­access.formscoding in case you or anybody else
misses it here): this is an Access database programming question (I
think), and it's very basic and simple: in the final two tables,
"tblAccountStocks" and "tblTransactions", linked by AcctStockID, I
want to add a field (the stock symbol) from the parent table
tblAccountStocks form, so that it appears (i.e., is read only) in the
child subform (which has data control record source tblTransactions of
course). Mainly so the user of the form has a visual clue, not to
populate any table (i.e., the field is read only). But in the drop
down List box data source: Properties | Data | Control Source these
parent fields don't show up (they never do--that's the heart of the
problem, and I'm wondering if there's something I'm missing). Only
the migrated primary key (i.e. the foreign key) which in your example
was "AcctStockID (fk)" shows up, as well as the other fields of the
subform table of course. I want to add to these fields a "stock
symbol" (StockSymbol) field from the parent table, since it's less
confusing to the user using the subform. Here's what I did, and it
works, but I'm wondering if there's a more elegant solution: I simply
added another primary key, "StockSymbol (pk)" in the parent form
(tblAccountStocks), and so now there are two primary keys (a compound
key), then I migrated this newly added key (i.e. made it a foreign
key) for the child subform table "tblTransactions". Thus the form and
subform are now linked by two keys rather than one: AccountStockID;
and StockSymbol; as can be seen when you click on the subform
properties under the heading "Link Childfields", "Link Master
fields". This workaround worked fine, but it seems this workaround
violates database design a bit, and I'm wondering if I can somehow
directly show a field from the parent form in the child subform
without going through this tedious workaround (preferably without
touching any Visual Basic code or [procedures], but I can deal with VB
if I have to)

Thanks!

RL

 

[Marshall Barton]

THis is a very basic question, since I'm learning Access without a
book, so don't read too much into it, and from my description I trust
you understand where I'm going. This post is a repost of another
thread that got rather long so I broke it into a new post.

As you can see below, the issue is how to bring into your child
subform a field that is not a migrated primary key (that is, is not a
foreign key) from the parent form, so that this field can be bound to
control (e.g. a text box) in the child subform, mainly so that the
user of the subform has a visual clue (ie the field will be read
only). There must be a way, as I'm sure this is a frequent problem.
My workaround below is not elegant and I trust there's a better way to
do this.



Hello Tina--Everything worked fine, exactly as you planned it, thanks
again, it's perfect with one small caveat (I will repost this question
in microsoft.public.­access.formscoding in case you or anybody else
misses it here): this is an Access database programming question (I
think), and it's very basic and simple: in the final two tables,
"tblAccountStocks" and "tblTransactions", linked by AcctStockID, I
want to add a field (the stock symbol) from the parent table
tblAccountStocks form, so that it appears (i.e., is read only) in the
child subform (which has data control record source tblTransactions of
course). Mainly so the user of the form has a visual clue, not to
populate any table (i.e., the field is read only). But in the drop
down List box data source: Properties | Data | Control Source these
parent fields don't show up (they never do--that's the heart of the
problem, and I'm wondering if there's something I'm missing). Only
the migrated primary key (i.e. the foreign key) which in your example
was "AcctStockID (fk)" shows up, as well as the other fields of the
subform table of course. I want to add to these fields a "stock
symbol" (StockSymbol) field from the parent table, since it's less
confusing to the user using the subform. Here's what I did, and it
works, but I'm wondering if there's a more elegant solution: I simply
added another primary key, "StockSymbol (pk)" in the parent form
(tblAccountStocks), and so now there are two primary keys (a compound
key), then I migrated this newly added key (i.e. made it a foreign
key) for the child subform table "tblTransactions". Thus the form and
subform are now linked by two keys rather than one: AccountStockID;
and StockSymbol; as can be seen when you click on the subform
properties under the heading "Link Childfields", "Link Master
fields". This workaround worked fine, but it seems this workaround
violates database design a bit, and I'm wondering if I can somehow
directly show a field from the parent form in the child subform
without going through this tedious workaround (preferably without
touching any Visual Basic code or [procedures], but I can deal with VB
if I have to)

I think all you need is to set the subform text box's
control source to an expression like:

=Parent.StockSymbol

 

[raylopez99]

I think all you need is to set the subform text box's
control source to an expression like:

        =Parent.StockSymbol

Are you sure? I quickly tried this and got "#Name?" as well as a
dialog box asking for input when I loaded the subform. I also got a
warning icon ("!") "This control has an invalid control source" and
clicking on the various options led me back to the start. BTW I am
using for parent.StockSymbol the name of the form, called
AccountStocks, not parent, if that matters, so it's
AccountStocks.StockSymbol. Also I tried it with and without the "="
sign and still got the same error.

RL

 

[raylopez99]

I found a hint--but just a hint--in a textbook on how to fix this
problem. Apparently, for at least Access 95 (the book is old) the
Form Design interface does not allow you to create a form from more
than one table (unlike the Form Wizard, which does). To trick the
interface, you must set up a query, and base the form on the query.

Anybody actually done this for Access 2003? Right now my workaround
is fine, but for future reference I'd like to know.

RL

 

[Marshall Barton]

Parent is a property that returns the form object of the
subform's parent form. You were not supposed to replace it.
Assuming the main form text box (or field) is named
StockSymbol, the control source should simple be:
=PARENT.StockSymbol

If you got prompts, then I would have to guess that you used
that in a query.

If you did use that in a subform text box's control source,
then you should get #Name if you forgot the = sign or if
StockSymbol is not really the name of the main form text box
or field.

If you have some kind of aversion to using the Parent
property, you could use the full reference:
=Forms!nameof mainform.StockSymbol

 

[tina]

i wouldn't change the tables structure to accommodate interface needs; you
can almost always find a way to put what you need in the interface while
maintaining the integrity of tables/relationships design.

in this case, i'd use a calculated field as Marsh suggested.

hth


THis is a very basic question, since I'm learning Access without a
book, so don't read too much into it, and from my description I trust
you understand where I'm going. This post is a repost of another
thread that got rather long so I broke it into a new post.

As you can see below, the issue is how to bring into your child
subform a field that is not a migrated primary key (that is, is not a
foreign key) from the parent form, so that this field can be bound to
control (e.g. a text box) in the child subform, mainly so that the
user of the subform has a visual clue (ie the field will be read
only). There must be a way, as I'm sure this is a frequent problem.
My workaround below is not elegant and I trust there's a better way to
do this.

Thanks

RL


Hello Tina--Everything worked fine, exactly as you planned it, thanks
again, it's perfect with one small caveat (I will repost this question
in microsoft.public.­access.formscoding in case you or anybody else
misses it here): this is an Access database programming question (I
think), and it's very basic and simple: in the final two tables,
"tblAccountStocks" and "tblTransactions", linked by AcctStockID, I
want to add a field (the stock symbol) from the parent table
tblAccountStocks form, so that it appears (i.e., is read only) in the
child subform (which has data control record source tblTransactions of
course). Mainly so the user of the form has a visual clue, not to
populate any table (i.e., the field is read only). But in the drop
down List box data source: Properties | Data | Control Source these
parent fields don't show up (they never do--that's the heart of the
problem, and I'm wondering if there's something I'm missing). Only
the migrated primary key (i.e. the foreign key) which in your example
was "AcctStockID (fk)" shows up, as well as the other fields of the
subform table of course. I want to add to these fields a "stock
symbol" (StockSymbol) field from the parent table, since it's less
confusing to the user using the subform. Here's what I did, and it
works, but I'm wondering if there's a more elegant solution: I simply
added another primary key, "StockSymbol (pk)" in the parent form
(tblAccountStocks), and so now there are two primary keys (a compound
key), then I migrated this newly added key (i.e. made it a foreign
key) for the child subform table "tblTransactions". Thus the form and
subform are now linked by two keys rather than one: AccountStockID;
and StockSymbol; as can be seen when you click on the subform
properties under the heading "Link Childfields", "Link Master
fields". This workaround worked fine, but it seems this workaround
violates database design a bit, and I'm wondering if I can somehow
directly show a field from the parent form in the child subform
without going through this tedious workaround (preferably without
touching any Visual Basic code or [procedures], but I can deal with VB
if I have to)

Thanks!

RL

 

[raylopez99]

i wouldn't change the tables structure to accommodate interface needs; you
can almost always find a way to put what you need in the interface while
maintaining the integrity of tables/relationships design.

in this case, i'd use a calculated field as Marsh suggested.

hth

Amazing! You guys are good. Before I even read this post, I did a
redesign of the interface to eliminate this need, but I'll keep in
mind what Marsh suggested for future reference.

This databases stuff is addictive...I'm getting the hang of it and I
haven't opened a book on Visual Basic yet (I did peak at some Visual
Studio Help files for how to set up a MsgBox however).

RL