I am having trouble figuring out how to open a subset of records in the
following case. Here are the elements:
1. tblTransactions: a recordset of unique transactions (Purchase, Sale,
etc.) each of which has a unique TransactionID
2. tblBooks: a recordset of individual books, each of which has a unique
BookID
3. mtblBooksTransactions: a many-to-many table consisting of only two
fields, TransactionID and BookID (I may purchase or sell multiple books in a
single transaction. Each book will, hopefully, have both a purchase and a
sale transaction.
4. I have a form called frmTransAddBooks that displays the fields for a
single transaction, and it contains a subform called sfrmTransAdd. I used
mtbleBooksTransactions to select the books associated with the TransactionID
to the subform. Here is the Record Source statement the wizard put together
for me:
SELECT mtblBooksTransactions.BookID, mtblBooksTransactions.TransactionID,
tblBooks.strTitle, tblBooks.strStatus, tblBooks.curMyBookCost,
tblBooks.curShipToMeCost, tblBooks.strInventoryNumber FROM tblBooks LEFT
JOIN mtblBooksTransactions ON tblBooks.BookID=mtblBooksTransactions.BookID;
5. I have a form called frmBooks that I use to edit data on individual
books.
What I want to do now is add a button to frmTransAddBooks or sfrmAddBooks (I
don't care which) that will open frmBooks for only those records from
tblBooks that are displayed on the subform. Unfortunately, this is too
complicated for the wizard to figure out for me. I need help understanding
what I can use for the "where" part of the DoCmd.OpenForm statement.
I'm hoping someone smarter than I am can help.
Thanks.
Alice