Making a form with several subforms?

[ejstefl]

Hello all,

I have a database set up with two main tables - Accounts and balances. The accounts table has an AccountID and the balance table has a BalanceID. Each account can hold several assets, and each combnation of account and asset has a unique AccountID. What I want is is a form where you could specify an asset, and it would bring up a new BalanceID for each account that holds that asset, all on one form. Is this possible? Could anyone please help me with this? Thanks everyone!

 

[ejstefl]

Maybe I should clarify a little more. What I want is a form in datasheet view that will bring up account information by asset and add a new balanceID for each account where you can enter the most current balance info. Can anyone help? Thanks!

 

[Dobie]

Not sure as your explanation is a bit over the place but!

When you goto create a new form and when it asks where you get your info from you can choose both tables (I presume you have two tables, one for account and one for balance)add contents of both tables, then view by Account, this will have the main form as the account and a sub form for the balance, then just add a button to add sub records under the Account. Just ensure that you have a field in both tables that match ie!

Table 1
AccountBalanceID (Primary Key)

Table 2
BalanceID (Primary Key)
AccountBalanceID
You can have this in datasheet view but form view looks better, also under each account it will list all the balances, obviously if there are none it will be blank.

Hope this helps!

[This message has been edited by Dobie (edited 02-08-2002).]

 

[ejstefl]

Sorry about my explaination, its a bit hard for me to describe this stuff with words. But yes, I it set up like you mentioned. I actually already have a form like you mentioned, too. What I want is a form that will have more than 1 account on it. Say I have 5 accounts, and each account holds 5 assets. I would have 25 accountID's. I want to be able to list those 5 accounts on one form by the asset they hold. That is, lets say the assets are numbered 1-5. I want to specify asset 4, and have it list each account and bring up a new balanceID for each account, all on the same form. I hope this makes sense... Thanks for the help!

 

[ejstefl]

Sorry to bump this again, but I'm still stuck on this. Anyone have any ideas for me?

 

[David R]

I believe Dobie's answer is correct, but I'm not quite sure I understand what you're asking. Let me paraphrase, and you correct me if I'm wrong:

Problem: You have a multi-part Primary Key for the tableAccounts, based on Acct# and AssetID. Then your tableBalances has the fields PrimID (probably Autonumber), AcctAssetID (or maybe two fields both as foreign keys - I don't work with multi-part Primary Keys often), and Balance, which is a point-in-time reference to the Balance of the Asset for that Account. All previous balances are also stored here for auditing/account purposes.

Am I close?

(Possible) Answer: My instinct is to use three tables:
tableAccounts: all the fields pertaining to each account. Account owner, contact information, date opened, etc.
tableAssets: all the fields pertaining to an individual asset (shares held, beneficiary, etc. I don't know the details) plus a Foreign Key field called AccountID, which is non-unique.
tableBalances: An autonum field for a PK, a Foreign Key field for AssetID, and a current balance. Probably a date/time field too for account purposes.

Then in my Relationships Window (Tools>Relationships) I would create a One:Many relationship between tableAccounts.AcctID and tableAssets.AcctID, and One:Many relationship between tableAssets.AssetID and tablebalances.AssetID.

Now on your main form you can create an unbound combo box to look up existing accounts, and create your subform to show Continuously your assets (the link should be created automagically by the wizard if you use Insert>Subform/Subdatasheet). You can even add a second subform to your subform, and default it to show the last entry (sorted by Date/Time field) in tableBalances for that Asset. With Allow Additions turned on, you should be able to update your balance anytime you like. Look into using the default value of Now() for your date field to prevent data entry errors. Consider strongly recording WHO does WHAT with auditing fields, especially if you are handling money.

I hope that helps.
David R

[This message has been edited by David R (edited 02-12-2002).]

 

[ejstefl]

OK, let me try to explain what I have. This is for balancing mutual funds (working with share amounts).

tblAccounts - Holds information about accounts, such as account numbers, names, etc. I have an AccountID autonumber primary key. One account can have several mutual funds in it, so each AccountID will have one account number and one mutual fund. That is to say, one account number may have 3 accountID's if it holds 3 mutual funds. Not the best way to set it up, I know, but..

tblBalances - holds AccountID, our share balance, the share balance at the mutual fund company, the date, comments, etc. Has a BalanceID autonumber primary key.

tblAssets - holds infomation about the different mutual funds, like name, phone number, etc. Has a CUSIP (kind of like a ticker symbol) as the primary key.

OK, what I want is a form in which you will specify a certain mutual fund when you open it. The form will then contain all the accounts that hold that mutual fund. For each accountID it will create a new BalanceID, which will allow you to enter all the current balance information. That way, you can enter information for an entire mutual fund at once.

For example, (and I'm just making this up): There's a mutual fund called Vanguard Short Term Bond Fund. We have 3 accounts that hold this fund. I want to open a form, specify the Vanguard Short Term Bond Fund, and see those 3 accounts. I also want to be able to have the form add a new balanceID for each account that holds this fund and let me type in the balance information.

Does this make sense? I hope so...

 

[David R]

Ok, for your structure my proposed solution was a bit bass-ackwards. tblAssets would be your main table, joined 1:M to tblAccounts, which is joined 1:M to tblBalances.
The basic idea is the same though. You can have a combo box with all your funds, and then pull up your accounts that have that fund.
Actually, if you ever want to have a look at a client account and all the related assets, you'll need a Many-to-Many relationship (requiring one more table, with just two fields, primary key is multipart consisting of the two primary keys from your Assets and Accounts tables). Then you can look at the One Asset with Many Accounts, or the One Account with Many Assets.

Unfortunately, implementing M:M relationships on my forms is still on my 'to do' list.

Search the forums or find yourself a good book to curl up with.

Good luck,
David R