connecting query data to a specific record 2

[swtrader]

OK, maybe the subject is not very helpful.

The short question is: I have a tblForms containing 200 records. There is a one-to-many relationship between tblTransactions and tblForms. (One transaction can have many forms.)

Based on user input, a query for the tblForms reduces the list to 20 or 30. 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?

Added info -- through a master form (Transaction combo box selection) and a sub-form (tblForms), the user could select each required form from the list of 200. However, the query 'selects' the needed forms based on the type of transaction. So, I end up with a list of forms (in the query) that do not have a Transaction primary key value in them.

As usual, I am probably making this harder than it needs to be. Thanks for all help everyone posts in this forum.

 

[dhookom]

What do you mean by "Based on user input, a query for the tblForms reduces the list to 20 or 30"? I'm assuming you want the primary key field form tblForms appended to a junction table with a single value from the transaction table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

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