Need help displaying desired records in subform 1

[MacroScope]

I am creating a new database in Access 2007 (soon to migrate to 2010). Its function is to track share offerings for a newly formed company.

I created a main form and used the subform tool in form design to add a subform. The main form contains data for individual investors/shareholders, typical name, contact info, etc.

Each record on the subform represents a complete transaction. It has TransactionID, ShareholderID, number of shares purchased, date, whether shares were purchased or transferred from an owner's holdings to someone else, name of the recipient, and other things of a similar nature.

As I scroll from record to record in the main form, the subform shows a new record with the same ShareholderID, ready to add a new transaction.

It seems that by default Access sets the subform up like an order form, designed to add multiple records to a single invoice, for example, like front brakes, rotors, brake fluid, etc. all purchased at the same time.

That's not what I want in this case. As I scroll from record to record I would like to see all the previous transactions, with the new record awaiting a new transaction.

To try to make myself more clear, imagine that ShareholderID 1 has made 3 purchases of 1,000 shares, on three separate dates. What I'd like to see displayed in the subform as I hit ShareholderID 1 on the main form would be the three previous transactions. To repeat, each line represents a complete transaction, with all details pertaining to it contained within it.

How can I get the subform to display all previous records related to that shareholder ID, with a new record awaiting a new transaction?

All suggestions will be appreciated.

 

[straybullet]

Take a look at datasheet view for the subform.

Let them hate - so long as they fear... Lucius Accius

 

[dhookom]

Are you not seeing any previous transactions?
Are you sure there are previous transactions?
Are you seeing more than 3 but want this limited to the most recent three?
What are the significant data properties of the subform?

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

OK, I think the problem is that I'm not communicating this well. I'll try again.

Basically each record in the subform represents one complete transaction, with all the information related to that transaction on it.

Access wants to see each record in the subform as a separate line item as one would normally find on an invoice.

When I write a test record all is well. When I go off the main form record and return to the record, I see a brand new subform screen with the ShareholderID inserted, ready to write a new record, again, as one would normally expect.

What I want to see when I return to the main form record is all the previous transactions related to that mainform ShareholderID, with a new record waiting to be written if another transaction is made.

There is no real data in the database yet. It's all test stuff at this point, but all the transactions I make are stored in the appropriate tables. They just are not displayed as I enter the Shareholder record. It just goes to a new record.

This is what I'd want or expect in most applications. I have a customer, and each time I return to that customer I'd normally expect to write some sort of multi-line invoice, so as Access provided it the subform would work perfectly.

Given the different situation I have here, I want to display all previous transactions in the subform and be ready to write a new transaction if needed.

I hope this is a little more clear.

 

[dhookom]

True, you are not communicating well. Answer all of my question especially "What are the significant data properties of the subform?"

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I am not at the computer with the database. I'll have to answer the question later when I get back.

When you ask for significant data properties, specifically what are you asking for? Record Source or ???

 

[dhookom]

Regarding the Data properties of the form, I see about 12 of them in Access 2007. How about providing them all?

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

OK, will do later this evening.

 

[MacroScope]

Here's the info. Properties not mentioned have no specific setting.

Record Source: SELECT [TransactionResults].[TransactionID], [TransactionResults].[Transactions].[ShareholderID], [TransactionResults].[Volume], [TransactionResults].[TransDate], [TransactionResults].[BorS], [TransactionResults].[Issued?], [TransactionResults].[Certificate], [TransactionResults].[IssueDate], [TransactionResults].[TransfererReceiver], [TransactionResults].[TransferToID], [TransactionResults].[TypeofCert], [TransactionResults].[Processor], [TransactionResults].[PmtMethod] FROM TransactionResults;

Record Type: Dynaset
Fetch Defaults: Yes
Filter on Load: No
Order by on Load: Yes
Data Entry: Yes
Allow Additions: Yes
Allow Deletions: No
Allow Edits: Yes
Allow Filters: Yes
Record Locks: No Locks

Thanks.

 

[dhookom]

Change Data Entry to No

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Dude, you're a bloody miracle worker!

Thanks!

Do you live anywhere near Southern CA? I'd like to buy you a beer!


I really appreciate your help. You nailed it on this one!