Re: A newbie paradox: is this a PK-FK (relationship) problem, or programming problem?

From: raylopez99 <raylopez99_at_yahoo.com>
Date: Sun, 23 Dec 2007 14:26:01 -0800 (PST)
Message-ID: <c36967f0-f048-4066-b3a6-0ce00879831f_at_p69g2000hsa.googlegroups.com>


On Dec 23, 1:25 pm, "tina" <nos..._at_address.com> wrote:
>. many times in
> these newsgroups, i've recommended Michael Hernandez' Database Design for
> Mere Mortals, and i stand by that recommendation. (that's the book i learned
> from, used as the textbook in a night school class i took on relational
> design.) i believe it will be well worth your time and money to buy a copy
> and read it cover to cover, practicing the concepts as you go. good luck
> with your project!  :)
>

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 with a stock symbol 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;StockSymbol when you click on the subform properties under the heading "Link Childfields", "Link Master fields". This workaround worked swimmingly, 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 Received on Sun Dec 23 2007 - 23:26:01 CET

Original text of this message