swtrader
Okay, let me confirm the following...
One Transaction : Many Forms
I will Assume table design as follows. The names of the field dont have to be the same, but your table design should be close to the following....
tblTransaction
TransID - primary key
TransName - a name identifying the transaction
tblForm
FormID - primary key
TransID - foreign key pointing to tblTransaction.TransID
FormName - name of the form
This is a classical 1:M design where the TransID is the primary key on the "one" side, tblTransaction, and the "foriegn key" on the "many" side. Since you reference 1:M in the description of your problem, I will assume you have a fair idea of this concept.
Next
Have you established your relationships? You may have the correct design, but by defining your relationships before designing forms and queries simplifies the latter tasks. Open the Relationship window. From the menu, select "Tools" -> "Relationships". Add your tables. In my example, tblTransaction and tblForm. Then select your primary key on the "one-side" and drag it to the foriegn key on the "many-side". In my example, I would click and select tblTransaction.TransID and drag it to tblForm.TransID. A popup window window will open to which is used to further define the relationship. I usually just select "Enforce referential integrity".
Now, if I every add a subform based on tblForm to the main form based on tblTransaction, Access will automatically link the two tables using TransID. Ditto for queries using the two tables.
So if you have 200 forms, but only 20 forms are linked to the specific transaction, then only those 20 should display in the subform.
Moreover, if you create a form using the subform that is embeddde wihtin the main form for transactions, Access will automatically use the primary key, tblTransaction.TransID and use it for TransID for the tblForm entry.
...Moving on
I got a little confused by the latter part of your post.
So far, though, the required forms are not 'part of the transaction'. How do I get the Primary Key value from a selected transaction in the Transaction table into the query?
Make sure your table design is similar to the classical deisgn I presented above. It really helps to define your relationships using the Relationship tool.
Actually "entering the foreign key value" into the Forms record can be done two ways, and will depend mostly on "real estate" on the form.
The simplist way is to use a Form + Subform so Access creates the entry for you. The main form is based on tblTransaction and the subform is based on tblEform.
Another way is to have one form that defines your Transactions, and then another form for your Forms. On the Forms form (this gets confusing because of the choice of names), have a combo box setup as follows...
ControlSource: TransID (on the tblForm)
RowSource: SELECT TransID, TransName FROM tblTransaction
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1.5"
Note that values for ColumnCount and ColumnWidths will depend on your actual RowSource. I used TransID and TransName which means I have two columns. The ColumnWidths 0";1.5" means the first column, TransID, is not displayed / hidden, and the second column, TransName is displayed with a 1.5" column width.
Regardless, you still need to first define / create the Transaction record before you create Form records.
Personally, if I understand you correctly, I would use a main form with two subforms the subforms would be embedded in a tabular form.
The main form would display the "header information" for the Transaction.
On the first page of the tabular form, a subform based on would display all related Forms in a contineous form. Then using either a double-click event procedure, or by adding a command button, on the subform, a full single form based on tbleForm opens up, and can be filled in, or printed.
On the second page of the tabular form, the subform is also based on tblForm, but uses a "Single form" format, and not a contineous form. This is where the end user would create any new forms for the Transaction.
Hope I got it right.
Richard
