Re: A subform Link Child Fields, Link Master fields problem (newbie question)

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


"raylopez99" <raylopez99@xxxxxxxxx> wrote in message
news:60c28324-d9a7-404a-9f3c-fb6050df8834@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

On Dec 23, 1:25 pm, "tina" <nos...@xxxxxxxxxxx> wrote:

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


.

Relevant Pages

  • A subform Link Child Fields, Link Master fields problem (newbie question)
    ... foreign key) from the parent form, so that this field can be bound to ... control in the child subform, ... My workaround below is not elegant and I trust there's a better way to ...
    (microsoft.public.access.formscoding)
  • Re: Problem with SQL and Recordset
    ... To refer to the field on your subform from the parent form, ... the number in the Suffix field How do I ... LocationSub? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Referential Reference to Parent Form in Query Criteria
    ... If you are already capturing which DataSubject each DataField belongs in, ... fsub_DataSubjects (subform used to select and display the records from ... parent form that is set in VBA to be equal to the ...
    (microsoft.public.access.queries)
  • Re: A subform Link Child Fields, Link Master fields problem (newbie question)
    ... foreign key) from the parent form, so that this field can be bound to ... control in the child subform, ... control source to an expression like: ...
    (microsoft.public.access.formscoding)
  • Re: Calling a subform from another form
    ... You shouldn't need to reference the subform in your criteria to open the ... What gets displayed on the subform is determined from the LinkMasterFields ... and LinkChildFields properties of the subform control on the parent form. ... want this form to open the form (frmSelectDropDown this has the subform, ...
    (microsoft.public.access.formscoding)