Sub-Form or Pop-up Form

[accessstudent]

I'm trying to create a credit/debit ledger database. So far I got it going pretty well, but I've run into a snag. I have the form setup to enter transactions, but I need to set it up so I can split up one transaction for the purposes of multiple categorization. First, I am trying to figure out how to launch the separate form, and second I need to populate certain fields on the form from the main form (such as the "Payable to" field). Also, I need it to update the same table as the main form, and refresh the main form to reflect the transactions. Any thoughts?

[CJ_London]

First, I am trying to figure out how to launch the separate form, and second I need to populate certain fields on the form from the main form (such as the "Payable to" field).

I suspect you are coming at this from the wrong direction but rather than opening another form suggest a lot easier to use a subform for the multiple categorisations. Your issue will be that as a check, the sum of the multiple categorisations should equal the sum of transactions. You should be starting at the bottom (i.e. multiple categorisations), then no need to update the 'main' table.

Think you need to step back and think about all the implications around how the system is required to work - multiple/part payments across multiple invoices which in turn might be assigned to multiple cost centres/account codes. Then you are in a position to design your tables and relationships correctly followed by the forms.

[accessstudent]

I'm trying to create a credit/debit ledger database. So far I got it going pretty well, but I've run into a snag. I have the form setup to enter transactions, but I need to set it up so I can split up one transaction for the purposes of multiple categorization. First, I am trying to figure out how to launch the separate form, and second I need to populate certain fields on the form from the main form (such as the "Payable to" field). Also, I need it to update the same table as the main form, and refresh the main form to reflect the transactions. Any thoughts?

Thank you for the suggestion, I'm still a little new at this though. My goal is to create something like Quicken or Quickbooks uses where you can take one transaction and then split it into multiple categories. The main table needs to then have then first transaction categorized as "Split" and then all the sub-transactions would follow it as you go down. I thought about the sub form or possibly a button to take you to a new form so that the focus is on that and not the main form. I hope I've explained this well. While I'm quite savvy with IT overall, Access is definitely not my strong suite, so any "noob" advice would be helpful.

[CJ_London]

i understand the principle - I have built numerous accounting apps over the years (I'm also an accountant) which is why I suggested you are coming at this from the wrong direction. However you know your requirements better than I and I'm not going to spend time trying to convince you otherwise. I'm guessing you are trying to do something outside of double entry principles.

To answer your questions - these are my thoughts

I am trying to figure out how to launch the separate form

use docmd.openform,

second I need to populate certain fields on the form from the main form (such as the "Payable to" field)

you will need to pass the transactionID from the main form plus other values as required as an openarg parameter to the openform command (easiest is a comma separated string or array) and in the form open event assign this as a default value to a (likely hidden) control as the foreign key and other controls as required. But again, have to ask why - you already have the information in your main form. Database normalisation means you don't duplicate it

need it to update the same table as the main form

why? the main form should now not contain transactions, they will all be in the subform - and you don't store calculated values of this nature in the main form. However to do so, probably in your newly opened form close event you would have some code along the lines of

forms!mymainform.somefield=dsum("amounts","categor isations","PK=" & FK)

refresh the main form to reflect the transactions

follow the above code with
forms!mymainform.requery

[accessstudent]

I understand what you're saying and I probably am going the wrong direction. What would you recommend for this DB? I simply need a basic credit/debit system. I want to be able to enter transactions, categorize as necessary (which includes splits), and run reports and/or graphs to visually represent the data as needed. Also, I may have already found a solution for this, but I need to be able to add new merchants and categories as needed. Once again, what would you recommend? Additionally, is there a way to copy the data from one table to a new table without losing the data? I need to re-create one of the tables that was structured wrong. Thanks for your help.